How to join two events based on one common value?

[Naaba]

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

[woodcock]

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]

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

[woodcock]

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]

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

[somesoni2]

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]

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

[riqbal47010]

whats about xyseries command. which convert coloms to rows.

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

[somesoni2]

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]

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