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

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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

L4 Transporter

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

1 accepted solution

Accepted Solutions

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

 

 

 

 

View solution in original post

6 REPLIES 6

L5 Sessionator

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.

L2 Linker

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"

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".

 

 

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

 

 

 

 

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

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

 

 

  • 1 accepted solution
  • 2464 Views
  • 6 replies
  • 0 Likes
  • 78 Subscriptions
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!