topic Re: How to join two events based on one common value? in Splunk Search

How to join two events based on one common value?

Naaba — Tue, 29 Sep 2020 12:42:30 GMT

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

Re: How to join two events based on one common value?

somesoni2 — Tue, 29 Sep 2020 12:39:41 GMT

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)

Re: How to join two events based on one common value?

Naaba — Tue, 29 Sep 2020 12:42:38 GMT

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

Re: How to join two events based on one common value?

somesoni2 — Thu, 02 Feb 2017 16:56:02 GMT

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

Re: How to join two events based on one common value?

lguinn2 — Thu, 02 Feb 2017 17:20:40 GMT

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

Re: How to join two events based on one common value?

horsefez — Thu, 02 Feb 2017 18:24:43 GMT

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

Re: How to join two events based on one common value?

woodcock — Wed, 01 Mar 2017 21:55:28 GMT

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

Re: How to join two events based on one common value?

riqbal47010 — Thu, 21 Nov 2019 05:57:26 GMT

whats about xyseries command. which convert coloms to rows.

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

Re: How to join two events based on one common value?

riqbal47010 — Thu, 21 Nov 2019 05:57:47 GMT

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

Re: How to join two events based on one common value?

woodcock — Thu, 21 Nov 2019 15:35:37 GMT

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