Splunk Search

How to show data from TWO different sourcetypes ?

zacksoft_wf
Contributor

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 ?

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @zacksoft_wf,

good for you, see next time!

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...