Query Details
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 Zscaler on Dummy //map every sign-in to all zscaler Ips | where ipv4_is_match(IPAddress, ZscalerIP)
Explanation
This query is designed to analyze sign-in logs and identify successful sign-ins that match specific IP addresses associated with Zscaler. Here's a simplified breakdown of what the query does:
-
Fetch Zscaler IPs: It retrieves a list of IP address prefixes from a Zscaler API endpoint. This data is in JSON format, and the query expands these prefixes into individual entries.
-
Prepare Zscaler Data: Each IP prefix is converted into a string and given a dummy value of 1 to facilitate joining with another dataset.
-
Filter Sign-in Logs: It looks at sign-in logs from the past 90 days and filters for successful sign-ins (where
ResultTypeis 0). -
Join Data: The query joins the sign-in logs with the Zscaler IP data using the dummy value, effectively mapping each sign-in attempt to all Zscaler IPs.
-
Match IP Addresses: Finally, it filters the results to find sign-ins where the IP address matches one of the Zscaler IPs.
In essence, the query identifies successful sign-ins from the past 90 days that originated from IP addresses associated with Zscaler.
Details
Jay Kerai
Released: April 29, 2025
Tables
Keywords
Operators