Comparing previous event field value to another event field value

[Moreilly97]

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.

[elliotproebstel · Accepted solution]

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

[Moreilly97]

Unfortunately I still get similar results. It is not comparing the tickets first event with the latest event.

[elliotproebstel]

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?

[Moreilly97]

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.

[elliotproebstel]

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?

[Moreilly97]

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

[elliotproebstel]

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.

[deepashri_123]

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