topic Re: How to show data from TWO different sourcetypes ? in Splunk Search

How to show data from TWO different sourcetypes ?

zacksoft_wf — Fri, 21 Jan 2022 11:44:33 GMT

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 ?

Re: How to show data from TWO different sourcetypes ?

gcusello — Fri, 21 Jan 2022 13:11:32 GMT

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

Re: How to show data from TWO different sourcetypes ?

gcusello — Fri, 21 Jan 2022 15:03:23 GMT

Hi @zacksoft_wf,

good for you, see next time!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉