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

[NicoloPunzalan2]

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

[anjambha · Accepted solution]

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

[NicoloPunzalan2]

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)

[anjambha]

can you share sample data.

[anjambha]

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

[NicoloPunzalan2]

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