Hello All,
I have the Office 365 plugin, and looking to refine some alerts I have setup. The alert is to notify me of an attempted login from Outside the United States, except a few users with a specific user ID and excluding a specific domain.
Everything works, with the exception of excluding a specific domain. Here is the search that I have setup (I have removed the sensitive information):
`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| where Country !="United States" AND NOT UserId="user1@test.com" AND NOT UserId="user2@test.com" AND NOT UserId="user3@test.com" AND NOT UserId="user1@test2.com" AND NOT UserId="user2@test2.com" AND NOT UserId="user3@test2.com" AND NOT UserId="user4@test2.com" AND NOT UserId="user5@test2.com" AND NOT !UserId="*@test3.com"
| table _time UserId LogonError ClientIP Country
| rename app AS App UserId AS User ExtendedProperties{}.Value AS Reason ClientIP AS "Client IP"
| sort - _time, user
The things that work are highlighted in green. The thing that doesn't work is highlighted in red.
Side note: My goal with the item in red is to exclude the entire domain test3.com for any user. For example, user1@test3.com is under the "User ID" field, so I am just wanting anything at test3.com to be excluded. That is not working for some reason. 😞
Thank you all!
Include the unwanted field in the where command like so.
`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| where Country !="United States" AND NOT UserId="user1@test.com" AND NOT UserId="user2@test.com" AND NOT UserId="user3@test.com" AND NOT UserId="user1@test2.com" AND NOT UserId="user2@test2.com" AND NOT UserId="user3@test2.com" AND NOT UserId="user4@test2.com" AND NOT UserId="user5@test2.com"
| where NOT match(UserId, "@test3.com" OR LogonError="FaultDomainRedirect")
| table _time UserId LogonError ClientIP Country
| rename app AS App UserId AS User ExtendedProperties{}.Value AS Reason ClientIP AS "Client IP"
| sort - _time, user
If your problem is resolved, then please click the "Accept as Solution" button to help future readers.
I also tried the following and no dice:
`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| where Country !="United States" AND NOT UserId="user1@test.com" AND NOT UserId="user2@test.com" AND NOT UserId="user3@test.com" AND NOT UserId="user1@test2.com" AND NOT UserId="user2@test2.com" AND NOT UserId="user3@test2.com" AND NOT UserId="user4@test2.com" AND NOT UserId="user5@test2.com" AND !UserId="*@test3.com"
| table _time UserId LogonError ClientIP Country
| rename app AS App UserId AS User ExtendedProperties{}.Value AS Reason ClientIP AS "Client IP"
| sort - _time, user
That was stray, sorry about that! Query is:
`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| where Country !="United States" AND NOT UserId="user1@test.com" AND NOT UserId="user2@test.com" AND NOT UserId="user3@test.com" AND NOT UserId="user1@test2.com" AND NOT UserId="user2@test2.com" AND NOT UserId="user3@test2.com" AND NOT UserId="user4@test2.com" AND NOT UserId="user5@test2.com" AND NOT UserId="*@test3.com"
| table _time UserId LogonError ClientIP Country
| rename app AS App UserId AS User ExtendedProperties{}.Value AS Reason ClientIP AS "Client IP"
| sort - _time, user
Try this search. It breaks out the unwanted domain into a separate command.
`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| where Country !="United States" AND NOT UserId="user1@test.com" AND NOT UserId="user2@test.com" AND NOT UserId="user3@test.com" AND NOT UserId="user1@test2.com" AND NOT UserId="user2@test2.com" AND NOT UserId="user3@test2.com" AND NOT UserId="user4@test2.com" AND NOT UserId="user5@test2.com"
| where NOT match(UserId, "@test3.com")
| table _time UserId LogonError ClientIP Country
| rename app AS App UserId AS User ExtendedProperties{}.Value AS Reason ClientIP AS "Client IP"
| sort - _time, user
Worked like a charm, you rock, thanks! Can I ask one more question? If I wanted to filter on another field (screenshot here), how would I do that? Say I do not want to include the "FaultDomainRedirect" results?
Include the unwanted field in the where command like so.
`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| where Country !="United States" AND NOT UserId="user1@test.com" AND NOT UserId="user2@test.com" AND NOT UserId="user3@test.com" AND NOT UserId="user1@test2.com" AND NOT UserId="user2@test2.com" AND NOT UserId="user3@test2.com" AND NOT UserId="user4@test2.com" AND NOT UserId="user5@test2.com"
| where NOT match(UserId, "@test3.com" OR LogonError="FaultDomainRedirect")
| table _time UserId LogonError ClientIP Country
| rename app AS App UserId AS User ExtendedProperties{}.Value AS Reason ClientIP AS "Client IP"
| sort - _time, user
If your problem is resolved, then please click the "Accept as Solution" button to help future readers.