[QueryCorner][January2023] Live Discover - Network: Processes with an open network connection

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

Understanding 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