Combine 3 different source type in single table

ND — Fri, 09 Oct 2020 18:37:11 GMT

Hi,

I want to combine 3 different source type in single table. The column names are different in all the 3 source type to join them.

Eg.

Source type = a

ID name location

Source type = b

PID country

Source type = c

id contact_number

Kindly help to get output as follows:

PID name location country contact_number

There are more than 50 column I want in table.

Thank & Regards

Re: Combine 3 different source type in single table

Richfez — Fri, 09 Oct 2020 18:56:45 GMT

if PID is equal to ID (I hope it is, or else you either didn't give us enough information to solve the problem, or the problem is unsolveable because they're not actually related records... 🙂 )

(sourcetype=a AND index=foo) OR (sourcetype=b AND index=bar) OR (sourcetype=c) | eval master_id = case(sourcetype=a, ID, sourcetype=b, PID, sourcetype=c, id) | stats values(*) as * by master_id

You can use, like I did, an eval/case statement to collapse all possible field names that are your ID into one field you can stats on.

Or you could use the coalesce function.

Or a couple of eval/if statements.

And the `stats values(*) as * by ...` is really just shortcut to "doing all the fields". You could replace it with values(field1) as newnameforField1, values(field2) as ...

Or list(field1) as newnameforField1, ...

or any other pile of possibilities.

Anyway.

The idea is just pile them all together, create a new ID that is the same field name for all, then let stats "group" then by that new id field.

happy splunking!

-Rich

topic Re: Combine 3 different source type in single table in Splunk Search

Combine 3 different source type in single table

Re: Combine 3 different source type in single table