Correlate different id's and average out data

[BBakkenes]

Hello Splunky's,

I'am working on a project and want to correlate a couple of id's on different logs and got the time it has taken from earlest event to latest event.

This is the input:

SMTP

2013-12-04 09:00:00 Verwerking SMTP id=1203
2013-12-04 09:00:20 Verwerking SMTP id=1205
2013-12-04 09:00:21 Verwerking SMTP id=1503

CORE

2013-12-04 09:00:01 Verwerking CORE IN=1203, OUT=adf
2013-12-04 09:00:21 Verwerking SMTP IN=1205, OUT=sda
2013-12-04 09:00:25 Verwerking SMTP IN=1503, OUT=yuis

POP3

2013-12-04 10:00:50 Verwerking POP3 id=adf
2013-12-04 09:00:26 Verwerking POP3 id=sda

This is the search query I fire at this moment.

host=SMTP1 OR host=CORE1 | eval sameID=coalesce(SMTPID,IN) | stats latest(_time) as lt earliest(_time) as et by sameID | eval durationIN = lt - et | appendcols [SEARCH host=CORE1 OR host=POP3 | eval sameOUT=coalesce(pop3ID,OUT) | stats latest(_time) as lt2 earliest(_time) as et2 by sameOUT | eval durationOUT = lt2 - et2] | eval durationTotal=durationOUT+durationIN | table durationTotal durationIN sameID durationOUT sameOUT

Problem with this is that when POP3 hasn't a id witch CORE does have there's a 0 reported witch actualy should not show 0 but NULL. In one short centence: I want to have the avarage lead time from the first step to the latest.

Other problem is that it does not give one number, I want to have an average number for al the steps.

Who can help me with this problem?

[kristian_kolb · Accepted solution]

This is one way to do it;

| multisearch [search sourcetype=smtp | rename id as IN] [search sourcetype=pop3 | rename id as OUT] [search sourcetype=core] | transaction IN,OUT 

Instead of appending and coalesceing you can run the searches in parallel and renaming the id fields before merging the search results. Then you can make transactions based on the transitive id's.

Since transaction automatically creates the duration field, you can now do tuff like;

... | table duration, eventcount, IN, OUT

or

... | stats avg(duration)

Read more here;

http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Multisearch
http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Transaction

Hope this helps,

K