Connections To Abused TL Ds Device Network Events

Query

//Get spamhaus TLD list
let spamhausTLDS = (externaldata(['TLD']: string, ['Badness Index']: string)
[h@"https://raw.githubusercontent.com/cyb3rmik3/Hunting-Lists/main/spamhaus-abused-tlds.csv"]with (format="csv"))
| extend TLD = iif(TLD startswith ".",replace_string(TLD,".",""),TLD)
| where TLD != "TLD";
//Get info-sec TLD list
let infosecTLDs = (externaldata(TLD: string)
[h@"https://www.info-sec.ca/tld-block.txt"]with (format="csv"))
| where TLD !startswith "#";
//Union lists together
let list_tlds = union spamhausTLDS, infosecTLDs
| project TLD;
//Exclude potential noise by uncommenting line below
//| where TLD !in ("live","info","link")
DeviceNetworkEvents
| where ActionType == "ConnectionSuccess"
| where isnotempty(RemoteUrl)
//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)
//Validate parsed domain by checking TLD against TLDs from threat feed and drop domains where there is no chance of a match
| where tld in~ (list_tlds)
| 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
//https://github.com/jkerai1/TLD-TABL-Block

Explanation

This KQL (Kusto Query Language) query is designed to identify potentially malicious network connections by analyzing the top-level domains (TLDs) of URLs accessed by devices. Here's a simplified breakdown of what the query does:

Fetch Spamhaus TLD List: It retrieves a list of TLDs known for abuse from Spamhaus, a well-known threat intelligence source. The data is fetched from a CSV file hosted on GitHub.
Fetch Info-Sec TLD List: It also retrieves another list of TLDs from an Info-Sec source, which is also hosted on GitHub.
Combine TLD Lists: Both lists are combined into a single list of TLDs to be used for checking against network events.
Filter Device Network Events: It filters network events to only include successful connections (ConnectionSuccess) where the RemoteUrl is not empty.
Process URLs: The query processes the URLs by:
- Removing "http://" or "https://" from the start.
- Trimming URLs to keep only the domain part.
- Extracting the TLD from the domain.
Generate Links: For each URL, it creates links to VirusTotal and AbuseIPDB for further investigation.
Validate TLDs: It checks if the extracted TLDs from the URLs are present in the combined threat intelligence TLD list. Only URLs with matching TLDs are retained.
Organize Output: The final output is organized to include relevant information such as the time of the event, TLD, investigation links, URL, user account, and device name.
Optional Noise Exclusion: There is an optional line (commented out) that can be used to exclude certain common TLDs like "live", "info", and "link" to reduce noise in the results.

Overall, this query helps in identifying network connections to potentially malicious domains by leveraging threat intelligence on TLDs.

Details

Jay Kerai

Released: February 23, 2025

Tables

DeviceNetworkEvents

Keywords

DeviceNetworkEventsRemoteUrlIPTimeGeneratedNameAccount

Operators

externaldatah@withformatextendiifstartswithreplace_stringwhereunionprojectproject-awayproject-reorderisnotemptyindexofcontainssubstringtostringsplitstrcatin~

Actions

GitHub

KQL Search