Splunk Search

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

Path Finder

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 > (infomaxtime-54000) AND _time < (infomax_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

Tags (3)
1 Solution

SplunkTrust
SplunkTrust

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 datehour fields for all those events, however the values it computes for the datehour 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 datehour 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 hourofday 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 datehour 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) )

View solution in original post

SplunkTrust
SplunkTrust

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 datehour fields for all those events, however the values it computes for the datehour 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 datehour 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 hourofday 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 datehour 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) )

View solution in original post

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

Brilliant this works! thanks a lot

0 Karma

SplunkTrust
SplunkTrust

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) )

Path Finder

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 hourofday=strftime(time,"%H") | eval dayofweek=strftime(time,"%u") | search ( ComputerName="DB" OR ( ComputerName="dev" hourofday>9 hourofday<18 day_of_week>0 dayofweek<6) )

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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

0 Karma