Splunk Search

Splunk search for between 2 sourcetypes where field 1 of source1 matches with field 2 of source 2?

M28
Explorer

I have 2 sourcetype sourcetype="source1" and sourcetype="source2"

This is how sample data looks:

source1:

CID,Cname,CData

Source 2:

CID,key,FName,LName

Here values of CID of source 1 and key of source 2 will be same.Even though CID will be present in source 2 but it will be having different value.

I need to write query to when CID(source 1) = key (source 2) then fetch all other fields from source 1 and source 2 display in table .

 

Any suggestions would be appreciated.

 

 

Labels (2)
Tags (1)

ITWhisperer
SplunkTrust
SplunkTrust
sourcetype="source1" OR sourcetype="source2"
| eval key=coalesce(key,CID)
| stats values(*) as * by key
0 Karma

M28
Explorer

Thank you for the response.

I have tried the above query.But it doesn't seems to be working as coalesce is not matching if the values of CID(source1) and Key(source 2 ) same .Also its displaying only the fields of source 1.Fields of source 2 are not being fetched.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What do you think coalesce is doing?

coalesce is simply creating a field called key using the value in field key (if it exists as in source 2) or CID (if it doesn't exist as in source 1). This is similar to

| eval key=if(isnotnull(key), key, CID)

Having got a value in key, the stats is matching the events from both source 1 and source 2 by the value in key (where key = CID for source 1). Is this not what you wanted?

0 Karma

M28
Explorer

Thank you for the response.

Yes, that is what I am looking for .

But the catch here is CID is present in both source1 and source 2.

source1:

CID, Cname, CData

Source 2:

CID, key, FName, LName

But i need to match CID of source 1 and key of source 2.Since CID field name is common in both the sources its not fetching properly as combined results .Its giving 2 rows of data for each sources having fields as null for other source.

Hope I am making it clear.

Please suggest .

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
sourcetype="source1" OR sourcetype="source2"
| eval common=if(sourcetype="source2",key,CID)
| stats values(*) as * by common
0 Karma

M28
Explorer

Thank you for the response.

it is fetching only the fields of source 1 and not combining the fields of both source 1 and source 2:(

In below CID will be taken from source 1? my use case is to match key of source 2 and CID of source 1But CID present in both the sources.

| eval common=if(sourcetype="source2",key,CID)

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The statement is putting the value from the key field in the common field if the sourcetype is "source2" and the value of the CID field in the common field if the sourcetype is not "source2". How is this not what you want?

0 Karma

M28
Explorer

Thank you for your patience and response.

Just briefing here with sample data.Please suggest.

sourcetype= source1
"CID": 123
"Cname": John
"CData": XYZ

sourcetype= source2
"CID": 285
"Key": 123,
"FName": Ken
"LName": Dan

Here CID(source1) = Key(source 2) so I need result in below format:

"Cname": John
"CData": XYZ

"CID": 285

"FName": Ken
"LName": Dan

But with the below query I am seeing the result like below:

sourcetype="source1" OR sourcetype="source2"

| eval common=if(sourcetype="source2",key,CID)

| stats values(*) as * by common

"common": 123

"CID": 123
"Cname": John
"CData": XYZ

There are no fields of source 2(FName,LName) is fetched here

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults
| fields - _time

| eval sourcetype= "source1", "CID"= 123,"Cname"="John","CData"= "XYZ"
| append
    [makeresults
    | fields - _time
    | eval sourcetype= "source2","CID"= 285,"Key"= 123,"FName"= "Ken","LName"= "Dan"]
``` the lines above just set up your sample data ```    

| eval common=if(sourcetype="source2",Key,CID)
| eval CID=if(sourcetype="source2",CID,null())

| stats values(*) as * by common
| fields - common sourcetype Key
0 Karma

M28
Explorer

Thank you for the response.

Apologies for the multiple threads.

I did try below both the queries you have suggested and its fetching only the fields of source1 .(i.e CID, Cname ,CData). Source2 fields are not being combined and fetched.

I am not sure what am missing here .Please suggest.

sourcetype="source1" OR sourcetype="source2"

| eval common=if(sourcetype="source2",key,CID)

| stats values(*) as * by common

sourcetype="source1" OR sourcetype="source2"

| eval common=if(sourcetype="source2",Key,CID)

| eval CID=if(sourcetype="source2",CID,null())

| stats values(*) as * by common

| fields - common sourcetype Key

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You should only need the lower part

sourcetype="source1" OR sourcetype="source2"
``` Set common to Key if sourcetype is source2 otherwise use CID (or source1 events) ```
| eval common=if(sourcetype="source2",Key,CID)
``` Preserve CID for sourcetype source2 otherwise clear the field (as you don't appear to be interested in this value) ```
| eval CID=if(sourcetype="source2",CID,null())
``` Gather all the fields from both sourcetypes where Key from source2 = CID from source1 ```
| stats values(*) as * by common
| fields - common sourcetype Key

If you are still not getting a match, this seems to suggest that CID in source1 has no matches for Key in source2

0 Karma

M28
Explorer

I will try this .Thank you

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Something like

source IN (source1, source2)
| stats values(Cname) as Cname, values(CData) as CData, values(key) as key, values(FName) as FName, values(LName) as LName values(source) by CID
| where mvcount('values(source)') > 1

Hope this helps.

Tags (1)
0 Karma

M28
Explorer

Thank you for the response.

I have tried this and not working.

As i need to match CID of source 1 and Key of source 2 are same then fetch all the fields of source1 and source 2.One thing to note here though CID is present in source 2 it will be having different value.so need to match if(CID(source1) equals to key(source2) then fetch all fields of source1 and source2). please suggest.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...