topic Re: Need help with query and excluding various items in Splunk Search

Office 365 Plugin: How to create alerts for attempted logins that also exclude specific user ID's and domains?

ryanbarnes306 — Thu, 16 Jul 2020 19:21:33 GMT

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!

Re: Need help with query and excluding various items

richgalloway — Thu, 16 Jul 2020 17:25:32 GMT

There seems to be a stray "!" in the red text. Is that in the original query or an error in copying into the question?

Re: Need help with query and excluding various items

ryanbarnes306 — Thu, 16 Jul 2020 17:57:17 GMT

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

Re: Need help with query and excluding various items

ryanbarnes306 — Thu, 16 Jul 2020 18:02:22 GMT

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

Re: Need help with query and excluding various items

richgalloway — Thu, 16 Jul 2020 20:39:06 GMT

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

Re: Need help with query and excluding various items

ryanbarnes306 — Fri, 17 Jul 2020 14:22:46 GMT

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?

Re: Need help with query and excluding various items

richgalloway — Fri, 17 Jul 2020 15:37:21 GMT

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.