How to link two sources with equals fields in each?

[clairebesson]

Hey everyone,
Here is my problem:
I have two sources (Source1 and Source2):
* In source1 I have the field "device number" written like that: "Inst-C6:82:c3"
* In the second source I have the field "MAC Address" written like that: "9B:DC:DF:C6:82:c3"

As you can see, the parts in bold are the same. I would like to create a table with this two fields that displays when field1=field2.
I don't know how to select the part in bold and add a condition to create a table.
Could you please help me with that?
(This will permit me to link these two different sources)

Thanks in advance !

[chanmi2 · Accepted solution]

Try this:

source=source1 | eval common_part = replace("device number", "^Inst-(\w\w:\w\w:\w\w)$", "\1") | join common_part [source=source2 |eval common_part = replace("Mac Address", "\w\w:\w\w:\w\w:(\w\w:\w\w:\w\w)$", "\1")]

[somesoni2]

Try something like this

source=source1 OR source=source2 | table "Device Number" "Mac Address" | eval commonfield=replace(coalesce('Device Number','Mac Address'),"(.*)(\w\w:\w\w:\w\w)$","\2") | stats values(*) as * by commonfield

[richgalloway]

Perhaps something like this will get you started.

source=Source1 OR source=Source2 | rex field="device number" "Inst-(?P<part1>\w\w:\w\w:\w\w)" | rex field="MAC Address" "\w\w:\w\w:\w\w:(?P<part2>\w\w:\w\w:\w\w)" | where part1=part2 | ...