topic Re: Search for event X and Y, but only Y during business hours? in Splunk Search

Search for event X and Y, but only Y during business hours?

johnpof — Mon, 28 Sep 2020 13:21:52 GMT

Hey Guys, This is my current search (It looks for SQL I/O delays) =

sourcetype="WinEventLog:Application" MSSQLSERVER "requests taking longer than"

But there are few DEV servers where we don't want to hear about I/O delays after hours.. So I figured out how to limit the time search. This returns 9-5pm:

sourcetype="WinEventLog:Application" MSSQLSERVER earliest=-1d@d latest=@d | addinfo | where _time > (info_max_time-54000) AND _time < (info_max_time-25200)

But how do I make it search all servers all the time except DEV servers outside of business hours? been racking my brain all day.

Thx

Re: Search for event X and Y, but only Y during business hours?

sideview — Mon, 28 Sep 2020 13:21:54 GMT

if your date has date_hour extracted (which it generally will), then you can use those as searchterms.

I'm not sure what your searchterms are for production vs dev so I'm going to invent two searchterms - "PRODUCTION" and "DEV" to be my placeholders.

sourcetype="WinEventLog:Application" MSSQLSERVER ( PRODUCTION OR ( DEV date_hour>08 date_hour<17 ) )

Note that if by chance the time values splunk is getting its timestamps from, are themselves in epochtime format, (ie if the times in your events are the number of seconds since 1/1/1970), then there's a weird bug in Splunk where it will create date_hour fields for all those events, however the values it computes for the date_hour fields will be the hours as they would be calculated if your server were in the GMT timezone. This means that unless GMT also happens to be the timezone your server is in, all of the date_hour values will all be off by a constant. If this is the case you can of course use the eval command and it's strftime function to make yourself reliable hour_of_day fields. The drawback is they wont be search-time fields and you'll have to get all of the DEV events off disk and filter them in a subsequent search or where clause, and this wont be as fast as using date_hour in the initial search clause.

UPDATE:

Sorry I didn't know that WinEventLog:Application didn't have date_* fields by default. So if you have to make yourself fields with eval and strftime, here's how it would work. The _time field exists on all events, and it's epochtime-valued, meaning it's the number of seconds elapsed since 1/1/1970. This is easy to confused because whenever an epochtime-valued field called "_time" appears in the Splunk UI, it will actually appear as a readable text timestamp. However remember for the purposes of eval and stats and everything else, it's just a big number of seconds.

Thus.

search terms | eval hour_of_day=strftime(_time,"%H") | eval day_of_week=strftime(_time,"%u")

(you could use another strftime constant to get "Monday" vs "Sunday" but %u will give you integers from 0 to 7 and I find it easier to work with.

So here's your search to match all Production events, and Dev events but only on weekdays during business hours.

search terms | eval hour_of_day=strftime(_time,"%H") | eval day_of_week=strftime(_time,"%u") | search ( PRODUCTION OR ( DEV hour_of_day>9 hour_of_day<18 day_of_week>0 day_of_week<6) )

Re: Search for event X and Y, but only Y during business hours?

johnpof — Mon, 28 Sep 2020 13:21:57 GMT

the traditional date hour type search won't work in WinEventLog, hense my info_max_time search. I'm a week into splunk so quite green.
Can you elaborate on how would I use eval / strftime to make hour_of_day fields? If I understand correctly, what I am doing for the time works but it's offset. What do you mean by date_hour extracted?
For most of my queries I use: ( date_wday=monday OR date_wday=tuesday OR date_wday=wednesday OR date_wday=thursday OR date_wday=friday ) AND ( date_hour > 9 AND date_hour < 18 )
But that doesn't work for WinEvents
I appreciate the response.

Re: Search for event X and Y, but only Y during business hours?

sideview — Thu, 21 Feb 2013 01:31:55 GMT

OK. Gotcha. I'll update my answer above.

Re: Search for event X and Y, but only Y during business hours?

johnpof — Mon, 28 Sep 2020 13:22:07 GMT

This makes sense, but it's not working and I know i'm close. Production is basically database or in this case computers named DB and the ones i'd like to keep quiet are DEV servers. This returns no results:

sourcetype="WinEventLog:Application" MSSQLSERVER "requests taking longer than" eval hour_of_day=strftime(_time,"%H") | eval day_of_week=strftime(_time,"%u") | search ( ComputerName="DB*" OR ( ComputerName="dev*" hour_of_day>9 hour_of_day<18 day_of_week>0 day_of_week<6) )

Re: Search for event X and Y, but only Y during business hours?

sideview — Thu, 21 Feb 2013 18:20:23 GMT

eval is a different search command, so you need a pipe before it. sourcetype="WinEventLog:Application" MSSQLSERVER "requests taking longer than" | eval hour_of_day=strftime(_time,"%H") | eval day_of_week=strftime(_time,"%u") | search ( ComputerName="DB" OR ( ComputerName="dev" hour_of_day>9 hour_of_day<18 day_of_week>0 day_of_week<6) )

Re: Search for event X and Y, but only Y during business hours?

johnpof — Thu, 21 Feb 2013 18:56:36 GMT

Brilliant this works! thanks a lot

Re: Search for event X and Y, but only Y during business hours?

sideview — Thu, 21 Feb 2013 19:10:27 GMT

Stats and eval. Elegant weapons for a more civilized age. Have fun. You're just at the beginning. 😃