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!

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...