Forum Discussion
Log Analytics query the logs that are not in IP range
Hi All,
I'm struggling with writing a query that will find sign-ins in logs that are not in IP ranges.
So we have Log Analytics Workplace which is collecting sign-in logs. And we want to trigger an alert when an account is signed in from an IP that is not in one of our IP ranges.
We have a lot of known network rages and we have to use an external repository like github with a txt file of those rages.
I've tried to use the function "ipv4_is_match()", but from my understanding, it's looking just like to like, but not looking foreach.
That being said I've tried something like this, but it doesn't work. Does anyone experienced here can help with writing such a query, or even answer if it's possible?
let ipList = externaldata (IPAddress:string) [
@"https://raw.githubusercontent.com/NameOfRepository/IPv4Range.txt"
];
SigninLogs
| where UserPrincipalName contains "email address removed for privacy reasons"
| where IsInteractive == true
| where not (ipv4_is_match(IPAddress , ipList)
1 Reply
It is indeed feasible to query sign-in logs against external IP ranges in Azure Log Analytics. To achieve this, the externaldata operator must be used correctly to import the list of ranges, and the ipv4_is_match() function should be applied in a manner that evaluates each log entry against the imported dataset. Microsoft’s official documentation confirms that ipv4_is_match() supports CIDR notation and can be effectively combined with externaldata to perform this type of validation.
https://learn.microsoft.com/en-us/kusto/query/ipv4-is-match-function?view=microsoft-fabric
https://learn.microsoft.com/en-us/kusto/query/externaldata-operator?view=microsoft-fabric
