topic Re: How to create one table by combining a common field with different field names from two sources? in Splunk Search

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

clairebesson — Wed, 25 Nov 2015 15:21:22 GMT

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!

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

HeinzWaescher — Tue, 29 Sep 2020 07:59:43 GMT

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

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

clairebesson — Wed, 25 Nov 2015 15:51:47 GMT

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

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

woodcock — Wed, 25 Nov 2015 16:14:57 GMT

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

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

clairebesson — Wed, 25 Nov 2015 21:02:16 GMT

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

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

carmackd — Wed, 25 Nov 2015 23:08:20 GMT

If I understand correctly, this should work.

Supposed to be a single astrik

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

woodcock — Wed, 25 Nov 2015 23:34:03 GMT

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"

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

clairebesson — Thu, 26 Nov 2015 21:54:06 GMT

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

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

woodcock — Sun, 29 Nov 2015 01:10:12 GMT

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.

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

clairebesson — Mon, 30 Nov 2015 17:31:58 GMT

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

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

woodcock — Wed, 02 Dec 2015 15:50:15 GMT

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