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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...