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

[aramakrishnan]

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!

[Flynt]

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.