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.
Below are the scripts and queries used in the 2021 XDR academy. Have fun. 
The academy runs Oct 5th-7th 2021 10AM CET. For more info on the Academy or to register (GERMAN) check out : https://register.gotowebinar.com/register/2491720265450032908
Watch this space for when the other geographic regions host the academy in your location.
---------------------
By using or accessing the Software below, you agree to be bound by the terms of the Sophos End User License Agreement.
(#1) Data Lake Query - Lists doc,docx,docm that have been accessed
SELECT meta_hostname, username, time, sophos_pid, name,
CASE event_Type
WHEN 0 THEN 'created'
WHEN 1 THEN 'renamed'
WHEN 2 THEN 'deleted'
WHEN 3 THEN 'modified'
WHEN 4 THEN 'hard link created'
WHEN 5 THEN 'time stamps modified'
WHEN 6 THEN 'permisions modified'
WHEN 7 THEN 'ownership modified'
WHEN 8 THEN 'accessed'
WHEN 9 THEN 'binary file mapped'
ELSE 'unknown '
END Event_TYPE,
filename, path, file_extension, sha256
FROM xdr_data
WHERE query_name = 'access_productivity_documents'
AND UPPER(file_extension) IN ('DOC', 'DOCX', 'DOCM')
limit 100
(#2) Data Lake Query - List versions of installed applications (to compare against known vulnerable versions)
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
FROM xdr_data
WHERE query_name = 'windows_programs'
GROUP BY meta_hostname
(#3) Data Lake Query - Installed Sophos components with version number
SELECT meta_hostname AS ep_name,
MAX(CASE WHEN name = 'Sophos Anti-Virus' THEN version END) AS SophosAV,
MAX(CASE WHEN name = 'Sophos ML Engine' THEN version END) AS SophosML,
MAX(CASE WHEN name = 'Sophos AutoUpdate XG' THEN version END) AS SophosUpd,
MAX(CASE WHEN name = 'Sophos Management Communications System' THEN version END) AS SophosMgmt,
MAX(CASE WHEN name = 'Sophos Clean' THEN version END) AS SophosClean,
MAX(CASE WHEN name = 'Sophos Live Query' THEN version END) AS SophosLiveQ,
MAX(CASE WHEN name = 'Sophos Live Terminal' THEN version END) AS SophosLiveTerminal,
MAX(CASE WHEN name = 'Sophos Network Threat Protection' THEN version END) AS SophosNTP,
MAX(CASE WHEN name = 'Sophos Exploit Prevention' THEN version END) AS SophosExploit
FROM xdr_data
WHERE query_name = 'windows_programs'
GROUP BY meta_hostname
(#4) Data Lake Query - BitLocker Status
SELECT DISTINCT meta_hostname, drive_letter, protection_status, encryption_method, drive_device_id, persistent_volume_id, conversion_status, version, percentage_encrypted, lock_status
FROM xdr_data
WHERE query_name = 'disk_encryption_windows'
ORDER BY meta_hostname ASC
(#5) Data Lake Query - SHA256 Check
-- Search SHA256 with Additional Info
-- Variable: sha256 STRING, supports multiple value separated by |
WITH Open_Connections AS ( SELECT DISTINCT meta_hostname, path, query_name, count(*) FROM xdr_data WHERE query_name IN ('open_sockets')
GROUP BY meta_hostname, path, query_name
UNION ALL
SELECT DISTINCT meta_hostname, path, query_name, count(address) FROM xdr_data
WHERE query_name IN ('listening_ports‘) AND address NOT IN ('::1', '0.0.0.0', '::‘) AND path <> ''
GROUP BY meta_hostname, path, query_name
UNION ALL
SELECT DISTINCT x1.meta_hostname, x2.new_pid, x1.query_name, count(*) FROM xdr_data AS x1
CROSS JOIN
UNNEST(SPLIT(x1.sophos_pids, ',')) AS x2(new_pid)
WHERE x1.query_name IN ('sophos_ips_windows', 'sophos_urls_windows')
GROUP BY meta_hostname, new_pid, query_name
)
SELECT DISTINCT sha256, meta_hostname AS hostname, query_name, path,
CASE query_name
WHEN 'access_productivity_documents' THEN 'N/A'
ELSE IF((select count(*) from Open_Connections AS oc WHERE (oc.path = xd.path AND oc.meta_hostname = xd.meta_hostname) OR oc.path = xd.sophos_pid) > 0,'Yes','No')
END AS used_network
FROM xdr_data AS xd
WHERE regexp_like(sha256, '($$sha256$$)')
ORDER BY sha256
(#6) Powershell Script - XDR Device Check
Description: Sample code to explore the XDR API. The query help to discover devices that have no Endpoint on them and who is in the ARP neighborhood.
param([switch] $CheckDNS, [switch] $GetVendor)
<#
Description: Find unprotected devices on your network
Uses the Sophos Central Endpoint and XDR API's for retrieving the data
Parameters: -CheckDNS Performs reverse DNS lookups for IP-Addresses related to MAC-Addresses without a Hostname
-GetVendor Uses macvendors.com to lookup the Vendor related to the MAC-Address
Restriction: Only works for smaller environments due to the current XDR EAP limitations of max. 1000 results per query
Also note that the script is currently limited to up to 500 devices managed by Sophos Central
#>
function AddMacSeen($mac, $ip, $seenby) {
if(-not $global:macsSeen.contains($mac)) {
$global:macsSeen.add($mac, ([ordered]@{hostname="";tamperprotection="";health="";seenby=@{$ip=$seenby}}))
} else {
if(-not $global:macsSeen[$mac].seenby.contains($ip)) {
$global:macsSeen[$mac].seenby.add($ip, $seenby)
} else {
$global:macsSeen[$mac].seenby[$ip] = $global:macsSeen[$mac].seenby[$ip] + "|" + $seenby
}
}
}
cls
Write-Host("Sophos Central XDR & API Device Check")
Write-Host("=====================================")
# Check if Central API Credentials have been stored, if not then prompt the user to add them
if ((Test-Path $env:userprofile\sophos_central_admin.json) -eq $false){
# Prompt for Credentials
$clientId = Read-Host "Please Enter your Client ID"
$clientSecret = Read-Host "Please Enter your Client Secret" -AsSecureString | ConvertFrom-SecureString
# Out to JSON Config File
ConvertTo-Json $ClientID, $ClientSecret | Out-File $env:userprofile\sophos_central_admin.json -Force
}
# Read Credentials from JSON Config File
$credentials = Get-Content $env:userprofile\sophos_central_admin.json | ConvertFrom-Json
$clientId = $credentials[0]
$clientSecret = $credentials[1] | ConvertTo-SecureString
# Create PSCredential Object for Credentials
$SecureCredentials = New-Object System.Management.Automation.PSCredential -ArgumentList $clientId , $clientSecret
Write-Host("[API] Authenticate to Sophos Central...")
# SOPHOS OAuth URL
$TokenURI = "https://id.sophos.com/api/v2/oauth2/token"
# TokenRequestBody for oAuth2
$TokenRequestBody = @{
"grant_type" = "client_credentials";
"client_id" = $SecureCredentials.GetNetworkCredential().Username;
"client_secret" = $SecureCredentials.GetNetworkCredential().Password;
"scope" = "token";
}
$TokenRequestHeaders = @{
"content-type" = "application/x-www-form-urlencoded";
}
# Set TLS Version
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# Post Request to SOPHOS OAuth2 token:
$APIAuthResult = (Invoke-RestMethod -Method Post -Uri $TokenURI -Body $TokenRequestBody -Headers $TokenRequestHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
# If there's an error requesting the token, say so, display the error, and break:
if ($ScriptError) {
Write-Output "FAILED - Unable to retreive SOPHOS API Authentication Token - $($ScriptError)"
Break
}
# Set the Token for use later on:
$script:Token = $APIAuthResult.access_token
Write-Host("[API] Get tenant details...")
# SOPHOS Whoami URI:
$WhoamiURI = "https://api.central.sophos.com/whoami/v1"
# SOPHOS Whoami Headers:
$WhoamiRequestHeaders = @{
"Content-Type" = "application/json";
"Authorization" = "Bearer $script:Token";
}
# Get Request SOPHOS Whoami Details:
$APIWhoamiResult = (Invoke-RestMethod -Method Get -Uri $WhoamiURI -Headers $WhoamiRequestHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
# Set TenantID and ApiHost for use later on:
$script:ApiTenantId = $APIWhoamiResult.id
$script:ApiHost = $APIWhoamiResult.apiHosts.dataRegion
# SOPHOS XDR API Headers:
$XDRAPIHeaders = @{
"Authorization" = "Bearer $script:Token";
"X-Tenant-ID" = "$script:ApiTenantId";
"Content-Type" = "application/json";
}
# Hashtable to store data found via the XDR API
$global:macsSeen = @{}
# Get Mac-Addresses from the Firewall logs
Write-Host("[XDR] Get list of devices seen by Firewalls...") -NoNewLine
if ($apihost -ne $null){
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body '{ "adHocQuery": {
"template": "SELECT DISTINCT device_serial_id, src_mac, src_ip FROM xgfw_data WHERE log_component = ''Firewall Rule'' AND src_zone NOT IN (''WAN'', ''VPN'') ORDER BY device_serial_id, src_mac"
}}')
While ($XDR_Response.status -ne "finished") {
start-sleep -s 1
Write-Host(".") -NoNewLine
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?maxSize=1000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
foreach($device in $XDR_Response.items) {
AddMacSeen $($device.src_mac) $($device.src_ip) $($device.device_serial_id)
}
} else {
Write-Output ("Request failed!")
Write-Output ($XDR_Response)
}
}
# Get Mac-Addresses from the ARP-Cache seen by the Endpoints (Clients and Servers)
Write-Host("`n[XDR] Get list of devices seen by Endpoints and Servers...") -NoNewLine
if ($apihost -ne $null){
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body '{ "adHocQuery": {
"template": "SELECT DISTINCT meta_hostname, mac, UPPER(meta_mac_address) as device_mac, address FROM xdr_data WHERE query_name = ''arp_cache'' ORDER BY device_mac"
}}')
While ($XDR_Response.status -ne "finished") {
start-sleep -s 1
Write-Host(".") -NoNewLine
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?maxSize=1000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
foreach($device in $XDR_Response.items) {
# Ignore broadcast and multicast mac addresses
if (-not $device.mac.startswith("01:00:5E") -and $device.mac -ne "FF:FF:FF:FF:FF:FF") {
AddMacSeen $($device.mac) $($device.address) $($device.meta_hostname)
}
}
} else {
Write-Output ("Request failed!")
Write-Output ($XDR_Response)
}
}
# SOPHOS Endpoint API Headers:
Write-Host("`n[API] Get Endpoint details...")
$TentantAPIHeaders = @{
"Authorization" = "Bearer $script:Token";
"X-Tenant-ID" = "$script:ApiTenantId";
}
if ($apihost -ne $null){
# Get Endpoint details via SOPHOS Endpoint API:
$AllEndpoints = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/endpoint/v1/endpoints?pageSize=500&pageTotal=true&fields=hostname,tamperProtectionEnabled,health,macAddresses&view=summary&sort=healthStatus" -Headers $TentantAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
foreach ($Endpoint in $AllEndpoints.items) {
foreach ($mac in $Endpoint.MacAddresses) {
if($global:macsSeen.contains($mac)) {
$global:macsSeen[$mac].hostname = $Endpoint.hostname
$global:macsSeen[$mac].tamperprotection = $Endpoint.tamperProtectionEnabled
$global:macsSeen[$mac].health = $Endpoint.health.overall
}
}
}
# Try to resolve Hostnames for unknown devices via DNS
if ($CheckDNS) {
Write-Host("[DNS] Perform reverse DNS lookups for unknown devices...")
foreach($mac in $global:macsSeen.keys) {
if ($global:macsSeen["$mac"]["hostname"] -eq "") {
foreach($ip in $global:macsSeen["$mac"]["seenby"].keys) {
try {
$ComputerName = [System.Net.Dns]::GetHostEntry($ip).HostName
$ComputerName = $ComputerName.Split('.')[0]
$global:macsSeen["$mac"]["hostname"] = "[" + $ComputerName + "]"
}
catch { }
}
}
}
}
# Try to get Vendor details for the MAC-Addresses
if ($GetVendor) {
Write-Host("[MAC] Get Vendor for MAC Addresses...")
foreach($mac in $global:macsSeen.keys) {
try {
$MAC_Response = Invoke-RestMethod -Method Get -Uri https://api.macvendors.com/$Mac -ErrorAction SilentlyContinue
}
catch { }
$global:macsSeen["$mac"]["vendor"] = $MAC_Response
Start-Sleep -Milliseconds 1000
}
}
# Present results
Write-Host("`n`n")
if ($GetVendor) {
Write-Host("{0,-18} {1, -16} {2, -25} {3, -6} {4, -11} {5, -16} {6}" -f "macAddress", "Vendor", "Hostname", "TP", "Health", "IPAddress(es)", "Seen by")
Write-Host("{0,-18} {1, -16} {2, -25} {3, -6} {4, -11} {5, -16} {6}" -f "=================", "===============","========================", "=====", "==========", "===============", "========================================================")
foreach($mac in $global:macsSeen.keys) {
$NewBlock = $true
foreach($ip in $global:macsSeen["$mac"]["seenby"].keys) {
if ($NewBlock) {
$Vendor = $global:macsSeen["$mac"]["vendor"]
if ($Vendor.Length -gt 15) {
$Vendor = $Vendor.Substring(0,14)
}
Write-Host("{0,-18} {1, -16} {2, -25} {3, -6} {4, -12}" -f $mac, $Vendor, $global:macsSeen["$mac"]["hostname"], $global:macsSeen["$mac"]["tamperprotection"], $global:macsSeen["$mac"]["health"]) -NoNewLine
Write-Host ("{0, -16} {1}" -f $ip, $global:macsSeen["$mac"].seenby["$ip"])
$NewBlock = $false
} else {
Write-Host("{0,-80} {1, -16} {2}" -f "", $ip, $global:macsSeen["$mac"].seenby["$ip"])
}
}
Write-Host("{0,-18} {1, -16} {2, -25} {3, -6} {4, -11} {5, -16} {6}" -f "-----------------", "---------------", "------------------------", "-----", "----------", "---------------", "--------------------------------------------------------")
}
} else {
Write-Host("{0,-18} {1, -25} {2, -6} {3, -11} {4, -16} {5}" -f "macAddress", "Hostname", "TP", "Health", "IPAddress(es)", "Seen by")
Write-Host("{0,-18} {1, -25} {2, -6} {3, -11} {4, -16} {5}" -f "=================", "========================", "=====", "==========", "===============", "========================================================")
foreach($mac in $global:macsSeen.keys) {
$NewBlock = $true
foreach($ip in $global:macsSeen["$mac"]["seenby"].keys) {
if ($NewBlock) {
Write-Host("{0,-18} {1, -25} {2, -6} {3, -12}" -f $mac, $global:macsSeen["$mac"]["hostname"], $global:macsSeen["$mac"]["tamperprotection"], $global:macsSeen["$mac"]["health"]) -NoNewLine
Write-Host ("{0, -16} {1}" -f $ip, $global:macsSeen["$mac"].seenby["$ip"])
$NewBlock = $false
} else {
Write-Host("{0,-63} {1, -16} {2}" -f "", $ip, $global:macsSeen["$mac"].seenby["$ip"])
}
}
Write-Host("{0,-18} {1, -25} {2, -6} {3, -11} {4, -16} {5}" -f "-----------------", "------------------------", "-----", "----------", "---------------", "--------------------------------------------------------")
}
}
(#7) Powershell Script - XDR SHA256 IOC List
Description: Sample code to explore the XDR API. This query inside is meant for a SHA256 hunt and checks for existance of one or more SHA256 entries in the data lake
param($SHA256)
<#
Description: Search for SHA-256 based IoC's
#>
$IoC_list = @()
cls
Write-Host("Sophos XDR Academy - Search for SHA-256 based IoC's")
Write-Host("===================================================")
if ($SHA256 -eq $null){
Write-Host("You need to specify at least one SHA-256 as parameter for this script to work!")
Write-Host("Multiple SHA-256 values can be specified by using a | as separator, for example:")
Write-Host("SHA256-Value1|SHA256-Value2")
exit
}
# Check if Central API Credentials have been stored, if not then prompt the user to add them
if ((Test-Path $env:userprofile\sophos_central_admin.json) -eq $false){
# Prompt for Credentials
$clientId = Read-Host "Please Enter your Client ID"
$clientSecret = Read-Host "Please Enter your Client Secret" -AsSecureString | ConvertFrom-SecureString
# Out to JSON Config File
ConvertTo-Json $ClientID, $ClientSecret | Out-File $env:userprofile\sophos_central_admin.json -Force
}
# Read Credentials from JSON Config File
$credentials = Get-Content $env:userprofile\sophos_central_admin.json | ConvertFrom-Json
$clientId = $credentials[0]
$clientSecret = $credentials[1] | ConvertTo-SecureString
# Create PSCredential Object for Credentials
$SecureCredentials = New-Object System.Management.Automation.PSCredential -ArgumentList $clientId , $clientSecret
#Write-Host("[API] Authenticate to Sophos Central...")
# SOPHOS OAuth URL
$TokenURI = "https://id.sophos.com/api/v2/oauth2/token"
# TokenRequestBody for oAuth2
$TokenRequestBody = @{
"grant_type" = "client_credentials";
"client_id" = $SecureCredentials.GetNetworkCredential().Username;
"client_secret" = $SecureCredentials.GetNetworkCredential().Password;
"scope" = "token";
}
$TokenRequestHeaders = @{
"content-type" = "application/x-www-form-urlencoded";
}
# Set TLS Version
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# Post Request to SOPHOS OAuth2 token:
$APIAuthResult = (Invoke-RestMethod -Method Post -Uri $TokenURI -Body $TokenRequestBody -Headers $TokenRequestHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
# If there's an error requesting the token, say so, display the error, and break:
if ($ScriptError) {
Write-Output "FAILED - Unable to retreive SOPHOS API Authentication Token - $($ScriptError)"
Break
}
# Set the Token for use later on:
$script:Token = $APIAuthResult.access_token
#Write-Host("[API] Get tenant details...")
# SOPHOS Whoami URI:
$WhoamiURI = "https://api.central.sophos.com/whoami/v1"
# SOPHOS Whoami Headers:
$WhoamiRequestHeaders = @{
"Content-Type" = "application/json";
"Authorization" = "Bearer $script:Token";
}
# Get Request SOPHOS Whoami Details:
$APIWhoamiResult = (Invoke-RestMethod -Method Get -Uri $WhoamiURI -Headers $WhoamiRequestHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
# Set TenantID and ApiHost for use later on:
$script:ApiTenantId = $APIWhoamiResult.id
$script:ApiHost = $APIWhoamiResult.apiHosts.dataRegion
# SOPHOS XDR API Headers:
$XDRAPIHeaders = @{
"Authorization" = "Bearer $script:Token";
"X-Tenant-ID" = "$script:ApiTenantId";
"Content-Type" = "application/json";
}
# Build Query Text
$Query = @'
{ "adHocQuery":
{ "template": "WITH Connection_Info AS (\n SELECT DISTINCT meta_hostname, path, query_name, count(*) FROM xdr_data WHERE query_name IN ('open_sockets') GROUP BY meta_hostname, path, query_name\n
UNION ALL\n SELECT DISTINCT meta_hostname, path, query_name, count(address) FROM xdr_data WHERE query_name IN ('listening_ports') AND address NOT IN ('::1', '0.0.0.0', '::') AND path <> ''
GROUP BY meta_hostname, path, query_name \n UNION ALL\n SELECT DISTINCT x1.meta_hostname, x2.new_pid, x1.query_name, count(*) FROM xdr_data AS x1 CROSS JOIN UNNEST(SPLIT(x1.sophos_pids, ',')) AS x2(new_pid)
WHERE x1.query_name IN ('sophos_ips_windows', 'sophos_urls_windows') GROUP BY meta_hostname, new_pid, query_name\n)\n SELECT DISTINCT sha256, meta_hostname AS hostname, query_name, path, CASE query_name
WHEN 'access_productivity_documents' THEN 'N/A' ELSE IF((select count(*) from Connection_Info AS ci WHERE (ci.path = xd.path AND ci.meta_hostname = xd.meta_hostname) OR ci.path = xd.sophos_pid) > 0,'Yes','No')
END AS used_network FROM xdr_data AS xd WHERE regexp_like(sha256, '($$sha256$$)') ORDER BY sha256",
"name":"mini IoC"},"variables":[{"name":"sha256","dataType":"text","value":"#SHA256#"}]
}
'@ -replace "`r" -replace "`n", ' ' -replace "#SHA256#", $SHA256
# Query Data Lake
Write-Host("[XDR] Send Request to run Ad-Hoc Query for finding occurrances of specified SHA256 values...")
if ($apihost -ne $null){
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body $Query)
Write-Host("[XDR] Request sent wait for results...")
While ($XDR_Response.status -ne "finished") {
start-sleep -s 5
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?pageSize=2000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
$XDR_Response.items | Format-Table -Property sha256, hostname, path, query_name, used_network
Write-Host("[XDR] Request completed, " + $XDR_Response.Items.Count + " entries were found!")
} else {
Write-Output ("[XDR] Request failed!")
Write-Output ($XDR_Response)
}
}
(#8) Powershell Sample Script - XDR Vulnerability Checker
Description: Sample code to explore the XDR API for datalake queries aorund common vulnerabilities. It's not the complete spectrum but helps to add more.
<#
Description: Check whether machines in your environement are vulnerable against common attacks
Uses the Sophos Central XDR API for retrieving the data
#>
cls
Write-Host("=================================================================")
Write-Host("Sophos Central XDR Vulnerability Check")
Write-Host("=================================================================`n")
Write-Host("This scripts automatically checks the Sophos Central Data Lake for various vulnerabilities.`nIf vulnerable devices are detected, please use Live Discover to retrieve further details.`n")
# Check if Central API Credentials have been stored, if not then prompt the user to add them
if ((Test-Path $env:userprofile\sophos_central_admin.json) -eq $false){
# Prompt for Credentials
Write-Host("=================================================================")
Write-Host("Sophos Central API Authentication:")
Write-Host("=================================================================")
$clientId = Read-Host "Please Enter your Client ID"
$clientSecret = Read-Host "Please Enter your Client Secret" -AsSecureString | ConvertFrom-SecureString
# Out to JSON Config File
ConvertTo-Json $ClientID, $ClientSecret | Out-File $env:userprofile\sophos_central_admin.json -Force
}
# Read Credentials from JSON Config File
$credentials = Get-Content $env:userprofile\sophos_central_admin.json | ConvertFrom-Json
$clientId = $credentials[0]
$clientSecret = $credentials[1] | ConvertTo-SecureString
# Create PSCredential Object for Credentials
$SecureCredentials = New-Object System.Management.Automation.PSCredential -ArgumentList $clientId , $clientSecret
#Write-Host("[API] Authenticate to Sophos Central...")
# SOPHOS OAuth URL
$TokenURI = "https://id.sophos.com/api/v2/oauth2/token"
# TokenRequestBody for oAuth2
$TokenRequestBody = @{
"grant_type" = "client_credentials";
"client_id" = $SecureCredentials.GetNetworkCredential().Username;
"client_secret" = $SecureCredentials.GetNetworkCredential().Password;
"scope" = "token";
}
$TokenRequestHeaders = @{
"content-type" = "application/x-www-form-urlencoded";
}
# Set TLS Version
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# Post Request to SOPHOS OAuth2 token:
$APIAuthResult = (Invoke-RestMethod -Method Post -Uri $TokenURI -Body $TokenRequestBody -Headers $TokenRequestHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
# If there's an error requesting the token, say so, display the error, and break:
if ($ScriptError) {
Write-Output "FAILED - Unable to retreive SOPHOS API Authentication Token - $($ScriptError)"
Break
}
# Set the Token for use later on:
$script:Token = $APIAuthResult.access_token
#Write-Host("[API] Get tenant details...")
# SOPHOS Whoami URI:
$WhoamiURI = "https://api.central.sophos.com/whoami/v1"
# SOPHOS Whoami Headers:
$WhoamiRequestHeaders = @{
"Content-Type" = "application/json";
"Authorization" = "Bearer $script:Token";
}
# Get Request SOPHOS Whoami Details:
$APIWhoamiResult = (Invoke-RestMethod -Method Get -Uri $WhoamiURI -Headers $WhoamiRequestHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
# Set TenantID and ApiHost for use later on:
$script:ApiTenantId = $APIWhoamiResult.id
$script:ApiHost = $APIWhoamiResult.apiHosts.dataRegion
# SOPHOS XDR API Headers:
$XDRAPIHeaders = @{
"Authorization" = "Bearer $script:Token";
"X-Tenant-ID" = "$script:ApiTenantId";
"Content-Type" = "application/json";
}
if ($apihost -ne $null){
###################################################################################################################################
# Spectre and Meltdown vulnerabilities - Checks whether Spectre and Meltdown vulnerability mitigations are missing
###################################################################################################################################
Write-Host("`n=================================================================")
Write-Host("[Data Lake] Check Spectre & Meltdown vulnerability:")
Write-Host("=================================================================")
$vulnerableDevices = @()
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body '{ "adHocQuery": {
"template": "SELECT meta_hostname, count FROM xdr_data WHERE query_name = ''vulnerability_spectre_meltdown''"
}}')
While ($XDR_Response.status -ne "finished") {
start-sleep -s 1
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?maxSize=1000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
foreach($device in $XDR_Response.items) {
if ($device.count.Equals(1)){
$vulnerableDevices += $device.meta_hostname
}
}
Write-Host("Vulnerable Devices:`n")
($vulnerableDevices | select -Unique) | Format-Table
} else {
Write-Output ("Request failed!")
Write-Output ($XDR_Response)
}
###################################################################################################################################
# UAC disabled vulnerability - Checks whether User Account Control is turned off.
###################################################################################################################################
Write-Host("`n=================================================================")
Write-Host("[Data Lake] Check UAC vulnerability:")
Write-Host("=================================================================")
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body '{ "adHocQuery": {
"template": "SELECT meta_hostname, count FROM xdr_data WHERE query_name = ''vulnerability_uac_disabled''"
}}')
While ($XDR_Response.status -ne "finished") {
start-sleep -s 1
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?maxSize=1000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
Write-Host("Vulnerable Devices:")
($XDR_Response.items | select -Unique) | Format-Table -HideTableHeaders
} else {
Write-Output ("Request failed!")
Write-Output ($XDR_Response)
}
###################################################################################################################################
# SecureBoot vulnerability - Checks whether SecureBoot is supported but not turned on.
###################################################################################################################################
Write-Host("=================================================================")
Write-Host("[Data Lake] Check Secureboot vulnerability:")
Write-Host("=================================================================")
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body '{ "adHocQuery": {
"template": "SELECT meta_hostname FROM xdr_data WHERE query_name = ''vulnerability_secureboot''"
}}')
While ($XDR_Response.status -ne "finished") {
start-sleep -s 1
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?maxSize=1000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
Write-Host("Vulnerable Devices:")
($XDR_Response.items | select -Unique) | Format-Table -HideTableHeaders
} else {
Write-Output ("Request failed!")
Write-Output ($XDR_Response)
}
###################################################################################################################################
# SEHOP validation vulnerability - Checks whether Structured Exception Handling Overwrite Protection is turned off.
###################################################################################################################################
Write-Host("=================================================================")
Write-Host("[Data Lake] Check SEHOP validation vulnerability :")
Write-Host("=================================================================")
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body '{ "adHocQuery": {
"template": "SELECT meta_hostname FROM xdr_data WHERE query_name = ''vulnerability_sehop_validation''"
}}')
While ($XDR_Response.status -ne "finished") {
start-sleep -s 1
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?maxSize=1000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
Write-Host("Vulnerable Devices:")
($XDR_Response.items | select -Unique) | Format-Table -HideTableHeaders
} else {
Write-Output ("Request failed!")
Write-Output ($XDR_Response)
}
###################################################################################################################################
# SEHOP Vulnerability - Checks whether Structured Exception Handling Overwrite Protection is turned off.
###################################################################################################################################
Write-Host("=================================================================")
Write-Host("[Data Lake] Check SEHOP vulnerability:")
Write-Host("=================================================================")
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body '{ "adHocQuery": {
"template": "SELECT meta_hostname FROM xdr_data WHERE query_name = ''vulnerability_sehop''"
}}')
While ($XDR_Response.status -ne "finished") {
start-sleep -s 1
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?maxSize=1000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
Write-Host("Vulnerable Devices:")
($XDR_Response.items | select -Unique) | Format-Table -HideTableHeaders
} else {
Write-Output ("Request failed!")
Write-Output ($XDR_Response)
}
###################################################################################################################################
# App compatibility vulnerability - Lists applications with special compatibility set for an executable for all users.
###################################################################################################################################
Write-Host("=================================================================")
Write-Host("[Data Lake] Check App compatibility vulnerability:")
Write-Host("=================================================================")
$vulnerableDevices = @()
$XDR_Response = (Invoke-RestMethod -Method POST -Uri $script:ApiHost"/xdr-query/v1/queries/runs" -Headers $XDRAPIHeaders -Body '{ "adHocQuery": {
"template": "SELECT meta_hostname, count FROM xdr_data WHERE query_name = ''vulnerability_app_compatibility''"
}}')
While ($XDR_Response.status -ne "finished") {
start-sleep -s 1
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
}
if ($XDR_Response.result -eq "succeeded") {
$XDR_Response = (Invoke-RestMethod -Method Get -Uri $script:ApiHost"/xdr-query/v1/queries/runs/$($XDR_Response.id)/results?maxSize=1000" -Headers $XDRAPIHeaders -ErrorAction SilentlyContinue -ErrorVariable ScriptError)
Write-Host("Vulnerable Devices:")
($XDR_Response.items | select -Unique) | Format-Table -HideTableHeaders
} else {
Write-Output ("Request failed!")
Write-Output ($XDR_Response)
}
}
Updated disclaimer
[edited by: Qoosh at 9:49 PM (GMT -7) on 31 Mar 2023]
