Splunk Search

How to get data that is only for a relative time range.

NicoloPunzalan2
Engager

Hi All,

Im trying to get the ticket inflow for the last three months. My data is connected to service now and the date that I use is on the field opened_at. I keep on getting tickets that are opened at more than three months, that's my problem because every time there is an update on some aging tickets it creates a new event that will be later be fetched on my search.
I have also set my time range to be relative to past 3 months. My question is that how can I only get data that has a date of opened_at for the previous 3 months relatively on the time now. My query is below for more details. Please I have been stuck on this. Thank you.

index=aiam_itsm_ticket_kerry
|dedup ticket_number sortby +_time
|where (u_category!="Change Request" AND u_category!="Z SAP CR" AND u_category!="Z SAP Non Production" AND u_category!="NON PRODUCTION (SAP Staff Only)") AND caller_id!="SN Event Creator (snevent_creator)" AND assignment_group="SAP/MDM/MDM - AM/"
| eval created_at=strptime(opened_at,"%d/%m/%Y %H:%M:%S")
| eval Month=strftime(created_at,"%b")
| stats count(ticket_number) by Month

All the best,
Nicolo

Tags (3)
0 Karma
1 Solution

anjambha
Communicator

Hi NicoloPunzalan24,

Try this run anywhere search

| makeresults | eval created_at="2017-10-06 12:31:30, 2018-03-06 11:31:30" | makemv delim="," created_at | mvexpand created_at | eval epoch = strptime(created_at,"%Y-%m-%d %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400

In your environment :

<Your Search>  | eval epoch = strptime(created_at,"%Y-%m-%d %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400

View solution in original post

0 Karma

anjambha
Communicator

Hi NicoloPunzalan24,

Try this run anywhere search

| makeresults | eval created_at="2017-10-06 12:31:30, 2018-03-06 11:31:30" | makemv delim="," created_at | mvexpand created_at | eval epoch = strptime(created_at,"%Y-%m-%d %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400

In your environment :

<Your Search>  | eval epoch = strptime(created_at,"%Y-%m-%d %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400
0 Karma

NicoloPunzalan2
Engager

Hi anjambha,

I tried to add it to my query but it returned zero results. Please see my query below.

index=aiam_itsm_ticket_kerry
|dedup ticket_number sortby -_time
|where (u_category!="Change Request" AND u_category!="Z SAP CR" AND u_category!="Z SAP Non Production" AND u_category!="NON PRODUCTION (SAP Staff Only)") AND caller_id!="SN Event Creator (snevent_creator)" AND assignment_group="SAP/MDM/MDM - AM/"
| eval epoch = strptime(opened_at,"%d-%m-%Y %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400
| stats count(ticket_number)

0 Karma

anjambha
Communicator

can you share sample data.

0 Karma

anjambha
Communicator
| makeresults | eval created_at="2017-10-06 12:31:30",Ticket_no="101" | append [| makeresults | eval created_at="2018-03-06 11:31:30",Ticket_no="102"] | eval epoch = strptime(created_at,"%Y-%m-%d %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400 | stats count(Ticket_no) as Ticket_count

run above anywhere search you will get some idea. Or you can test your search by changing < or > operator of where clause also make sure time-format in the strptime() is proper.

0 Karma

NicoloPunzalan2
Engager

Thanks there was a problem with my timestamp format. Much appreciated.

0 Karma
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 ...