So I have events that are tickets that have a State eg. "New" , "In Progress" , "Completed" etc and a short_description field with its value being a descriptor, as well as a unique number to differentiate between the tickets such as NO232 .
What Im trying to do is iterate through all the tickets and find out when short description changes from one value to another and in which state it was in.
I currently use streamstats which I believed worked well until I realised that it wasn't doing what I thought it was doing.
| base search getting the tickets I want to focus on ...
| streamstats current=true last(short_description) AS old_description, first(short_description) AS new_description BY number
| search old_description!="*#*" AND new_description ="*#*"))
| sorting and adding results to table...
What I expected this to do was search the last event of the ticket, which was when its state was New, and see if its description did not include a # , and then check to see if the tickets description was updated anywhere in the lifespan to include a #.
What I believe it is doing is just comparing the latest state and the previous state, for example comparing Closed and In Progress instead of New -> Closed.
Any help is appreciated.
Given your goal, I think you will get what you're looking for if you replace this:
| streamstats current=true last(short_description) AS old_description, first(short_description) AS new_description BY number
with this:
| eventstats earliest(short_description) AS old_description latest(short_description) AS new_description BY number
Given your goal, I think you will get what you're looking for if you replace this:
| streamstats current=true last(short_description) AS old_description, first(short_description) AS new_description BY number
with this:
| eventstats earliest(short_description) AS old_description latest(short_description) AS new_description BY number
Unfortunately I still get similar results. It is not comparing the tickets first event with the latest event.
Ok, let's back up a little bit. I assume it's always true that a ticket starts in a state with short_description="New"
, moves next into short_description="In Progress"
, and concludes with short_description="Complete"
. If that's true, then this code snippet:
| eventstats earliest(short_description) AS old_description latest(short_description) AS new_description BY number
should result in each ticket getting a new field called old_description="New"
, because that would always be the earliest state that a ticket was in. Tickets that past the state of being new would either wind up with new_description="In Progress"
(if the ticket never got completed) or new_description="Complete"
(if the ticket did get completed).
So how about identifying two tickets in your records - one that did get completed, and one that never reached completion, and running a search like this:
whatever search finds all events involving those two tickets
| eventstats earliest(short_description) AS old_description latest(short_description) AS new_description BY number
Do you wind up with the expected values for old_description
and new_description
?
Sorry! Think I might of explained it poorly. The "New" and "In Progress" are in a field named ticket_state while the short_description field holds values that describe the ticket, such as "Linux VM keeps crashing" for example.
Disregarding that, I still dont get the values I expect. I picked a ticket at random to try and trouble shoot. It had 3 states, New , In Progress and Complete. I wanted to test to see if it worked by using
| eventstats earliest(ticket_state) as old_state latest(ticket_state) as new_state BY number
Obviously the earliest should be New and the latest should be Complete, but im getting both being set to Complete.
Well, that's quite odd. Any chance you can post a screencap (with any sensitive data covered up) or copy/paste the events and full query (again, redacted as needed) so I can see if there's anything I can spot?
@elliotproebstel Hi Elliot, sorry for the late reply, I got your suggestion to work (problem on my end).
However, Im still not getting what Im expecting. I handpicked a few tickets to check the results.
These tickets had the old_description==new_description , however, If I investigate it the earliest state i.e when the the ticket was "New" the description is not equal to the new description.
For example : https://prnt.sc/iq1v78 This is a picture of the results which state that the earliest description matches the latest description. However, if I dig down I find the following https://prnt.sc/iq1v28 Which shows that the earliest description doesnt match at all.
I want to help, but the screencaps are so limited in what they're displaying that I don't have much to work with here. Can you include the queries and the field headers? Or some sanitized version of the same? I just don't know what I'm seeing in those screencaps, sorry.
Hey Moreilly97,
I am not sure if this will work but you can try this:
| stats values(state) AS state values(short_description) AS description by number,_time | eval status=case(description="#" AND state="NEW","updated",description!="#" AND state="NEW","NotUpdated") | stats values(status) AS status dc(status) AS count by number | search count>1
This should give you the numbers that were updated.
Let me know if this helps!!