How to consolidate events that have an ID field with different names across multiple sourcetypes?

[changux]

Hi all.

I have almost 20 different sourcetypes. Field names in sourcetypes are different and I don't have the same in more than 1 sourcetype. Each sourcetype has a "slice" of a record using an ID, but this ID has different names across the sourcetypes. I mean:

sourcetype: example1
ID Field: ex1_id
ex1_id: 5555
ex1_type: Tool

sourcetype: example2
ID Field: ex2_id
ex2_id: 5555
ext2_name: SN56616

sourcetype: example3
ID Field: ex3_id
ex2_id: 5555
ext3_kind: none

I need to search in all sourcetypes and create a resulting table that looks like:

ID     |   ext1_type   |  ext2_name    |  ext3_kind
5555   |   Tool        |  SN56616      |  none

I tried to create a field alias by sourcetype to the appropriate ID Field name to use the same name like ID to facilitate the search. I don't how proceed, transaction? join?

Thanks!

[twinspop]

sourcetype=example* | stats values(ex1_type) as ext1_type values(ext2_name) as ext2_name values(ext3_kind) as ext3_kind by ID

Assumptions:

You have a field extraction for ID Field as ID
You have field extractions for ex1_type, ext2_name and ext3_kind

[MuS · Accepted solution]

Hi changux,

Take a look at this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... to learn on this topic. But meanwhile try this search:

sourcetype=example1 OR sourcetype=example2 OR sourcetype=example3
| eval ID=case(sourcetype="example1",ex1_id,sourcetype="example2",ex2_id,sourcetype="example3",ex3_id,) 
| stats values(*) AS * by ID
| table ID *

Hope this helps ...

cheers, MuS

[changux]

Thanks, awesome!