Splunk Search

Combine 3 different source type in single table

ND
Engager

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

 

Labels (2)
0 Karma

Richfez
SplunkTrust
SplunkTrust

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

.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!