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!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...