[QueryCorner][October2022] Audit Peripheral Control

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

Sophos Endpoint and Server products all come equipped with Peripheral Control. This technology allows operators to monitor or restrict access to different devices and communication technologies, such as USBs or Bluetooth.

If you are not using this, our docs are here:

Often, an administrator may want to export a report of their current exclusion or block list.

Prerequisites

You must have XDR enabled in your environment.

This is intended for Windows only on Live Discovery.

Query Focus #1 - What are my Peripheral Control Exemptions?

System Impact	Smallest
Data Transferred	Small
Execution Time	Fast

-- Variable type String
-- Variable name deviceTypePath
-- Ex: \device_types\device_type_000006

WITH policyTimeStamp AS (
SELECT name, (key || data || '$$deviceTypePath$$') AS policyPath, (key || data || '$$deviceTypePath$$' || '\exemptions\exemption_00000%') AS fullPath, datetime(mtime, 'unixepoch', 'localtime') AS regTimeStamp
FROM registry
WHERE key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Sophos\Management\Policy\DeviceControl\' 
AND name = 'latest'
),
policyTypeDefault AS (
SELECT name, data, datetime(mtime, 'unixepoch', 'localtime') AS regTimeStamp
FROM registry
WHERE key = (SELECT policyPath FROM policyTimeStamp)),
exemptListAccess AS (
SELECT name, data, datetime(mtime, 'unixepoch', 'localtime') AS regTimeStamp, key, path
FROM registry
WHERE key LIKE (SELECT fullPath FROM policyTimeStamp)
AND name = "access"
),
exemptListDeviceID AS (
SELECT name, data, datetime(mtime, 'unixepoch', 'localtime') AS regTimeStamp, key, path
FROM registry
WHERE key LIKE (SELECT fullPath FROM policyTimeStamp)
AND name = "device_id"),
exemptListInstanceID AS (
SELECT name, data, datetime(mtime, 'unixepoch', 'localtime') AS regTimeStamp, key, path
FROM registry
WHERE key LIKE (SELECT fullPath FROM policyTimeStamp)
AND name = "instance_id"),
exemptListDeviceAccess AS (
SELECT exemptListAccess.data AS policyAction,
exemptListDeviceID.data AS peripheral,
exemptListDeviceID.regTimeStamp
FROM exemptListAccess
JOIN exemptListDeviceID
ON exemptListAccess.key = exemptListDeviceID.key),
exemptListInstanceAccess AS (
SELECT exemptListAccess.data AS policyAction,
exemptListInstanceID.data AS peripheral,
exemptListInstanceID.regTimeStamp
FROM exemptListAccess
JOIN exemptListInstanceID
ON exemptListAccess.key = exemptListInstanceID.key)
SELECT *
FROM exemptListDeviceAccess
UNION ALL
SELECT *
FROM exemptListInstanceAccess
ORDER BY policyAction

Results:

Auditing Other Types

Allow us to focus our attention to the variable. In the example, we used \device_types\device_type_000006 as the focal point. This returns removable storage exemptions.

If we modify the number "6" in our variable, it will allow us to search other device types.

0	Optical Drive
1	Floppy Drive
2	Modem
3	Encrypted Storage
4	Infrared
5	Wireless
6	Removeable Storage
7	Bluetooth
8	MTP

Query Focus #2 - Can I see my Peripheral Control Log entries?

System Impact	Largest
Data Transferred	Medium
Execution Time	Slow

-- Variable Type String
-- Variable Name YYYY-MM
-- Ex: 2022-08

WITH sspLog AS (
   SELECT *
   FROM grep
   WHERE path = 'C:\ProgramData\Sophos\Endpoint Defense\Logs\SSP.log'
   AND pattern = '$$YYYY-MM$$'),
logDetails (timestamp, description) AS (
   SELECT datetime(CAST(SPLIT(line, ' ', 0) AS VARCHAR),'localtime'),
         CAST(SPLIT(line, ']', 1) AS VARCHAR)
        FROM sspLog)
SELECT * FROM logDetails
WHERE description LIKE '%DeviceControl%'
ORDER BY timestamp DESC

Results:

Important

Focusing our attention to the variable in this query, we can pinpoint more specifics. For example, we used YYYY-MM to return an entire month of logs. This is why we see large costs in resources to compute this. If you modify the variable, to YYYY-MM-DD, this will allow us a more precise timeline and smaller query.

You will want to also reflect this change in line 9 of the shared code. If not, you can expect an error. In doing so, this will return a smaller cost in resources to the machines.

Happy querying!

-jk

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