Only include certain hours/days for a long term search

[sepkarimpour]

I'm looking to run a search over a 4 week period here I find the count of results per week but I want to look for a specific time range - Sunday 11pm to Friday 11pm.

Having found an answer to a similar question to this, I'm using this search at the moment:

"base search"
| eval hour = tonumber(strftime(_time,"%H"))
| eval dow = tonumber(strftime(_time,"%w"))
| where dow!=6 AND (dow!=0 AND hour<=23) AND (dow!=5 AND hour>=23)
| timechart span=7d count

I can see the number of results is significantly lower when I run the query with these restrictions: 20m+ results with all days included, under 1m results with the WHERE command included. Would this correctly show the range I'm looking for?

[cmerriman]

try this statement instead:

|eval keep=if((date_wday="sunday" AND date_hour>=23) OR (date_wday="friday" AND date_hour<23 ) OR date_wday="monday" OR date_wday="tuesday" OR date_wday="wednesday" OR date_wday="thursday",1,0)
|search keep=1| timechart span=7d count

the date_wday and date_hour fields should be in your data, as they are derived from the event timestamp.
https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/Knowledge/Usedefaultfields

[gcusello · Accepted solution]

Hi sepkarimpour,
instead use eval, you could use Splunk fields date_wday and date_hours with the same logic, but it's more efficient:

your_search NOT ((date_wday=friday date_hour>23) OR date_wday=saturday OR (date_wday=sunday date_hour<24))
| timechart span=7d count

Bye.
Giuseppe

[sepkarimpour]

Ah, this is picking up a lot more results now. I had read before that sometimes wday and hour aren't picked up properly sometimes from the logs. This seems to work much better now with the number much closer to the original now. Thanks.