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
If you have not traversed the XDR journals, please review the rich data journals we are logging on our endpoints and servers. This data is available for the last 90 days on agent. You can create your own queries, but in this post, we are going to start the New Year out by focusing on what ships with the product. SQL can be daunting at first, but with designer mode disabled, it allows the security administrator to see reports based on the query description.
This post will focus on a Live Discover Query, under the Network category, called "Processes with an open network connection."
Prerequisites
You must have XDR enabled in your environment. We do not need designer mode enabled.
Network: Processes with an open network connection
Over time, the platform will report on the expected system impact, data transferred, and execution time. Do not be alarmed if this is empty.
Results
The results will give any security administrator insight into their active connections. Quick reports like this can expose "shadow IT" programs and connections that are not authorized.
Results Breakdown:
| Column Name | Description | Pivot Options |
| epName | Hostname | Scan device, Live response, Data Lake Queries |
| sophos_pid | ID of the process + start time | Live Discover + Data Lake Queries |
| path | pathname of the process | Live Discover + Data Lake Queries |
| local_port | socket local port | N/A |
| remote_address | socket remote address (ignores localhost loopback) | Live Discover + Data Lake Queries and 3rd Enrichment (ex: VirusTotal) |
| remote_report | socket remote port | N/A |
| local_rep | machine learning local rep, where -1 is unknown, local is 0-100 | N/A |
| global_rep | machine learning global rep, where -1 is unknown, global is 0-100 | N/A |
| ml_score | machine learning malware score, where -1 is unknown, score from successful ML PE PUA scan is 0-100 | N/A |
| pua_score | machine learning potentially unwanted applications (PUA) score, where -1 is unknown, the score from a successful ML PE PUA is 0-100 | N/A |
| sha256 | SHA-256 hash of the file | Live Discover + Data Lake Queries and 3rd Enrichment (ex: VirusTotal) |
Understanding
Sophos Lab Static Scoring
| Category | Score |
| Malware | 0 - 19 |
| PUA | 20 - 29 |
| Unknown/Suspicious | 30 - 69 |
| Known Good | 70 - 100 |
"Pivoting" is taking a portion of the data in the report to investigate further. If you discovered that the ScreenConnect software highlighted in the screenshot should not be installed, you may use a column like "sha256" to query where else it is present. Pivoting may call upon one or more queries to answer questions you have. The "pivots" will open a new tab for your investigation. Other options include scanning the device, Live Response control, or using one of the enrichment data connectors to find out more information.
You can setup a free account at https://ipstack.com if you want to see how to add in your own API source.
Happy querying!
-jk
Added Disclaimer
[edited by: GlennSen at 3:55 PM (GMT -7) on 5 Apr 2023]