Adult Content MDE Device Network Events

Query

//Use Web Content Filtering in MDE to block Adult Content https://learn.microsoft.com/en-us/defender-endpoint/web-content-filtering?WT.mc_id=MVP_473477
let Adult0 = externaldata(type: string)[@"https://raw.githubusercontent.com/4skinSkywalker/Anti-Porn-HOSTS-File/master/HOSTS.txt"] with (format="csv", ignoreFirstRecord=False);
let Adult1 = externaldata(type: string,type2:string, type3:string)[@"https://raw.githubusercontent.com/StevenBlack/hosts/master/alternates/porn/hosts"] with (format="csv", ignoreFirstRecord=False);
let Adult2 = externaldata(type: string)[@"https://raw.githubusercontent.com/columndeeply/hosts/main/hosts01"] with (format="csv", ignoreFirstRecord=False);
let Adult3 = externaldata(type: string)[@"https://raw.githubusercontent.com/columndeeply/hosts/main/hosts02"] with (format="csv", ignoreFirstRecord=False);
let Adult4 = externaldata(type: string)[@"https://raw.githubusercontent.com/columndeeply/hosts/main/hosts03"] with (format="csv", ignoreFirstRecord=False);
let Adult5 = externaldata(type: string)[@"https://raw.githubusercontent.com/columndeeply/hosts/main/hosts04"] with (format="csv", ignoreFirstRecord=False);
let Adult6 = externaldata(type: string)[@"https://raw.githubusercontent.com/Bon-Appetit/porn-domains/master/block.txt"] with (format="csv", ignoreFirstRecord=False);
let Adult7 = externaldata(type: string)[@"https://gist.githubusercontent.com/sibaram-sahu/5248d7600a24284f580219b29d178c49/raw/b35fdaf7a8685b536da0022102e125df70c50eb1/pornsite-list.txt"] with (format="csv", ignoreFirstRecord=False);
let AdultDomains =Adult7
//| union Adult0, Adult1,Adult2,Adult3,Adult4,Adult5,Adult6 //Substitute in Lists as lists are too large to include all in one query
| extend RemoteUrl = replace_string(replace_string(replace_string(replace_string(replace_string(replace_string(replace_string(replace_string(type, "127.0.0.1 ",""),"0.0.0.0    ",""),"    127.0.0.1	      ",""),"	  127.0.0.1       ",""),"https://",""),"http://",""),"www.","")," ","")
| where RemoteUrl <> "	"
| where RemoteUrl <> "	"
| where RemoteUrl <> "    #"
| where RemoteUrl <> "    # get rid of this shit"
|distinct RemoteUrl;
DeviceNetworkEvents
| join AdultDomains on RemoteUrl 
| summarize count() by RemoteUrl

Explanation

This KQL query is designed to help block adult content by using web content filtering in Microsoft Defender for Endpoint (MDE). Here's a simplified explanation of what the query does:

Data Sources: The query pulls in lists of known adult content domains from several external sources. These lists are hosted on GitHub and other platforms. Each list is loaded into a separate variable (e.g., Adult0, Adult1, etc.).
Data Cleaning: The query processes these lists to clean up the domain names. It removes unnecessary prefixes like "127.0.0.1", "0.0.0.0", "http://", "https://", and "www.", as well as any extra spaces or unwanted characters.
Domain Consolidation: The cleaned domain names are consolidated into a single list called AdultDomains. Note that the query currently only uses Adult7 for demonstration purposes, but it can be expanded to include other lists by uncommenting the union line.
Event Matching: The query then checks network events (DeviceNetworkEvents) to see if any of the domains in AdultDomains have been accessed. It does this by joining the network events with the list of adult domains based on the RemoteUrl.
Result Summarization: Finally, the query summarizes the results by counting how many times each adult domain has been accessed, providing a distinct list of these domains and their access counts.

In essence, this query is used to identify and count access attempts to known adult content domains, which can then be used to block such content using web content filtering in MDE.

Details

Jay Kerai

Released: October 3, 2025

Tables

DeviceNetworkEvents

Keywords

WebContentFilteringMDEAdultDeviceNetworkEvents

Operators

letexternaldatawithextendreplace_stringwheredistinctjoinsummarizeby

Actions

GitHub

KQL Search