Splunk query to eliminate specific events

[alexspunkshell]

Hi All,

Below is my Splunk query.

I want to only eliminate the result if "UPN" & "Event_title" both are the same for 7 days in my result.

And I want to get the other result as it is.

Please help me with the Splunk query.

Splunk Query

index=myindex "vendorInformation.provider"=myprovider
| eval Event_Date=mvindex('eventDateTime',0)
| eval UPN=mvindex('userStates{}.userPrincipalName',0)
| eval Logon_Location=mvindex('userStates{}.logonLocation',0)
| eval Event_Title=mvindex('title',0)
| eval Event_Severity=mvindex('severity',0)
| eval AAD_Acct=mvindex('userStates{}.aadUserId',0)
| eval LogonIP=mvindex('userStates{}.logonIp',0)
| table Event_Date, Event_Title, Event_Severity UPN LogonIP Logon_Location

@scelikok @soutamo @saravanan90 @thambisetty @ITWhisperer @gcusello @bowesmana   @to4kawa 

[ITWhisperer]

It is not clear what "both are the same for 7 days" means.

Is that any 7 consecutive days in your results or at least (?) 7 of the days in your time range?

Is it that the UPN has only the same Event_title value on all 7 days and no other Event_title value?

Does it matter how many times on any of the days that the Event_title value appears?

[alexspunkshell]

@ITWhisperer Thanks for your reply. 

In my search results, I have multiple UPN & Event_title. And my query is running in real-time in the dashboard.

But I want to eliminate results (in query) if any UPN has the same Event_title for the last 7 days time frame.

[ITWhisperer]

| gentimes start=-7
| streamstats count as row
| eval row=row*20
| makecontinuous row span=1
| reverse
| filldown starttime
| reverse
| eval row=if(row=20,1,row)
| makecontinuous row
| filldown starttime
| rename starttime as _time
| fields _time
| eval severity=mvindex(split("high,medium,low",","),random() % 3)
| eval title=mvindex(split("A,B,C",","),random() % 3)
| eval upn=mvindex(split("111,222,333",","),random() %3)
``` The above sets up some dummy data ```

``` Get date (not time) ```
| eval day=strftime(_time,"%Y-%m-%d")
``` Count distinct dates by upn and title ```
| eventstats dc(day) as event_days count as event_count by upn title
``` Remove events where title has appeared for upn for all 7 days ```
| where event_days < 7

[alexspunkshell]

@ITWhisperer  Still same UPN & Event_Title is not eliminated. Could you please help here.

index=myindex "vendorInformation.provider"=myprovider
| eval Event_Date=mvindex('eventDateTime',0)
| eval UPN=mvindex('userStates{}.userPrincipalName',0)
| eval Logon_Location=mvindex('userStates{}.logonLocation',0)
| eval Event_Title=mvindex('title',0)
| eval Event_Severity=mvindex('severity',0)
| eval AAD_Acct=mvindex('userStates{}.aadUserId',0)
| eval LogonIP=mvindex('userStates{}.logonIp',0)
| eval day=strftime(_time,"%Y-%m-%d")
| eventstats dc(day) as event_days count as event_count by UPN Event_Title
| where event_days < 7
| table Event_Date, Event_Title, Event_Severity UPN LogonIP Logon_Location

[ITWhisperer]

You could try basing your day on the Event_Date field (the date in the data)

index=myindex "vendorInformation.provider"=myprovider
| eval Event_Date=mvindex('eventDateTime',0)
| eval UPN=mvindex('userStates{}.userPrincipalName',0)
| eval Logon_Location=mvindex('userStates{}.logonLocation',0)
| eval Event_Title=mvindex('title',0)
| eval Event_Severity=mvindex('severity',0)
| eval AAD_Acct=mvindex('userStates{}.aadUserId',0)
| eval LogonIP=mvindex('userStates{}.logonIp',0)
| eval day=substr(Event_Date,1,10)
| eventstats dc(day) as event_days count as event_count by UPN Event_Title
| where event_days < 7
| table Event_Date, Event_Title, Event_Severity UPN LogonIP Logon_Location

Having said that, you didn't answer my earlier questions:

It is not clear what "both are the same for 7 days" means.

Is that any 7 consecutive days in your results or at least (?) 7 of the days in your time range?

Is it that the UPN has only the same Event_title value on all 7 days and no other Event_title value?

Does it matter how many times on any of the days that the Event_title value appears?

[alexspunkshell]

@ITWhisperer  Thanks for your prompt reply.

Question - Is that any 7 consecutive days in your results or at least (?) 7 of the days in your time range?

Answer - 7 consecutive days in my results.

Question - Is it that the UPN has only the same Event_title value on all 7 days and no other Event_title value?

Answer - There are multiple values in UPN & multiple Event_titles fileds.

Question - Does it matter how many times on any of the days that the Event_title value appears?

Answer - It doesn't matter if the same Event_title appears.  It doesn't matter if the same UPN appears.

If the UPN matches the Event_title up to 7 consecutive days, then I want to eliminate in the result.

Could you please help here?

[ITWhisperer]

Here is a run-anywhere example which hopefully demonstrates what you are trying to achieve

| gentimes start=-10 increment=10m
| rename starttime as _time 
| fields _time 
| eval UPN=mvindex(split("ABCDEF",""),random() % 6)
| eval Event_Title=mvindex(split("ABCDE",""),random() % 5).mvindex(split("ABCDE",""),random() % 5)


| eval day=relative_time(_time,"@d")
| fieldformat day=strftime(day,"%Y-%m-%d")
| sort UPN Event_Title day
| streamstats window=1 current=false values(Event_Title) as previous_Title values(UPN) as previous_UPN values(day) as previous_day
| eval startofsequence=if(isnull(previous_Title),1,if(previous_Title != Event_Title OR previous_UPN != UPN, 1, if(day-previous_day>24*60*60,1,null)))
| streamstats sum(startofsequence) as sequenceid by Event_Title UPN
| eventstats dc(day) as distinct_days by Event_Title UPN sequenceid
| where distinct_days < 7

The main process is after the blank lines. The lines before the blank lines generate a random data set for demonstration purposes. The process is as follows:

• Ensure your days are held as epoch times
• Sort by UPN, Event_Title and day
• Find the values for the previous event
• Find the start of a sequence of consecutive days either because it is the first event for the UPN and Event_Title or because it is more than 1 day after the previous event for the UPN and Event_Title
• Generate a sequence id for each sequence (I have done it by UPN and Event_Title but this isn't strictly necessary as the previous line already ensures that a new sequence starts if the UPN or Event_Title changes)
• Count the distinct days in each sequence (Again, I have done it by UPN and Event_Title as well, but if you remove the UPN and Event_Title from the previous line, you can remove it here also)
• Finally, remove all events which are part of sequences with more than 6 consecutive days.

Hopefully, that makes sense enough for you to apply it to your search.