Splunk Search

Scheduled Query - change query content

mjd555
Path Finder

Background
I have created a query that will allow me to view all tickets created within one month. As some of the 'resolved' events occur after the month has ended I cannot use | stats count by date_month.

Query

The following query will allow me to view all tickets created in the month of September:

index="cyber" sourcetype=response queue = "Incident" status ="resolved" Dates_Created >= 2015-09-01 00:00:00 AND Dates_Created < 2015-10-01 00:00:00 | dedup ticket |stats count AS Sept

Problem

I am going to use this above query as a scheduled query for each month - however I wish for the Dates_Created to change on a monthly basis i.e I wish 2015-09-01 to change to 2015-10-01 and 2015-10-01 to change to 2015-11-01 and I'm not sure how to do this, any help will be greatly appreciated!

0 Karma
1 Solution

DennisMohn
Path Finder

You can use the now() time and eval to create the timestamps for your query - if you know when the search runs.

If you are running the query in the following month you can use the following search command:

  index="cyber" sourcetype=response queue = "Incident" status ="resolved"
 | eval startstamp=strftime(relative_time(now(),"-mon@mon"),"%Y-%m-%d %H:%M:%S"), endstamp=strftime(relative_time(now(),"@mon"),"%Y-%m-%d %H:%M:%S")  
 | where Dates_Created >= startstamp AND Dates_Created < endstamp 
 | dedup ticket 
 | stats count AS Sept`

View solution in original post

DennisMohn
Path Finder

You can use the now() time and eval to create the timestamps for your query - if you know when the search runs.

If you are running the query in the following month you can use the following search command:

  index="cyber" sourcetype=response queue = "Incident" status ="resolved"
 | eval startstamp=strftime(relative_time(now(),"-mon@mon"),"%Y-%m-%d %H:%M:%S"), endstamp=strftime(relative_time(now(),"@mon"),"%Y-%m-%d %H:%M:%S")  
 | where Dates_Created >= startstamp AND Dates_Created < endstamp 
 | dedup ticket 
 | stats count AS Sept`
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...