Disclaimer: This information is provided as-is for the benefit of the Community. Please contact Sophos Professional Services if you require assistance with your specific environment.
Purpose
If you have not traversed the XDR journals, please review the rich data journals we are logging on our endpoints and servers. This data is available for the last 90 days on agent. You can create your own queries, but in this post, we are going to start the New Year out by focusing on what ships with the product. SQL can be daunting at first, but with designer mode disabled, it allows the security administrator to see reports based on the query description.
This post will focus on a Data Lake Query, under the Device category, called "Pending Windows Updates" and "Pending MacOS Updates."
Prerequisites
You must have XDR enabled in your environment. We do not need designer mode enabled.
Device: Pending Windows OR MacOS Updates
If you're overwhelmed by the amount of options available to query, make use of the search feature to find what you're after. While we're not quite on ChatGPT terms (yet), the search functionality can allow you to parse the hundreds of preinstalled queries for what you're after. Here we type "pending updates" to find both Windows and Mac.
Results
Here, I can see that in the last 7 days, my ep_name "CY-DC" is in need of a reboot to complete the installation. As we are thinking about the query, it's important to remember that this is one you should have scheduled out to report automatically in every environment. This query is extremely valuable for a variety of reasons.
- As an administrator, you can match hotfix_IDs to make sure no system is vulnerable
- You're able to quickly see the "Important", "Critical" or other updates are sitting dormant
- This can be used (if) you need to validate to an insurance agency that your machines had all OS patches available at the time
- Your team is troubleshooting and wants to see if an device that they cannot access has any patches waiting to be applied
If your devices do not appear, either they have not been online to upload the data within the last (7) days in this case, or if there are no pending updates.
Happy querying!
-jk
Added Disclaimer
[edited by: GlennSen at 4:00 PM (GMT -7) on 5 Apr 2023]
