index=main event_simpleName=OsVersionInfo event_platform=Win WinOSVersion IN ("Windows 10", "Windows Server 2016", "Windows Server 2019")
| eval BuildAndSubBuild=BuildNumber_decimal.".".SubBuildNumber_decimal
| eval PatchStatusCode=case( BuildNumber_decimal == "10586","3", BuildNumber_decimal == "15063","3", BuildNumber_decimal == "10240" AND SubBuildNumber_decimal >=18453,"1", BuildNumber_decimal == "16299" AND SubBuildNumber_decimal >=1622,"1", BuildNumber_decimal == "17134" AND SubBuildNumber_decimal >=1246,"1", BuildNumber_decimal == "17763" AND SubBuildNumber_decimal >=973,"1", BuildNumber_decimal == "18362" AND SubBuildNumber_decimal >=592, "1", BuildNumber_decimal == "18363" AND SubBuildNumber_decimal >=592,"1", BuildNumber_decimal == "14393" AND SubBuildNumber_decimal >=3442,"1", BuildNumber_decimal == "17763" AND SubBuildNumber_decimal >=973,"1", true(),"404")
| eval PatchStatus=case( PatchStatusCode=1, "Not Vulnerable", PatchStatusCode=2, "Vulnerable (Patched; Reboot Required)", PatchStatusCode=3, "OS EOL (Update Needed)", true(), "Vulnerable (Not Patched)")
| stats latest(PatchStatus) as PatchStatus, latest(WinOSVersion) as WinOSVersion count by aid cid
| lookup managedassets.csv aid OUTPUT MAC, LocalAddressIP4
| lookup aid_master.csv aid OUTPUT ComputerName, Time, SiteName, MachineDomain
| lookup cid_name cid OUTPUT name
| eval MAC=mvindex(MAC,0,2)
| eval LocalAddressIP4=mvindex(LocalAddressIP4,0,2)
| rename WinOSVersion as Version ComputerName as Hostname, WinOSVersion as "OS Version" MachineDomain as Domain , LocalAddressIP4 as "IP Address", name as "Company Name" PatchStatus as "Vulnerable Status"
| table Time , Hostname, "Vulnerable Status", "OS Version" ,MAC, "IP Address" ,SiteName , Domain , "Company Name" `formatDate(Time)`