Diff based on field data with count and drilldown to specific events

[rangarbus]

Hey 👋 ,

I have a usecase where i need to take a difference between events and report the ones which are missing. 

Example Events (each line is an event). 

txId will be identifier field which will be sent with type "R", "D" or "F"

Here in this case, i need to identify the txId for which i have "R" but missing "D" or "F"

{ "txId" : "0001", "type": "R"}

{ "txId" : "0001", "type": "F"}

{ "txId" : "0002", "type": "R"}

{ "txId" : "0003", "type": "R"}

{ "txId" : "0003", "type": "D"}

In the above sample events, txId = 0002 is the one missing D or F type.

I need to count such events & show in a panel as well as drill-down to those specific R events.

Additional challenge is there will be more than 3 million "R" events and corresponding "D" or "F" events.

Can you please help on this diff ?

[ITWhisperer]

| makeresults 
| eval _raw="{ \"txId\" : \"0001\", \"type\": \"R\"}|{ \"txId\" : \"0001\", \"type\": \"F\"}|{ \"txId\" : \"0002\", \"type\": \"R\"}|{ \"txId\" : \"0003\", \"type\": \"R\"}|{ \"txId\" : \"0003\", \"type\": \"D\"}"
| eval events=split(_raw,"|")
| mvexpand events
| table events


| spath input=events
| stats dc(type) as count by txId
| where count = 1

In your drilldown, pass the value of txId to the search in the drilldown panel