Compare two fields to pull data

[nlisle]

Hello,

I currently have a search running on two different indexes pulling different fields.

There is one field called "Device ID" from one index and another called "asset_tag" which is the same information in the same format from a different index which gathers information from that index which I would like to use in my results.

I would like to "match" the two fields so that when "Device ID" is the same as "asset_tag", it will pull the data requested from the second index.

Thank you,

N.

[jawaharas]

If you choose join you can use below syntax.

<YourBaseSearch on Index1>
|  rename "Device ID" as asset_tag
|  join asset_tag
    [<YourBaseSearch on Index2>
    | table asset_tag]

[gcusello]

Hi nlisle,
there many ways to do what you requested.
The easiest is to use a subsearch:

index=index1 [ search index=index2 | rename asset_tag AS  "Device ID" | fields  "Device ID" ]
| ...

this solution has only one limit: if the subsearch has more than 50,000 results, the global result will be incomplete because there's a limit of 50,000 results in subsearches.

So if you could have more than 50,000 results in the secondary search, you have to use a different approach:

index=index1 OR index=index2
| rename asset_tag AS  "Device ID"
| stats values(field1) AS field1 values(field2) AS field2 count BY "Device ID" 
| where count >1
| ...

in this way you haven't any limit;
remember to put in the stats command al the fields (with values option) you want to display.

You could also use the join command but it isn't recommended because it's very slow and has the same limit of subsearch.

Bye.
Giuseppe