I have an xml sourcetype, with multiple events correlated with a corrID field. For one class of events, I have a "begin" event <beginTrans> and an "end" event <endTrans>, each with a "timestamp" field <timestamp>. The timestamp field is also used as the time of the event when indexed. See examples below...
<TdrType><eventType>beginTrans</eventType> <timestamp>1487803204730</timestamp> <corrID>1487803204657_281de495-1a0c-48d4-b354-23f56d2a71ad</corrID> </TdrType>
<TdrType><eventType>endTrans</eventType> <timestamp>1487803204736</timestamp> <corrID>1487803204657_281de495-1a0c-48d4-b354-23f56d2a71ad</corrID> </TdrType>
I am trying to do a query that does a time diff between the correlated events based on the timestamp. Here is my query.
sourcetype=xmldata | xmlkv | search beginTrans [search sourcetype=xmldata endTrans | xmlkv | rename timestamp AS endTimestamp | table corrID] | rename timestamp AS beginTimestamp | eval cmeTime = endTimestamp - beginTimestamp | table corrID, beginTimestamp, endTimestamp, cmeTime
I have tried variations on this that use appendcols, eval, and fields but haven't found a combination that works. In all cases, endTimestamp is always blank and so cmeTime is also blank. If I run the subsearch by itself, then endTimestamp has a value. Any guidance?
Give this a try
sourcetype=xmldata | xmlkv | search eventType="beginTrans" OR eventType="endTrans"
| stats values(timestamp) as timestamp by corrID
| eval beginTimestamp=mvindex(timestamp,0) | eval endTimestamp=mvindex(timestamp,1)
| eval cmeTime = endTimestamp - beginTimestamp
| table corrID, beginTimestamp, endTimestamp, cmeTime
Optionally, add this to the end of above
| convert ctime(*Timestamp) timeformat="%F %T"