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!

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...