Splunk Search

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

ryanbarnes306
Explorer

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!

Labels (3)
Tags (3)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust
There seems to be a stray "!" in the red text. Is that in the original query or an error in copying into the question?
---
If this reply helps you, Karma would be appreciated.
0 Karma

ryanbarnes306
Explorer

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

0 Karma

ryanbarnes306
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

ryanbarnes306
Explorer

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? 

ryanbarnes306_0-1594995759191.png

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...