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!
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)
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)
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!
Great that it works - please make sure you mark it as solved, so others can benefit from the solution.