Splunk Search

How to create one table by combining a common field with different field names from two sources?

clairebesson
Explorer

Hi everyone,

I am trying to combine two sources with a common field.
The first source has the field LAN MAC Address and the second has the field MAC Address. These two fields are equals.
I want to combine these two sources using this common field and create one table that regroup fields from source1 and fields from source2.

Source1=awback.csv, fields = Status, Group, LAN MAC Address
Source2= actback.csv, fields= Serial, PO Number, MAC Address

Table desired: Status, Group, Serial, PO Number, MAC Address

I tried two different methods but it didn't work:
First method: using join

|inputlookup "awback.csv" | join "LAN MAC Address" [|inputlookup actback.csv | eval "MAC Address"=split("MAC Address",":") | mvexpand "MAC Address" | rename "MAC Address" as "LAN MAC Address"]

Second method using transaction:

source="awback.csv" OR source="actback.csv" | rename "MAC Address" as "LAN MAC Address" | transaction "LAN MAC Address"

Could you please help with with that?
Thanks a lot!

0 Karma

carmackd
Communicator

If I understand correctly, this should work.

| inputlookup awback.csv | rename "LAN MAC Address" AS Mac | append [|inputlookup actback.csv | rename "MAC Adress" AS Mac ] | stats values(*) AS ** by Mac

Supposed to be a single astrik

0 Karma

woodcock
Esteemed Legend

Like this:

| inputlookup awback.csv | eval source="awback.csv"
| appendpipe [|inputlookup actback.csv | eval "MAC Address"=split("MAC Address",":") | mvexpand "MAC Address" | rename "MAC Address" as "LAN MAC Address" | eval source="actback.csv" ]
| stats dc(source) AS numSources values(*) AS * BY "LAN MAC Address"
| where numSources=2
0 Karma

clairebesson
Explorer

Thanks for your answer. Unfortunatly it doesn't work. In source 1 there are some values that are NULL for LAN MAC Address. I don't know if it changes something...

0 Karma

woodcock
Esteemed Legend

OK, then try this:

| inputlookup awback.csv | eval source="awback.csv" | eval "LAN MAC Address"=coalesce($LAN MAC Address$, "NULL")
| appendpipe [|inputlookup actback.csv | eval "MAC Address"=split("MAC Address",":") | mvexpand "MAC Address" | rename "MAC Address" as "LAN MAC Address" | eval source="actback.csv" ]
| stats dc(source) AS numSources values(*) AS * BY "LAN MAC Address"
0 Karma

clairebesson
Explorer

It doesn't work. I was wondering if NULL and empty fields is the same thing.

0 Karma

woodcock
Esteemed Legend

It absolutely should work unless you have not described your data correctly. The only way to know for sure is to provide sample event data.

0 Karma

clairebesson
Explorer

Here are sample of the data:
source: awback.csv
LAN MAC Address
AB:34:dd:56:78:GF
(empty value)
CB:34:ds:45:gd:45
DR:45:ds:FR:56:G7

(empty value) etc...

source: actback.csv
MAC Address
AB:34:dd:56:78:GF
CB:34:ds:45:gd:45
DR:45:ds:FR:56:G7

GT:45:33:eE:45:eD
etc...

0 Karma

woodcock
Esteemed Legend

I mocked up your sample data with my original solution and it works perfectly:

|noop|stats count AS "LAN MAC Address"
| eval "LAN MAC Address" = "AB:34:dd:56:78:GF,CB:34:ds:45:gd:45,DR:45:ds:FR:56:G7"
| makemv delim="," "LAN MAC Address" | mvexpand "LAN MAC Address"
| eval source="awback.csv"
| appendpipe [
|noop|stats count AS "MAC Address"
| eval "MAC Address" = "AB:34:dd:56:78:GF,CB:34:ds:45:gd:45,DR:45:ds:FR:56:G7,GT:45:33:eE:45:eD"
| makemv delim="," "MAC Address" | mvexpand "MAC Address"
| rename "MAC Address" as "LAN MAC Address"
| eval source = "actback.csv"]
| stats dc(source) AS numSources values(*) AS * BY "LAN MAC Address"
| where numSources=2

Which results in this output:

  LAN MAC Address    numSources         source
AB:34:dd:56:78:GF             2    actback.csv
                                    awback.csv
CB:34:ds:45:gd:45             2    actback.csv
                                    awback.csv
DR:45:ds:FR:56:G7             2    actback.csv
                                    awback.csv
0 Karma

HeinzWaescher
Motivator

What about someting like this:

source="awback.csv" OR source="actback.csv"
| rename "MAC Address" as LAN_MAC_Address, "LAN MAC Address" AS LAN_MAC_Address, "PO Number" AS PO_Number
| stats values(Status) AS Status, values(Group) AS Group, values(Serial) AS Serial, values(PO_Number) AS PO_Number BY LAN_MAC_Address

0 Karma

clairebesson
Explorer

Thanks for your answer. It doesn't work. It only displays fields from actback.csv

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...