Splunk Search

Comparing previous event field value to another event field value

Moreilly97
Path Finder

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.

0 Karma
1 Solution

elliotproebstel
Champion

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

View solution in original post

0 Karma

elliotproebstel
Champion

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
0 Karma

Moreilly97
Path Finder

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

0 Karma

elliotproebstel
Champion

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?

0 Karma

Moreilly97
Path Finder

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.

0 Karma

elliotproebstel
Champion

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?

0 Karma

Moreilly97
Path Finder

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

0 Karma

elliotproebstel
Champion

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.

0 Karma

deepashri_123
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...