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
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
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
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)
can you share sample data.
| 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.
Thanks there was a problem with my timestamp format. Much appreciated.