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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...