Splunk Search

How to get total count of events excluding specific time range for certain days?

sahil237888
Path Finder

Can anyone please help?

I want to display the total count of events occurred in a week (but excluding specific day/time i.e. exclude 04-06 PM for Monday and 06-08 PM for Wednesday)

I am running below search, but it doesn't seem to be excluding the counts for specific time because if I am trying to remove the excluding condition, I am still getting the same results.

sourcetype=web_server events IN ("GET", "Delete")
 | eval myHour=strftime(_time, "%H") 
 | eval myMinute=strftime(_time, "%M") 
 | eval day_of_week = strftime(_time,"%A")
  | where NOT ( (day_of_week = "Monday" AND myHour= 4 AND myMinute>=0) OR (day_of_week = "Monday" AND myHour=5) OR (day_of_week = "Monday" AND myHour=6 AND myMinute<=59) OR (day_of_week = "Wednesday" AND myHour= 6 AND myMinute>=0) OR (day_of_week = "Wednesday" AND myHour=7) OR (day_of_week = "Wednesday" AND myHour=8 AND myMinute<=59))
  | stats count as Total_events avg(duration) as Duration_of_events by events
0 Karma

adonio
Ultra Champion

try this and see if it works for you:

index=<YOUR_INDEX_HERE> sourcetype=web_server events IN ("GET", "Delete")
| eval myHour=strftime(_time, "%H") 
| eval myMinute=strftime(_time, "%M") 
| eval day_of_week = strftime(_time,"%A")
| eval i_care_about_it = if((day_of_week="Monday" AND myHour=4 AND myMinute>=0) OR (day_of_week="Monday" AND myHour=5) OR (day_of_week="Monday" AND myHour=6 AND myMinute<=59) OR (day_of_week="Wednesday" AND myHour=6 AND myMinute>=0) OR (day_of_week="Wednesday" AND myHour=7) OR (day_of_week="Wednesday" AND myHour=8 AND myMinute<=59),"false","true")
| stats count by i_care_about_it day_of_week myHour myMinute

hope it helps

0 Karma

jpolvino
Builder

Can you please try this in place of your "where" expression?

| where NOT (date_wday="monday" AND date_hour>=16 AND date_hour<18) AND NOT (date_wday="wednesday" AND date_hour>=18 AND date_hour<20)

And why do you have "by events" at the end?

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...