I am preparing a talk for 2024 to showcase the capabilities of Kusto. As part of my preparation, I have compiled a set of KQL queries to demonstrate some tricks to analyze Azure traffic by focusing on IPv4 matching across different logs, even when you don’t know the name of the field you are looking the values in.
Query breakdown
- Dynamic Data : Dynamic data is handled with using pack_all and mv-apply, a neat way to deal with varied data structures. KQL Dynamic Data.
- Selective Data Projection: You can tailor the query’s focus by selectively projecting fields related to IP addresses – KQL Projection Operators.
- External Data Integration: Service tag data pulled from external JSON source KQL externaldata Operator.
- Practical IPv4 Address Identification: The query utilizes a regular expression for identifying and validating IPv4 addresses, a trick I saw on Stack Overflow. This ensures that IP addresses are accurately extracted from fields that migth other text data too- KQL Regular Expressions.
- Unified Data View: The query merges data from a variety of Azure logs into a single dataset KQL Union Operator .
- IP Address Analysis: It not only counts IP occurrences but also associates each IP address with specific log types. KQL Aggregation Functions.
- Azure Service Tag Cross-Referencing: The query matches each IP address against Azure service IP ranges. KQL ipv4_is_in_any_range Function.
- Service Range Summarization: The output summarizes IP addresses, counts , associated logs, and corresponding Azure service ranges. KQL Summarize Operator
// IPV4 matching for all Azure Traffic across summarized by IP and count
//For checking all the IP's https://stackoverflow.com/questions/5284147/validating-ipv4-addresses-with-regexp, this has to be used to get IP's extracted also from non IP fields (console messages etc)
// alternatively Filter with // | project-keep *Ip*, Type,*IP*, Message //if needed to somewhat limit the results
// Add more lines
let azIp =externaldata(changeNumber: string, cloud: string, values: dynamic)
["https://download.microsoft.com/download/7/1/D/71D86715-5596-4529-9B13-DA13A5DE5B63/ServiceTags_Public_20231127.json"]
with(format='multijson')
| mv-expand values
| project service =values.id, prefix =values.properties.addressPrefixes
| summarize list = make_list(pack_all(true));
//
// You can also use seach "*", but it becomes very slow
// feel free to modify union with any log sources desired
union
AzureDiagnostics,
MicrosoftGraphActivityLogs,
AzureActivity,
AADNonInteractiveUserSignInLogs,
AADServicePrincipalSignInLogs,
AADManagedIdentitySignInLogs,
SigninLogs
| extend prop = pack_all(true)
| mv-apply prop on (
mv-expand kind = array prop
| extend onlyVal=tostring(prop[1])
| extend LogKey = prop[0]
| extend isIp = extract(@"^((25[0-5]|(2[0-4]|1\d|[1-9]|)\d)\.?\b){4}$", 0, onlyVal)
| where isnotempty(isIp)
)
| summarize count(), make_set(strcat(LogKey, '-', Type)) by isIp
| mv-apply ranges= toscalar(azIp) to typeof(dynamic) on (
extend matchfound = ipv4_is_in_any_range(isIp, ranges.prefix)
| extend result = ranges.service
| where matchfound == true
)
| summarize AzureRanges = make_set(result, 2) by isIp, count_, logs = tostring(set_)
Tips:
Reduce the amount of sources or time range when you are starting with query
Version without Azure IP matching to simplified log match with Union *
union Databricks*
| extend prop = pack_all(true)
| mv-apply prop on (
mv-expand kind = array prop
| extend onlyVal=tostring(prop[1])
| extend LogKey = prop[0]
| extend isIp = extract(@"^((25[0-5]|(2[0-4]|1\d|[1-9]|)\d)\.?\b){4}$", 0, onlyVal)
| where isnotempty(isIp)
)
| summarize count(), make_set(strcat(LogKey, '-', Type)) by isIp
In conclusion, leveraging the power of Kusto Query Language (KQL) and its various operators, enables you to efficiently analyze Azure traffic by focusing on IPv4 address matching across different logs.
And as always if you find ways to get even more insights or query performance updates, then feel free to contribute 🙂
SecureCloudBlog 
0 comments on “Azure Traffic Analysis with Kusto: Harnessing the capabilities of Kusto Query Language for in-depth Azure traffic analysis of IP addresses”