How to compare two difference sources and return events that have the same hostname in both sources?

[chaseto]

Hello Experts,

I have 2 different sources

source 1 has hostname, ip address
source 2 has hostname, os, os version

I want to do a comparison on both the sources and get the events which have hostname common in both the sources. How this can be done?...Thanks in advance.

[somesoni2]

If you just want common host name, try this

(index=foo source=source1) OR (index=bar source=source1) | fields source hostname | stats values(source) as source by hostname | where mvcount(source)=2

OR

(your base search for source1) | stats count by source hostname | append [ search (your base search for source2) |stats count by source hostname] | stats values(source) as source by hostname | where mvcount(source)=2

If you want to merge both the sources to show fields for common hostnames, try this

(index=foo source=source1) OR (index=bar source=source1) | fields source hostname ip_address os os_version | stats values(*) as source by hostname | where mvcount(source)=2

[chaseto]

source1

hostname ip_address

abcq123 123.1.5.22

fwef123 109.1.1.101

source 2
hostname os os_version
abcq123 windows 10
jnknkn122 ubuntu 5

result:

hostname ip_address os os_version

abcq123 123.1.5.22 windows 10

fwef123 109.1.1.101

jnknkn122 ubuntu 5

[somesoni2]

Ok.. What are your queries to get data from both the sources?

[chaseto]

ya need the records from both sources

[somesoni2]

No, what is the query that you write to get result from the source?

[chaseto]

index=abc source=source1 |join hostname [index=abc source=source2]|table hostname,ipaddress,os,os_version

[chaseto]

Thanks somesh,will try this but In the below query of yours

(index=foo source=source1) OR (index=bar source=source1) | fields source hostname ip_address os os_version | stats values(*) as source by hostname | where mvcount(source)=2

what should I replace source and source1 in mine and in mvcount(source) ...its kinda confusing source,source1.....my sources are source1 and source2

[bmacias84]

... | join hostname | table hostname ipaddress, osversion