Map column from another table to my currently used table

[Jimenez]

Hi all,

I have the following issue. I have a table A 

col1	col2
A	aa
B	bb
C	aa

And a table B

colA	colB
aa	FYI
bb	LOL

I need to add to table A the column colB based on the matching values from col1 (table A) and colA (table B) and it should look like:

col1	colB	col2
A	FYI	aa
B	LOL	bb
C	FYI	aa

so basically map the values from col2 to colA and add colB based on the matches

Thanks for your support,

[yuanliu]

As with any good languages, there are many ways to do this.  A simple and semantically expressive method is to use coalesce.

sourcetype IN (tableA, tableB)
| eval col2 = coalesce(col2, colA)
| stats values(col1) as col1 values(colB) as colB by col2

[PickleRick]

Right. Didn't notice the columns were not named the same in both sourcetypes.

[Jimenez]

They are actually results coming from different event types. Each event contains different fields.

[PickleRick]

Yes. But are those results of some searches that you want to "merge" or do you simply have two different sourcetypes from which different sets of fields are extracted?

If it's the latter, your solution should be relatively simple

<some restriction on index(es)> sourcetype IN (sourcetype1, sourcetype2)
| stats values(colA) as colA values(colB) as colB values(col1) as col1 values(col2) as col2 [...] by common_column

If you want all columns, you might simply go with

values(*) as *

[isoutamo]

Are those tables individual sourcetypes on index or results of your SPL queries? If last, can you share it so we can modify it to create your requested result?

[PickleRick]

What do you mean by "table"? There are several different possible approaches depending on where those "tables" come from.