KQL Search

Query Details

HomeAI ToolsDevice Query

Block List Project Device Network Events

Query

let PornBlockListProj = externaldata(type: string)[@"https://raw.githubusercontent.com/blocklistproject/Lists/master/porn.txt"] with (format="csv", ignoreFirstRecord=False)
| where type !startswith "#"
| extend RemoteUrl = replace_string(replace_string(type,"0.0.0.0", "")," ","")
| project RemoteUrl;
let PiracyBlockListProj=  externaldata(type: string)[@"https://raw.githubusercontent.com/blocklistproject/Lists/master/piracy.txt"] with (format="csv", ignoreFirstRecord=False)
| where type !startswith "#"
| extend RemoteUrl = replace_string(replace_string(type,"0.0.0.0", "")," ","")
| project RemoteUrl;
let TorrentBlockListProj = externaldata(type: string)[@"https://raw.githubusercontent.com/blocklistproject/Lists/master/torrent.txt"] with (format="csv", ignoreFirstRecord=False)
| where type !startswith "#"
| extend RemoteUrl = replace_string(replace_string(type,"0.0.0.0", "")," ","")
| project RemoteUrl;
let PhishingBlockListProj = externaldata(type: string)[@"https://raw.githubusercontent.com/blocklistproject/Lists/master/phishing.txt"] with (format="csv", ignoreFirstRecord=False)
| where type !startswith "#"
| extend RemoteUrl = replace_string(replace_string(type,"0.0.0.0", "")," ","")
| project RemoteUrl;
let MalwareBlockListProj = externaldata(type: string)[@"https://raw.githubusercontent.com/blocklistproject/Lists/master/malware.txt"] with (format="csv", ignoreFirstRecord=False)
| where type !startswith "#"
| extend RemoteUrl = replace_string(replace_string(type,"0.0.0.0", "")," ","")
| project RemoteUrl;
let RansomBlockListProj = externaldata(type: string)[@"https://raw.githubusercontent.com/blocklistproject/Lists/master/ransomware.txt"] with (format="csv", ignoreFirstRecord=False)
| where type !startswith "#"
| extend RemoteUrl = replace_string(replace_string(type,"0.0.0.0", "")," ","")
| project RemoteUrl;
DeviceNetworkEvents
| where RemoteUrl in~(PornBlockListProj) or RemoteUrl in~(TorrentBlockListProj) or RemoteUrl in~(PiracyBlockListProj) or RemoteUrl in~(PhishingBlockListProj) or  RemoteUrl in~(MalwareBlockListProj) or RemoteUrl in~(RansomBlockListProj)
|extend VT_domain = iff(isnotempty(RemoteUrl),strcat(@"https://www.virustotal.com/gui/domain/",RemoteUrl),RemoteUrl)
| summarize count() by RemoteUrl,VT_domain

Explanation

This KQL (Kusto Query Language) query is designed to identify and summarize network events related to potentially harmful or unwanted websites. Here's a simplified breakdown of what the query does:

  1. Data Import and Cleaning:

    • The query imports several blocklists from external sources (GitHub) related to different categories of harmful or unwanted content: porn, piracy, torrent, phishing, malware, and ransomware.
    • For each blocklist, it removes any lines starting with "#" (which are typically comments) and cleans up the URLs by removing "0.0.0.0" and any spaces.

  2. Network Event Filtering:

    • It then checks the DeviceNetworkEvents table to see if any recorded network events involve URLs that match any of the URLs in the blocklists.

  3. URL Enrichment:

    • For each matching event, it creates a link to VirusTotal, a website that provides information about the safety of URLs, by appending the URL to a base VirusTotal domain URL.

  4. Summarization:

    • Finally, it summarizes the results by counting how many times each URL appears in the network events and provides a link to VirusTotal for further investigation.

In summary, this query is used to detect and count network events involving potentially harmful or unwanted URLs by cross-referencing them with known blocklists, and it provides additional context by linking to VirusTotal for each URL.

Details

Jay Kerai profile picture

Jay Kerai

Released: February 4, 2025

Tables

DeviceNetworkEvents

Keywords

DeviceNetworkEvents

Operators

letexternaldatawithformatignoreFirstRecordwhere!startswithextendreplace_stringprojectin~oriffisnotemptystrcatsummarizecountby

Actions