Splunk Search

Correlate different id's and average out data

BBakkenes
Explorer

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?

Tags (1)
0 Karma
1 Solution

kristian_kolb
Ultra Champion

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

View solution in original post

0 Karma

kristian_kolb
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

This Week's Community Digest - Splunk Community Happenings [9.26.22]

Get the latest news and updates from the Splunk Community here! Upcoming User Group Events! 👏 Check ...

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...