How to show data from TWO different sourcetypes ?

[zacksoft_wf]

I have,
sourcetype_A  (fields : ID, age, city, state)
sourcetype_B  (fields : ID, job, salary, gender)

The fields "ID" is common in both sourcetype_A and B but with a caveat.
example1 : for ID = 1687, it is present in sourcetype_A as 0001687 , in sourcetype_B as 1687
example2 : for ID = 9843, it is present in sourcetype_A as 009843 , in sourcetype_B as 9843
example3 : for ID = 8765, it is present in sourcetype_A as 08765 , in sourcetype_B as 8765
where 1687, 9843, 8765 are the actual IDs. zeros are creating mess in sourcetype_A .

I am not allowed to use join, So this is what I am trying but I am not seeing all my data.

===================================
(index=country) sourcetype=sourcetype_A OR sourcetype=sourcetype_B
| eval ID = ltrim(ID,"0")
| eventstats dc(sourcetype) as dc_st
| where dc_st >1
| table ID, age, city, state,  job, salary, gender
===================================

I also tried | stats values (age) as age 
                                ........
     ..........................................................
                  by ID.
But stats gave me massive multivalue fields with messy duplicates. I am asked to get in one row per data (no multivalues )

Any help ?

[gcusello · Accepted solution]

Hi @zacksoft_wf,

use stats, something like this:

index=country (sourcetype=sourcetype_A OR sourcetype=sourcetype_B)
| eval ID = ltrim(ID,"0")
| stats 
   count AS dc_st 
   values(age) AS age 
   values(city) AS city 
   values(state) AS state 
   values(job) As job 
   values(salary) AS salary 
   values(gender) AS gender
   By ID
| where dc_st >1

if you have a multivalue field, you can expand it adding at the end:

| mvexpand <field>

Ciao.

Giuseppe

[gcusello]

Hi @zacksoft_wf,

good for you, see next time!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉