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!
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
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
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...
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"
It doesn't work. I was wondering if NULL and empty fields is the same thing.
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.
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...
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
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
Thanks for your answer. It doesn't work. It only displays fields from actback.csv