So I am trying to write a query for a ticketing system. This ticketing system has a unique ID for each ticket but the state of each ticket can change, example state=Assigned -> state=Resolved -> state=Closed. Now what I would like to do is search unique tickets based on their state but exclude old ticket events that have an updated state. Example if a ticket ID=1 has gone from Assigned -> Resolved and I am searching for unique tickets with a state of Assigned, I don't want to include the old event which has the ticket ID=1 in the Assigned state.
My first thought was to use a subsearch to exclude ID's in the next state step but as there are quite a few states I thought that that might get complex and clunky.
Thank you in advance for your help.
Could you use the latest(x) function? This will return the chronologically latest seen occurrence of a value of a field X
Try something like this. The dedup command will take the latest records for each ID and then State filter will be applied to check if the latest State was assigned.
your base search without State filter | dedup ID | where State="Assigned"
Assuming that you have fields called
ticketId then how about trying if below works out for you:
your query to return events | stats latest(state) as CurrentState by ticketId | search CurrentState="Assigned"
I'm assuming that you have other fields besides the ticket's state that you want to see, and that you want to see only the most recent event for each ticket. I'm also assuming that you are running a historical search rather than a real-time search.
If so, then the solution is this simple -
your initial search terms | dedup ticketId
The standard functionality of dedup will keep the most recent event. On the other hand, if it is possible that there are events that should not be included in your search --marked as invalid, canceled or never completely filled out, as an example -- you will want to add a search and/or sort step(s) to make sure that the most recent VALID event is the one that gets returned.
your initial search terms | search tickettype!="Inquiry" | sort 0 ticketId validFlag -_time | dedup ticketId
The above would ensure that events with tickettype "Inquiry" were ignored completely, and that events with validFlag of 1 were only displayed if there were no events for that ticketId with a validflag of 0.
edited to use sort 0 rather than sort, in case more than 100 records were returned.