Sophos XDR: Instructions for query to check all vulnerabilities on workstations and servers

Overview

Sophos Extended Detection and Response (XDR) allows you to investigate detected threats (“threat cases”) and search for new threats or security vulnerabilities. It also enables remote monitoring and troubleshooting of devices. With Sophos XDR, you can access both cloud-stored data and data directly on devices, ensuring you always have the most up-to-date information.

This article provides a guide on how to use the Live Discover tool of Sophos CIXA with EDR to query the entire workstation and server system to obtain information about which machines have been installed with Sophos certificates. This helps reduce the time for administrators to manually check each machine for verification.

Query Diagram

How to configure

Step 1: Create Custom Query

Login Sophos Central Admin -> Choose Threat Analysis Center -> Choose Live Discover -> Enable Designer mode -> Click Create new query

Enter name for your query
In Category: Choose category that you want to save to
In Source: Choose Live Endpoint and choose OS that you want (some queries may not be supported for the selected operating system).

In SQL: Enter code

WITH program_list AS (
SELECT
REPLACE(REPLACE(REPLACE(name,’,’,’ ‘),’+’,’ ‘),’.’,’ ‘) name, — STRIP out some characters not normally found in product names to improve chances of finding it in the CSV DB
version,
REPLACE(REPLACE(REPLACE(publisher,’,’,’ ‘),’+’,’ ‘),’.’,’ ‘) publisher
FROM programs
WHERE version > ”
)
/\ | We will search for the Publisher, Product Name and version and use some wild cards ‘%” after we | | create a simple one word name for the publisher and product. The expectation is that these three | | pices of information should be relativly unique, but we can still get FPs | **/
SELECT
publisher,
CAST(name AS TEXT) || ‘ ‘ || version Application,
url ‘Identified CVE List’
FROM program_list
JOIN curl ON
url = ‘https://www.cvedetails.com/version-search.php?vendor=’
|| replace(program_list.publisher, ltrim(program_list.publisher, replace(program_list.publisher, ‘ ‘, ”)), ”)
|| ‘%&product=’
|| replace(program_list.name, ltrim(program_list.name, replace(program_list.name, ‘ ‘, ”)), ”)
|| ‘%&version=’
|| program_list.version
WHERE result
LIKE ‘%Details for%’;