Splunk Search

Correlation 2 sourcetype with common fields different name

pgbr7
Explorer

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 ?

Tags (1)
0 Karma
1 Solution

cvssravan
Path Finder

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

View solution in original post

0 Karma

cvssravan
Path Finder

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

0 Karma

pgbr7
Explorer

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.

0 Karma

pgbr7
Explorer

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

Thanks man.

0 Karma

cvssravan
Path Finder

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.

0 Karma

lakshman239
SplunkTrust
SplunkTrust

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]

0 Karma

FrankVl
Ultra Champion

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.

0 Karma

lakshman239
SplunkTrust
SplunkTrust

Thx FrankVI

0 Karma

pgbr7
Explorer

Don´t work, Thanks.

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