Splunk Search

How do I report on all changes in the value of a field over time?

aramakrishnan
New Member

I'm looking to report on all changes in a field value, and I know of a way to report just the first and last field change, but I want to report on all fields for a given serialNumber, so is there something I could use to spill out the phase change in between the first and last one? I'm looking to build a table like this:

serialNum ID phase1 phase2 phase3 
NTEST123  1   pre   except result
NTEST123  2   pre   except result
NTEST232  4   pre     -    result

While all IDs go through "pre" and "result", "except" only occurs on some. So that field change does not occur for alll segment IDs, and I don't want that factor to mess up my table (although I still want to report on "except" changes)

Is there a way I can produce a table for this? I tried something like:

...|stats first(phase) as phase1 last(phase) as phase3 

But in the above searcg, I don't know how to also determine if the phase "except" occurred. The bottom line is that I want to know if all the "except" cases finally changed to results.

Thanks for any help!

Tags (4)
0 Karma

Flynt
Splunk Employee
Splunk Employee

A quick hack for this (and yes it's totally a hack, someone else may have a better idea of search gymnastics here)

  |eval phasetype=case(phase="pre","phase1",phase="except" OR phase="-","phase2",phase="result","phase3")|eval serialNum=serialNum+"-"+ID|chart values(phase) by serialNum phasetype|rex field=serialNum "(?<serialNum>.*)-(?<ID>.*)"

This is provided your events are just 3 fields (serialNum, ID, and phase) and that there are only 3 phases with the exact same values. You may need to adjust for more values if you have more than 3 phases or more than one value for a phase. For instance, notice that for phase2 you can have "except" or "-".

The test search I used is as follows

|stats count|fields - count|eval serialNum="NTEST123"|eval ID="1"|eval  phase="pre"  |append [|stats count|fields - count|eval serialNum="NTEST123"|eval ID="1"|eval  phase="except"  ]|append [|stats count|fields - count|eval serialNum="NTEST123"|eval ID="1"|eval  phase="result"  ]

|append [ |stats count|fields - count|eval serialNum="NTEST123"|eval ID="2"|eval  phase="pre" ]|append [|stats count|fields - count|eval serialNum="NTEST123"|eval ID="2"|eval  phase="except"  ] |append [|stats count|fields - count|eval serialNum="NTEST123"|eval ID="2"|eval  phase="result" ]

|append [ |stats count|fields - count|eval serialNum="NTEST232"|eval ID="4"|eval  phase="pre" ]|append  [ |stats count|fields - count|eval serialNum="NTEST232"|eval ID="4"|eval  phase="-"   ] |append [ |stats count|fields - count|eval serialNum="NTEST232"|eval ID="4"|eval  phase="result" ]

|eval phasetype=case(phase="pre","phase1",phase="except" OR phase="-","phase2",phase="result","phase3")|eval serialNum=serialNum+"-"+ID|chart values(phase) by serialNum phasetype|rex field=serialNum "(?<serialNum>.*)-(?<ID>.*)"

As I don't have your source data, basically I spoof the values of the 3 fields. What I'm doing is adding a new field that tells us what phase we're in based on the values of the "phase" field. We concatenate the serialNum and ID because we can't chart over 3 fields. Once we've charted (and split our phasetype field into columns) we then extract the serialNum and ID into their own respective fields.

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