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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

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 ...