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