Query Details
TLD By Count For Device Network Events
Query
DeviceNetworkEvents
| where TimeGenerated > ago(90d)
| where ActionType == "ConnectionSuccess"
| where isnotempty(RemoteUrl)
| where RemoteIPType == "Public"
//Remove start and end of URL
| extend RemoteUrl = iif(RemoteUrl startswith "https://",replace_string(RemoteUrl,"https://",""),iif(RemoteUrl startswith "http://",replace_string(RemoteUrl, "http://",""),RemoteUrl))
| extend slashindex = indexof(RemoteUrl, "/")
| extend RemoteUrl = iif(RemoteUrl contains "/",substring(RemoteUrl, 0, slashindex),RemoteUrl)
| extend tld = tostring(split(RemoteUrl, ".")[-1])
| extend VT_Link = strcat("https://www.virustotal.com/gui/domain/",RemoteUrl)
| extend Abuse_Link = strcat("https://www.abuseipdb.com/check/",RemoteIP)
| project-away slashindex
| project-reorder TimeGenerated, tld, VT_Link, Abuse_Link, RemoteUrl, InitiatingProcessAccountUpn, DeviceName
| extend Host_0_HostName = DeviceName
| extend Account_0_Name = InitiatingProcessAccountUpn
| extend URL_0_Url = RemoteUrl
| summarize count() by tld
Explanation
This KQL (Kusto Query Language) query is designed to analyze network connection events from devices over the past 90 days. Here's a simplified breakdown of what the query does:
-
Data Source: It starts by looking at the
DeviceNetworkEventstable. -
Time Filter: It filters the data to include only events generated in the last 90 days.
-
Action Type: It further narrows down the data to events where the connection was successful (
ActionType == "ConnectionSuccess"). -
Remote URL and IP Type: It ensures that the
RemoteUrlfield is not empty and that theRemoteIPTypeis "Public". -
URL Processing:
- It removes "https://" or "http://" from the beginning of the
RemoteUrl. - It trims the URL to remove any path after the domain name, leaving just the domain.
- It removes "https://" or "http://" from the beginning of the
-
Top-Level Domain Extraction: It extracts the top-level domain (TLD) from the
RemoteUrl. -
External Links:
- It creates a link to VirusTotal for the domain.
- It creates a link to AbuseIPDB for checking the
RemoteIP.
-
Data Organization:
- It removes unnecessary columns and reorders the remaining ones.
- It renames some columns for clarity, such as
DeviceNametoHost_0_HostNameandInitiatingProcessAccountUpntoAccount_0_Name.
-
Summarization: Finally, it summarizes the data by counting the number of events for each top-level domain (TLD).
In essence, this query is used to track and analyze successful public network connections from devices, focusing on the domains being accessed, and provides links for further investigation into the domains and IPs involved.
Details
Jay Kerai
Released: February 23, 2025
Tables
Keywords
Operators