topic Re: I need a way to join two searches from different indexes in Splunk Search

I need a way to join two searches from different indexes

psomeshwar — Mon, 18 Mar 2024 16:01:49 GMT

Currently, I need to join information from two different indexes. I cannot show the information as it is confidential, but I can give a general overview of what it should look like

Search:

index=index1 sourcetype=sourcetype1 | table ApplicationName, ApplicationVersion, ApplicationVendor, cid

Result:

ApplicationName ApplicationVersion ApplicationVendor cid
name 1.0.3 vendor 78fds87324
...
...

Search2:

index=index2 sourcetype=sourcetype2 | table hostname, user, cid

Result:

hostname user cid
domainname username 78fds87324
...
...

What I need is a way to show the ApplicationName, ApplicationVersion, ApplicationVendor, hostname and username all in one table connected through the cid. Anyone have any ideas?

Re: I need a way to join two searches from different indexes

gcusello — Mon, 18 Mar 2024 14:55:12 GMT

Hi @psomeshwar,

forget the join command because your search will be very slow!

You should try to use the stats command.

Are there some rule in your join? e.g. results presnt in both the indexes or only in one of them?

I give you the solution without constrains:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) | stats values(ApplicationName) AS ApplicationName values(ApplicationVersion) AS ApplicationVersion values(ApplicationVendor) AS ApplicationVendor values(hostname) AS hostname values(username) AS username BY cid

Ciao.

Giuseppe

Re: I need a way to join two searches from different indexes

ITWhisperer — Mon, 18 Mar 2024 14:56:03 GMT

Start with this and see if that works for you - if not please try to explain with as much detail as you can as to why it doesn't work.

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2 ) | stats values(ApplicationName) as ApplicationName, values(ApplicationVersion) as ApplicationVersion, values(ApplicationVendor) as ApplicationVendor, values(hostname) as hostname, values(user) as user by cid

Re: I need a way to join two searches from different indexes

psomeshwar — Mon, 18 Mar 2024 15:16:08 GMT

Hello,

Thanks, this does help a little, however, there is one problem. One of the indexes has their events in a json format, and the cid is formatted as jsonevent.cid. As a result, I am only getting one side of the events, and the other is blank. Is there a way to work aroudn this

Re: I need a way to join two searches from different indexes

gcusello — Mon, 18 Mar 2024 15:26:18 GMT

Hi @psomeshwar ,

rename it to have the same field name:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) | rename jsonevent.cid AS cid | stats values(ApplicationName) AS ApplicationName values(ApplicationVersion) AS ApplicationVersion values(ApplicationVendor) AS ApplicationVendor values(hostname) AS hostname values(username) AS username BY cid

Ciao.

Giuseppe

Re: I need a way to join two searches from different indexes

psomeshwar — Mon, 18 Mar 2024 16:01:08 GMT

Hello @gcusello

I tried that and it didn't work. Let me show how each search works:

Result:

cid ApplicationName ApplicationVersion ApplicationVendor hostname username
743fsd234 AppName AppVersion AppVendor null null

Result:

cid ApplicationName ApplicationVersion ApplicationVendor hostname username
743fsd234 null null null hostname username

Re: I need a way to join two searches from different indexes

gcusello — Mon, 18 Mar 2024 15:42:14 GMT

Hi @psomeshwar,

what are the exact fieldnames of cid in both the indexes?

if they are cid and jsonevent.cid (it's a supposition, please confirm that), please try again the above solution, using the correct field name in the rename command.

Ciao.

Giuseppe

Re: I need a way to join two searches from different indexes

psomeshwar — Mon, 18 Mar 2024 15:48:31 GMT

Hello @gcusello

That is exactly what I did, the field name for cid in index1 is "cid" and the field name for cid in index2 is "jsonevent.cid" When I used the rename command, I only got the results from index2 and when I did not use the rename command, I only got the results from index1

Re: I need a way to join two searches from different indexes

gcusello — Mon, 18 Mar 2024 15:54:54 GMT

Hi @psomeshwar ,

please try this:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) | eval cid=coalesce(cid,jsonevent.cid) | stats values(ApplicationName) AS ApplicationName values(ApplicationVersion) AS ApplicationVersion values(ApplicationVendor) AS ApplicationVendor values(hostname) AS hostname values(username) AS username BY cid

Ciao.

Giuseppe

Re: I need a way to join two searches from different indexes

psomeshwar — Mon, 18 Mar 2024 18:44:08 GMT

Hello @gcusello

I managed to get it to work. The solution I used was:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) | rename cid as cid1 | rename jsonevent.cid as cid2 | eval jcid = coalesce(cid1, cid2) | stats stats values(ApplicationName) AS ApplicationName values(ApplicationVersion) AS ApplicationVersion values(ApplicationVendor) AS ApplicationVendor values(hostname) AS hostname values(username) AS username BY jcid

Thanks, this thread helped me a lot

Re: I need a way to join two searches from different indexes

gcusello — Mon, 18 Mar 2024 16:39:36 GMT

Hi @psomeshwar ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉