topic Re: Splunk search for between 2 sourcetypes where field 1 of source1 matches with field 2 of source 2? in Splunk Search

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

M28 — Tue, 29 Nov 2022 17:37:06 GMT

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.

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

yuanliu — Tue, 29 Nov 2022 18:15:13 GMT

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.

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

ITWhisperer — Tue, 29 Nov 2022 18:17:35 GMT

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

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

M28 — Wed, 30 Nov 2022 03:08:50 GMT

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.

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

M28 — Wed, 30 Nov 2022 03:11:47 GMT

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.

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

ITWhisperer — Wed, 30 Nov 2022 08:36:13 GMT

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?

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

M28 — Wed, 30 Nov 2022 09:24:14 GMT

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 .

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

ITWhisperer — Wed, 30 Nov 2022 09:49:14 GMT

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

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

M28 — Wed, 30 Nov 2022 10:09:01 GMT

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)

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

ITWhisperer — Wed, 30 Nov 2022 10:28:55 GMT

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?

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

M28 — Wed, 30 Nov 2022 11:07:01 GMT

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

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

ITWhisperer — Wed, 30 Nov 2022 11:53:50 GMT

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

M28 — Wed, 30 Nov 2022 12:20:05 GMT

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

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

ITWhisperer — Wed, 30 Nov 2022 12:52:12 GMT

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

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

M28 — Thu, 01 Dec 2022 06:59:00 GMT

I will try this .Thank you