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 ?
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
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
Hi @zacksoft_wf,
good for you, see next time!
Ciao and happy splunking.
Giuseppe
P.S.: Karma Points are appreciated 😉