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?
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 append
ing and coalesce
ing you can run the searches in parallel and renaming the id fields before merging the search results. Then you can make transaction
s 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
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 append
ing and coalesce
ing you can run the searches in parallel and renaming the id fields before merging the search results. Then you can make transaction
s 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