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!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...