Zscalar IP Sign In Check
Query
//Experimental, query needs optimization.
let Zscaler = externaldata (prefixes: dynamic) [@'https://config.zscaler.com/api/zscaler.net/future/json'] with (format=json, ignoreFirstRecord=True)
//| mv-expand prefixes
//| summarize by ZscalerIP= tostring(prefixes)
| extend Dummy = 1;
SigninLogs
| where TimeGenerated > ago(90d)
| where ResultType == 0
| extend Dummy = 1
| join kind=fullouter Zscaler on Dummy //map every sign-in to all zscaler Ips
| where ipv4_is_in_any_range(IPAddress, prefixes)
| project-away prefixesExplanation
This KQL (Kusto Query Language) query is designed to analyze sign-in logs and correlate them with a list of IP address prefixes from Zscaler. Here's a simple breakdown of what the query does:
-
Fetch Zscaler IP Prefixes: It retrieves a list of IP address prefixes from an external JSON source provided by Zscaler. This data is stored in a variable called
Zscaler. -
Prepare for Joining: A dummy column with a constant value of 1 is added to both the Zscaler data and the sign-in logs to facilitate a join operation.
-
Filter Sign-in Logs: It filters the sign-in logs to include only successful sign-ins (where
ResultTypeis 0) that occurred in the last 90 days. -
Join Data: It performs a full outer join between the sign-in logs and the Zscaler IP prefixes using the dummy column. This effectively maps every sign-in attempt to all Zscaler IPs.
-
Filter by IP Range: It filters the results to include only those sign-ins where the IP address falls within any of the Zscaler IP ranges.
-
Clean Up: It removes the
prefixescolumn from the final output, as it is no longer needed.
Overall, the query is trying to identify successful sign-in attempts that originated from IP addresses within the Zscaler network over the past 90 days. The comment at the top indicates that the query is experimental and may need optimization for better performance.
Details
Jay Kerai
Released: April 29, 2025
Tables
Keywords
Operators