I need a way to join two searches from different indexes

[psomeshwar]

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?

[ITWhisperer]

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

[gcusello]

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

[psomeshwar]

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

[gcusello]

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

[psomeshwar]

Hello @gcusello 

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

(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

Result:

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

(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

Result:

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

[gcusello]

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

[psomeshwar]

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

[gcusello]

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

[psomeshwar · Accepted solution]

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

[gcusello]

Hi @psomeshwar ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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