Archive
Highlighted

How to return field from a subsearch to main search for subsequent calculations

New Member

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?

Tags (1)
0 Karma
Highlighted

Re: How to return field from a subsearch to main search for subsequent calculations

SplunkTrust
SplunkTrust

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"
0 Karma