How to search for different events on the same day, with different time frames?

[Southy567]

Hi all!

I have been absolutely stumped by this and hoping you can help me out. I am trying to find users that have 2 different, distinct events that happen on the same day. One event can occur at any time of the day, and the second event occurs between 6-8 am.

The closest I have gotten is:

index=Info source=Trustme (EventCode=X OR EventCode=Y)
| eval hour=tonumber(strftime(_time,"%H"))
| where hour>=8 OR hour<0
| stats values(EventCode) as Event_Codes by User
| search Event_Codes=X Event_Codes=Y

This is clipping out users who have Event Y occur outside of that range, which I would like to avoid. Also, I want to cast this over a large period to test and make sure I'm capturing the right people, then I can hopefully set it up as an alert.

Any help would be greatly appreciated!

[bowesmana · Accepted solution]

Is it true that X occurs between 6-8 and Y outside that or doesn't it matter as long as one is between 6-8 and the other is any time?

Is there a reason why you are testing for hour < 0? 

Is this what you want?

index=Info source=Trustme (EventCode=X OR EventCode=Y)
| eval hour=tonumber(strftime(_time,"%H"))
| stats values(eval(if(hour>=6 AND hour<8, EventCode, null()))) as Event_Codes_In_TimeRange values(EventCode) as Event_Codes_All by User
| where mvcount(Event_Codes_All)=2 AND isnotnull(Event_Codes_In_TimeRange)

The where clause is saying that there must be both events X and Y in search window, but there must also be an event (whatever type) in the 6-8 time window.

Note that if you want to run this on a per day basis, then you would _time handling with bin/stats

index=Info source=Trustme (EventCode=X OR EventCode=Y)
| eval hour=tonumber(strftime(_time,"%H"))
| bin _time span=1d
| stats values(eval(if(hour>=6 AND hour<8, EventCode, null()))) as Event_Codes_In_TimeRange values(EventCode) as Event_Codes_All by _time User
| where mvcount(Event_Codes_All)=2 AND isnotnull(Event_Codes_In_TimeRange)

[Southy567]

That looks to have done the trick!!

thank you so much, I'm still relatively new to Splunk, but I can see where I was going wrong, you are a legend!

[bowesmana]

Great that it works - please make sure you mark it as solved, so others can benefit from the solution.