Correlation 2 sourcetype with common fields different name

[pgbr7]

Hello guys,

I have 2 sourcetype, the sourcetype A have the fields [ IP , hostname , source_mac ] , the sourcetype B have the fields [ Username , mac_addres ]
I need a correlation the sourcetype A source_mac with sourcetype B mac_addres, because it's the same MAC.
Return table with fields [ Username , mac_addres, IP ,hostname ]

I'm trying this:

index=main (sourcetype=A)
| fields IP , hostname , source_mac
| dedup IP , hostname , source_mac
| append
[ search sourcetype="B"
| dedup mac_addres
| fields mac_addres, Username
| eval Match=coalesce(source_mac, mac_addres)
| table Match,IP , hostname , Username

But don't work, return the sourcetype=A and sourcetype=B.

Any suggestion ?

[cvssravan · Accepted solution]

try this if you are ok with using join

index=main (sourcetype=A)
| fields IP , hostname , source_mac
| dedup IP , hostname , source_mac
| join source_mac
[ search sourcetype="B"
| dedup mac_addres
| rename mac_addess as source_mac
| fields source_mac, Username]
| table Match,IP , hostname , Username

[pgbr7]

In this case, In sourcetype"B" I have mac_addres, but in sourcetype="A" I don´t have . So I need
compare fields ( mac_addres and source_mac , If the Source_mac have the same mac_addres, i return the fields Sourcetype A ( IP , hostname ) and sourcetype B ( Username ) in the same table.

index=main (sourcetype=A)
| fields IP , hostname , source_mac
| dedup IP , hostname , source_mac
| join source_mac
[ search sourcetype="B"
| dedup mac_addres
| rename mac_addess as source_mac
| fields source_mac, Username]
| table Match,IP , hostname , Username

In this case:
index=main (sourcetype=A OR sourcetype=B)
| fields IP , hostname , source_mac , mac_address, Username
| search (mac_address == source_mac)
|table IP, hostname, source_mac, Username

Don´t work.

Thanks guys.

[pgbr7]

Sorry for the mistake. I test again and work. I forget the rename the field.

Thanks man.

[cvssravan]

As you don't have source_mac in both source types, we are renaming mac_address in source type B to source_mac to facilitate join with source type A. Not sure why it didn't work.

[lakshman239]

You would need to use join as mentioned by another splunker.

|makresults |eval sourcetype="A", IP="1.2.3.4", src_mac="abcd", host="host1"
|join src_mac [|makeresults | eval sourcetype="B", user="usr1", mac_address="abcd" | rename mac_address AS src_mac]

[FrankVl]

The entries for each sourcetype would come in their own rows in the results, so doing search (mac_address == source_mac) will never work. Also search cannot be used to compare fields, you need to use where for that.

[lakshman239]

Thx FrankVI

[pgbr7]

Don´t work, Thanks.