Sep 09 2021 02:51 AM
Greetings, I just ran into something interesting. I have created a analytics rule that looks like this:
let exceptionUsers = IdentityInfo
| where TimeGenerated > ago(22d) //IdentityInfo refreshes its information every 21 days
| where todynamic(GroupMembership) contains "SG-U Guest users excluded from CA blocked countries"
| distinct MailAddress;
//Creates a set of users that is to be ignored when looking for logins outside of europe.
SigninLogs
| where TimeGenerated > ago(4h)
| where Location !in ( "AL","AD","AM","AT","BY","BE","BA","BG","CH","CY","CZ","DE","DK","EE","ES","FO","FI","FR","GB","GE","GI","GR","HU","HR","IE","IS","IT","LI","LT","LU","LV","MC","MK","MT","NO","NL","PL","PT","RO","RU","SE","SI","SK","SM","TR","UA","VA","SJ","") // List of country codes in europe.
| where UserPrincipalName !in ( exceptionUsers )
| extend AccountCustomEntity = Identity
| extend IPCustomEntity = IPAddress
Might not be the greatest of queries, but still, I run this query and get no results. As i expect. However, the analytics rule with this configuration still manages to trigger.
This is the view from the analytics rule wizard when i test with current data.
The last spike indicates the one i saw today. How can the analytics rule wizard get different results from the same query i run in the Logs tab?
Sep 09 2021 05:14 AM
@stianhoydal The analytic rule will ignore any time details set in the query. As it states in the Set rule logic tab: Any time details set here will be within the scope defined below in the Query scheduling fields.
That may have something to do with it. Try running the query manually but use the value that is set In the Lookup data from the last field and see if that returns any results.
Sep 09 2021 05:32 AM
Sep 10 2021 06:24 AM
So i figured out a simple workaround, but still the query wizard shows that it would trigger the alarm several times although it shouldn't have.
let excludedUsers = GuestAccountsExcludedFromCAPolicy_CL
| distinct UserEmail_s;
SigninLogs
| where Location !in ( "AL","AD","AM","AT","BY","BE","BA","BG","CH","CY","CZ","DE","DK","EE","ES","FO","FI","FR","GB","GE","GI","GR","HU","HR","IE","IS","IT","LI","LT","LU","LV","MC","MK","MT","NO","NL","PL","PT","RO","RU","SE","SI","SK","SM","TR","UA","VA","SJ","") // List of country codes in europe.
| where UserPrincipalName !in (excludedUsers)
| extend AccountCustomEntity = Identity
| extend IPCustomEntity = IPAddress
The GuestAccountsExcludedFromCAPolicy_CL is simply a table filled with users fetched from AAD via logic apps.
Still the query wizard shows that it would trigged multiple alarms within the last 48 hours although there should only be one.
It seems to me as if the query is just ignoring the line
| where UserPrincipalName !in (excludedUsers)
because it would be correct otherwise, but the whole point is to not get alerted when one of the excluded members tries to log on.
Anyone have any ideas on why this is happening, or potential solutions?
Sep 10 2021 12:06 PM
Sep 14 2021 04:52 AM