A while ago, I wanted to ensure that all endpoints in the company where I was responsible for security were fully updated. Since we had a consistent Windows 10 environment, the task was to verify that the latest Microsoft Cumulative Update (CU) was installed on all machines. Without an existing tool to easily generate this report, I decided to create a solution using my favorite tool: Cortex XDR.
Crafting the Query
I began by creating a query to gather the necessary data. Here’s the step-by-step breakdown:
- Dataset: The correct dataset for this task is
host_inventory
.
dataset = host_inventory
- Expand Array: We need to expand the
kbs
array field within the dataset to access relevant information.
| arrayexpand kbs
- Filter by Workstation: Since we only want to report CU details on workstations, we need to add a filter.
| filter agent_type = ENUM.AGENT_TYPE_WORKSTATION
- Extract Data: Next, we’ll create new fields by extracting data.
| alter kbnr = json_extract(kbs , "$.name"), install_date = json_extract(kbs , "$.installation_date"), update_name = json_extract(kbs , "$.title")
- Filter for Cumulative Updates: Now, filter the KBs to show only those that contain “Cumulative Update” in their name.
| filter update_name contains "CUMULATIVE UPDATE FOR WINDOWS"
- Create and Sort Table: Finally, we’ll create a sorted table with the latest, deduplicated values.
| fields host_name, kbnr, update_name, install_date
| alter install_date = parse_timestamp("\"%Y-%m-%d\"", install_date)
| dedup host_name by desc install_date
| sort asc update_name
Full Query:
dataset = host_inventory
| arrayexpand kbs
| filter agent_type = ENUM.AGENT_TYPE_WORKSTATION
| alter kbnr = json_extract(kbs , "$.name"), install_date = json_extract(kbs , "$.installation_date"), update_name = json_extract(kbs , "$.title")
| filter update_name contains "CUMULATIVE UPDATE FOR WINDOWS"
| fields host_name, kbnr, update_name, install_date
| alter install_date = parse_timestamp("\"%Y-%m-%d\"", install_date)
| dedup host_name by desc install_date
Creating the Widget
Next, I created a custom widget to visualize the data.
- Navigate to Dashboards & Reports -> Customize -> Widget Library.
- Select Create Custom XQL Widget.
- Enter a name (e.g., “Cumulative Update Level”).
- Paste the query you created.
- Select a data range (I used “1M” for one month).
- Click Preview.
- Save the widget.
Creating a Graph
To make the report more visually appealing, I created another widget: “Cumulative Updates by Computer Graph.”
Here’s the query I used:
dataset = host_inventory
| arrayexpand kbs
| filter agent_type = ENUM.AGENT_TYPE_WORKSTATION
| alter kbnr = json_extract(kbs , "$.name"), install_date = json_extract(kbs , "$.installation_date"), update_name = json_extract(kbs , "$.title")
| filter update_name contains "CUMULATIVE UPDATE FOR WINDOWS"
| alter install_date = parse_timestamp("\"%Y-%m-%d\"", install_date)
| dedup host_name by desc install_date
| comp count (host_name) as computers by update_name
| sort asc update_name
| view graph type = pie xaxis = update_name yaxis = computers font = "Arial"
Creating the Report
Now it’s time to create the report:
- Navigate to Dashboards & Reports -> Customize -> Reports Templates.
- Select + New Template.
- Enter a report name (e.g., “Cumulative Update Level Report”).
- Set the data range (I chose “Last 1M”).
- Choose Blank as the report template.
- Click Next
- Drag and drop the widgets you created from the Widget Library into the report.
- Remove any unwanted widgets as needed.
- Once you’ve customized the report to your liking, click Next.
- Select Save as Template and Schedule.
- Choose how often you’d like the report to run.
- Add your email address for email distribution.
- Select Cumulative Update Level under the Attach CSV option.
- Save the template.
By following these steps, you’ll have regular visibility into the update levels across your organization.
If you have any questions or comments, feel free to post them below!