Splunk Search

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

techols
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

somesoni2
Revered Legend

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
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...