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

[clairebesson]

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!

[carmackd]

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

[woodcock]

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

[clairebesson]

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

[woodcock]

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"

[clairebesson]

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

[woodcock]

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.

[clairebesson]

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

[woodcock]

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

[HeinzWaescher]

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

[clairebesson]

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