Scheduled Query - change query content

[mjd555]

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!

[DennisMohn · Accepted solution]

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`