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!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...