Sophos Data Lake makes data about your devices available in the cloud. You can then use Sophos Live Discover to do as follows: Run security queries on all your devices, even if they’re not connected. Query data from the past 7 days or the past 30 days (depending on your license).
The article will guide you how to use SQL code customized by Sophos XDR to query check list versions of applications installed on the endpoint to compare against known vulnerable versions using Data Lake.
Step 1: Create query code with Data Lake
To learn more about new queries you can join the following forum: https://community.sophos.com/intercept-x-endpoint/p/query-forum
Login Sophos Central Admin > Threat Analysis Center > Live Discover >Data Lake Queries.
Here you can see the queries built-in by Sophos, with query category such as Device, Network, etc. You just need to select the query type, click run query and check the returned results.
To custom query, select Enable Designer Mode > Create new query.
Next, fill in the following information:
- Query Name: Enter name the query you want
- Category: Select the query category
- Fill the description if you want.
Scroll down and select Data Lake.
SQL section: You enter the code to query windows update.
SQL code: You enter the code query List versions of applications installed on Endpoint such as zoom, chrome,,..
SELECT meta_hostname AS Endpoint,
MAX(CASE WHEN name = ‘Zoom’ THEN version END) AS Zoom,
MAX(CASE WHEN name LIKE ‘Mozilla Firefox%’ THEN version END) AS Firefox,
MAX(CASE WHEN name = ‘Microsoft OneDrive’ THEN version END) AS OneDrive,
MAX(CASE WHEN name = ‘Google Chrome’ THEN version END) AS Chrome,
MAX(CASE WHEN name = ‘Microsoft Edge’ THEN version END) AS Edge,
MAX(CASE WHEN name = ‘Adobe Acrobat Reader DC’ THEN version END) AS AdobeReader
WHERE query_name = ‘windows_programs’
GROUP BY meta_hostname
Click Run Query
Step 2: Check the result
After the query is complete, you will have the version information of the applications integrated in the query code of each endpoint that has data on the Data Lake.