XQL for highest available install date of KBs / checking hosts for installed win updates

[RFeyertag]

Hello dear community, 

my goal is to see which host has not actual windows kbs installed. I would need the last KB installed information per host. 

Now I need to limit it per host, and not through the whole query result. Do you have an Idea how to get this running? Maybe you have another ideas how we can check fast which host is not Windows up to date with Cortex XDR Pro?

I also thought about to filter it for hosts which have not installed anything from the actual month. Maybe it is a better and faster way? 

config case_sensitive = false
| dataset = host_inventory

| arrayexpand kbs
| alter kbnr = json_extract(kbs , "$.name"), install_date = json_extract(kbs , "$.installation_date")
| fields host_name, kbs, install_date
| limit 1 // Limiting the results to only the top 1
|sort desc install_date

BR

Rob

[neelrohit]

HI @RFeyertag ,

Instead of running an XQL query, you can also consider running scripts on the endpoints with XDR Pro Capabilities enabled. 
Simply, go to script execution, select execute commands and run the following command

wmic qfe GET HotFixID, InstalledOn

Select your target endpoints and run the script on the same. You should be able to get the latest KBs on all endpoints with their install dates. You can pull the CSV or report widget for the same and tally your data with the latest KB dates and build number.

If you still need to consider the query mechanism, you can filter it by hostname.

[RonaldWeiss]

Perhaps this might be what you want:

config case_sensitive = false
| dataset = host_inventory

| arrayexpand kbs
| alter kbnr = json_extract(kbs , "$.name"), install_date = json_extract(kbs , "$.installation_date")
| fields host_name, kbnr , install_date
|dedup host_name by desc install_date
| sort asc install_date

and after that, perhaps a "|limit 50"

[RonaldWeiss]

So, nevertheless the install_date as string is hard to filter by date.
To resolve this, parse_timestamp might be a solution, but i dont get how it works...

The install_date string is "2022-08-11" which i'd like to convert to something filterable like "Aug 11th 2022".

[bbarmanroy · Accepted solution]

You can sort out the parse_timestamp with this line:  

alter install_date_mod = parse_timestamp("\"%Y-%m-%d\"", install_date

Note the use of regex to escape the double-quotes that comes as part of the default install_date string. 

So the whole query would look like

dataset = host_inventory
| arrayexpand kbs
| alter kbnr = json_extract(kbs , "$.name"), install_date = json_extract(kbs , "$.installation_date")
| fields host_name, kbnr , install_date
| alter install_date = parse_timestamp("\"%Y-%m-%d\"", install_date)
| dedup host_name by desc install_date
| sort asc install_date

[RFeyertag]

Hey @neelrohit, 

I will note this query for other use cases, but for my use case it isn't the best solution, because this is an live query.

The agents are sometimes 2 or 3 weeks offline. So it would need much time to run after them. 

And with the xql query I have 98% coverage of all of our agents. 

BR

Rob

[RFeyertag]

Thank you very much! 

My final query, which was integrated into a widget and dashboard:

config timeframe between "-1y" and "+5d"
|dataset = host_inventory
| arrayexpand kbs
| alter kbnr = json_extract(kbs , "$.name"), install_date = json_extract(kbs , "$.installation_date")
| fields host_name, kbnr , install_date, agent_domain
| alter install_date = parse_timestamp("\"%Y-%m-%d\"", install_date)
| dedup host_name, agent_domain by desc install_date
| sort asc install_date

Some issues with the content can be found in this conversation:

https://live.paloaltonetworks.com/t5/cortex-xdr-discussions/host-inventory-installed-kbs-no-entry/td...

BR

Rob