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

This query is designed to give you a "Total Report" of what is happening on a specific device. Such details include:

• Operating System
  
• IP Address
  
• Disk Encryption Status
• Uptime
• Logged In User

Prerequisites

You must have XDR enabled in your environment. This is entirely a Live Discover query.

This is intended for MacOS.

Query Focus

This Mac specific query was written by RaviSoni 

DISCLAIMERS FOR USING THIS QUERY:

• You should run this as a per-device requirement - can get messy with large sets of machines

-- Cards orient the results in five columns
--    ATRIBUTE - The thing being checked
--    VALUE - The result of the evaluation
--    CONTEXT - Some useful information related to the Attribute
--    CONTEXT_DATA - The inoformation for the context information being shown
--    NOTES - Some additional relevant information on the Attribute/Value


-- BLANK LINE BETWEEN EACH DEVICE
SELECT CAST(' ' AS TEXT) ATTRIBUTE, CAST(' ' AS TEXT) VALUE, CAST(' ' AS TEXT) CONTEXT, CAST(' ' AS TEXT) CONTEXT_DATA, CAST(' ' AS TEXT) NOTES
UNION ALL
SELECT CAST('=========================' AS TEXT) ATTRIBUTE, CAST('=========================' AS TEXT) VALUE, CAST('=========================' AS TEXT) CONTEXT, CAST('=========================' AS TEXT) CONTEXT_DATA, CAST('=========================' AS TEXT) NOTES

UNION ALL

--Device Info
SELECT CAST('DEVICE INFO ' AS TEXT) ATTRIBUTE, CAST(' ' AS TEXT) VALUE, CAST(' ' AS TEXT) CONTEXT, CAST(' ' AS TEXT) CONTEXT_DATA, CAST(' ' AS TEXT) NOTES

UNION ALL

-- Operating System information
SELECT 'OPERATING SYSTEM' ATTRIBUTE, name VALUE, 'VERSION' CONTEXT, version CONTEXT_DATA,  'INSTALLED ON: ' || datetime(install_date,'unixepoch') NOTES
FROM os_version

UNION ALL

-- Current IP/MAC and DHCP Server for the device
SELECT 'IP-ADDRESS' ATTRIBUTE, CAST(ia.address AS TEXT) VALUE, 'MAC ADDRESS' CONTEXT, id.mac CONTEXT_DATA, '' NOTES
FROM interface_addresses ia JOIN interface_details id ON id.interface = ia.interface
WHERE ia.address NOT IN ('::1','127.0.0.1') AND ia.address NOT LIKE 'fe80::%'

UNION ALL

-- Disk Encryption Status
SELECT DISTINCT 'DISK ENCRYPTION STATUS' ATTRIBUTE, 'Name: ' || disk_events.name ||CHAR(10)||'Device Path: '|| device VALUE, encryption_status CONTEXT, type CONTEXT_DATA, '' NOTES
FROM disk_encryption JOIN disk_events ON disk_encryption.name = disk_events.device WHERE encryption_status <> 'undefined'

UNION ALL

--Device Uptime
SELECT 'UPTIME' ATTRIBUTE, 'DD:HH:MM:SS' VALUE, days||':'||hours||':'||minutes||':'||seconds CONTEXT, '' CONTEXT_DATA, '' NOTES
FROM uptime

UNION ALL

--LOGGED IN USER
SELECT 'LOGGED IN USER' ATTRIBUTE, 'User' VALUE, user CONTEXT, '' CONTEXT_DATA, '' NOTES
FROM logged_in_users

Understanding the Code

This query will select pieces of information about the device and union it with additional tables. It is very direct but effective and an example of how you can aggregate multiple pieces of data into one report.

---

Happy querying!

-jk

Added Disclaimer
[edited by: GlennSen at 3:31 PM (GMT -7) on 5 Apr 2023]