Can you help me make a query that would return results from last 30 days while also excluding certain days and time ranges?

[kaniesb]

I currently am pulling in event IDs from Windows events for the purpose of monitoring when servers are being rebooted and for what reasons. However, every Monday at 6AM (local server time), there is a scheduled task that reboots the entire fleet which ranges over every time zone in the U.S, and this throws off the dashboard count panels I have created since all 800+ servers are rebooting.

Example of current search:

source="WinEventLog:System" NOT Message=*Explorer.exe*  EventCode=1074 

So what I would like to do is search through the past 30 days and exclude Mondays from 5 to 7 AM (local server time). I've tried things like "date_wday!=monday" and it seems to break the search telling me to "expand my search range"(and "NOT date=wday="monday"" or any variant doesn't work either).

I've seen people suggest things like:

 | eval weekDay = strftime(_time,"%a")
 | eval HourOfDay = strftime(_time,"%H")

But I don't quite grasp what is happening here or how to use it.

Below is an example of one of the events that I can see and how its formatted. I noticed that the "Time" field is different from the time in the "Event" field. Should I (or could I) use the date/time from the event to do this? I would think that would always be accurate for what I'm getting at here.

[zonistj · Accepted solution]

Hi,

The logic of using the eval statements is that you could then write a search or where excluding the day and time period that you want. The eval statements make it easier for using a NOT statement to filter out a time period.

 source="WinEventLog:System" NOT Message=*Explorer.exe*  EventCode=1074 
  | eval weekDay = strftime(_time,"%a")
  | eval HourOfDay = strftime(_time,"%H")
  | where weekDay!="Mon" AND HourOfDay!="06"

  or 

  |search weekDay!="Mon" AND HourOfDay!="06"

Edit: I could have explained this better. The eval statements are creating new fields that are created from the "week day" and "hour of the day" values from the "_time" field. The strftime function lets you extract specific parts of date time values and pass them to be the value of the newly created field. So, "%a" let's you extract the day of the week and "%H" let's you extract the hour of the day from the "_time" field.

After that, you just create the NOT statement shown above.

[kaniesb]

Hi and thanks for the response!

This seems to be blocking out all events that happen on a Monday period. I tried both |where and |search

any ideas?

[zonistj]

Oh, I just realized something. If we change the AND statement in my first comment to an OR statement then it does exactly what you're asking to do:

   | eval weekDay = strftime(_time,"%a")
   | eval HourOfDay = strftime(_time,"%H")
   | where weekDay!="Mon" OR HourOfDay!="06"

[kaniesb]

Awesome thanks! That gets rid of all Monday at 6. Would you happen to know how I could do the other parts of my question? to be able to get lets say, monday from 6 am to 11 am ?

And I still get the feeling that since Splunks time is different than the one in the event I might want to use the time being reported in the event itself. Is there a way to use that time from the event to cut out mondays from 6am to sometime ?

[zonistj]

Hi,

I spent some time figuring out a way to get it to filter out Mondays without having to update the search every Monday.

| eval date_filter = strftime('_time',"%a") 
| eval todays_date=strftime('_time',"%Y/%m/%d") 
| eval start_time_filter = strptime("06:00:00 AM","%H:%M:%S %p") 
| eval start_time_filter=strftime('start_time_filter',"%H:%M:%S %p") 
| eval start_time='todays_date' + " " + 'start_time_filter' 
| eval start_time=strptime('start_time',"%Y/%m/%d %H:%M:%S %p") 
| eval end_time_filter = strptime("11:00:00 AM","%H:%M:%S %p") 
| eval end_time_filter=strftime('end_time_filter',"%H:%M:%S %p") 
| eval end_time='todays_date' + " " + 'end_time_filter' 
| eval end_time=strptime('end_time',"%Y/%m/%d %H:%M:%S %p") 
| eval fake_start_time=strptime("1990/09/24 06:00:00 AM","%Y/%m/%d %H:%M:%S %p") 
| eval fake_end_time=strptime("1990/09/24 11:00:00 AM","%Y/%m/%d %H:%M:%S %p") 
| eval start_time = if('date_filter'="Mon",'start_time','fake_start_time') 
| eval end_time = if('date_filter'="Mon",'end_time','fake_end_time') 
| where _time > end_time OR _time < start_time 

You could shorten this by putting some of the eval statements together, but I left them like this for ease of reading. This creates logic to check if today's day is Monday and if it is then create a filter for today's date from 6 to 11 am. If it's not Monday then we set the filter to 1990 which will always be greater than the end_time in our where statement.

This uses the event time stamp itself vs. the other method of hard-coding a date. I think this is what you're looking for exactly, but let me know if you need anything else.

[kaniesb]

Wow thank you so much for spending all the time to do this! This is quite a bit more advanced that anything I could write at current but I'm going to save it/use it/experiment with it and hopefully learn from it. If I had more points to give you I absolutely would haha.

If you have the time, the only thing left I need help with (if its even possible) is trying to use the "local" time reported back from within the event itself instead of the time column itself. If this isn't possible or its too much work, I just want to say again how much I appreciate all the iterations of this you've created and shared with me.

[zonistj]

I have two solutions for you. If we stick with the way you were doing it then it could look like this:

| eval weekDay = strftime(_time,"%a") 
| eval HourOfDay = strftime(_time,"%H") 
| where weekDay!="Mon" OR (HourOfDay!="06" AND HourOfDay!="07" AND HourOfDay!="08" AND HourOfDay!="09" AND HourOfDay!="10" AND HourOfDay!="11")

It's not the prettiest but it works.

Another way to do it is by creating an explicit time range and filtering that out with a where clause:

| eval start_time=strptime("2018/09/24 06:00:00 AM","%Y/%m/%d %H:%M:%S %p") 
| eval end_time=strptime("2018/09/24 11:00:00 AM","%Y/%m/%d %H:%M:%S %p")

| where _time > end_time OR _time < start_time

The downside of this method is that you have to update the date to reflect the latest Monday.

[kaniesb]

Ahh okay I'll stick with:
| where weekDay!="Mon" OR (HourOfDay!="06" AND HourOfDay!="07" AND HourOfDay!="08" AND HourOfDay!="09" AND HourOfDay!="10" AND HourOfDay!="11")

I like that the best for this case so I can set it and forget it.

This is all filtering things from the "Time" column, correct? Will Splunk recognize the date/time im pulling in from my events and let me filter based on that instead of the actual "Time" field(Example shown below)? I ask because of time zones, and not wanting to have to stop monitoring for such large chunks of time. It would be great to be able to just cut out from 4AM to 6:30AM and have it deal with timezones properly.

I appreciate all your help!

[zonistj]

Apologies!

It's the way the AND statement is getting processed in the search. One solution is to combine the two fields and filter based on the combined field:

| eval weekDay = strftime(_time,"%a") 
| eval HourOfDay = strftime(_time,"%H") 
| eval date_time_filter = 'weekDay' + "-" + 'HourOfDay'
| where date_time_filter!="Mon-06"

I tested this and it works for me. Let me know if it works for you.