Query Details
Domain Extensions By Country Analysis
Query
//This query analyzes domain extensions usage by country
//Excludes common extensions like .nl and .com
DeviceNetworkEvents
| where RemoteUrl !endswith ".nl" and RemoteUrl !endswith ".com"
| extend DomainExtension = extract(@"\.([a-z]+)(:|/|$)", 1, RemoteUrl)
| where DomainExtension in ("ru", "be", "fr", "de", "uk", "it", "es", "pt", "se", "no", "fi", "pl", "ch", "at", "cz", "gr", "ro", "bg", "hu", "ua", "sk", "si", "hr", "lt", "lv", "ee", "is", "ie", "tr", "by", "md", "rs", "mk", "me", "ba", "al", "ge", "am", "az", "kg", "kz", "uz", "tj", "tm", "cn", "jp", "kr", "in", "pk", "id", "my", "sg", "ph", "th", "vn", "au", "nz", "za", "eg", "dz", "ma", "ng", "ke", "gh", "za", "bw", "mu", "tn", "ar", "br", "cl", "mx", "pe", "ve", "uy", "py", "bo", "cr", "pa", "sv", "gt", "hn", "ni", "do", "cu", "ht")
| summarize Count = count() by DomainExtension
| order by Count desc Explanation
This query is designed to analyze the usage of various domain extensions by country, excluding the common extensions ".nl" and ".com". Here's a simple breakdown of what it does:
-
Data Source: It starts by looking at network events related to devices.
-
Filtering: It filters out any URLs that end with ".nl" or ".com" to focus on less common domain extensions.
-
Extracting Domain Extensions: It extracts the domain extension from the remaining URLs. The domain extension is the part of the URL that comes after the last dot (e.g., ".ru" in "example.ru").
-
Specific Extensions: It further filters the data to include only specific domain extensions from a predefined list, which includes country-specific extensions like ".ru" for Russia, ".fr" for France, ".de" for Germany, and many others.
-
Counting: It counts how many times each of these domain extensions appears in the data.
-
Sorting: Finally, it sorts the results in descending order based on the count, so the most frequently occurring domain extensions appear first.
In summary, the query identifies and ranks the usage frequency of specific country-related domain extensions in network events, excluding the common ".nl" and ".com" extensions.
Details
User Submission
Released: November 10, 2024
Tables
Keywords
Operators