Splunk Search

How to join two events based on one common value?

Naaba
New Member

Hi,

I have two different events of data :

Event 1 = mail :
id_mail : 1
title_mail : test
mail_srv : host1

Event 2 = server:
id_srv : 3
srv_name : host1
srv_ip : 192.168.0.1

I want to print Event 1 (mail) data with a column containing the server IP like this : id_mail, title_mail, mail_srv, srv_ip

How can I do this?

Thanks

0 Karma

woodcock
Esteemed Legend

Like this:

... | eval srv_name = coalesce(mail_srv,srv_name)
| fields id_mail title_mail id_srv srv_name srv_ip
| stats values(*) AS * BY srv_name
| table id_mail title_mail id_srv srv_name srv_ip

riqbal47010
Path Finder
0 Karma

woodcock
Esteemed Legend

Try this:

Your Search Here To Get Both Types Of Events | eval srv_ip=coalesce(srv_ip, mail_srv) | stats min(_time) AS _time values(*) AS * BY srv_ip

lguinn2
Legend

There is almost certainly a better way to do this, but I think this will work based on the information that you have given

index=A sourcetype=mail
| join type=outer mail_srv [ search index=B sourcetype=server | dedup srv_name | rename srv_name as mail_srv ]
| table id_mail, title_mail, mail_srv, srv_ip
0 Karma

somesoni2
Revered Legend

There need to be a common field between those two type of events. If that common field (in terms of matching values) is mail_srv/srv_name, then try like this

your base search fetching both type of events
| eval host_name=coalesce(mail_srv,srv_name)

Naaba
New Member

Thank you for your answer but It doesn't give the result i want.
I want to be able to use the fields the two events :

Event 1 = mail :
id_mail : 1
title_mail : test
mail_srv : host1

Event 2 = server:
id_srv : 3
srv_name : host1
srv_ip : 192.168.0.1

I want to be able to print a table like this :
id_mail, title_mail, host1,id_srv ,srv_name ,srv_ip

0 Karma

riqbal47010
Path Finder

whats about xyseries command. which convert coloms to rows.

https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Xyseries

0 Karma

somesoni2
Revered Legend

Didn't realize the query was incompletely posted. Here is the full query

your base search fetching both type of events
| eval host_name=coalesce(mail_srv,srv_name)
| stats values(id_mail) as id_mail, values(title_mail) as title_mail ,values(id_srv) as id_srv, values(srv_ip) as srv_ip by host_name 

horsefez
Motivator

the usage of "coalesce" is brilliant... I would've suggested "join"

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...