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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...