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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...