Splunk Search

I need a way to join two searches from different indexes

psomeshwar
Path Finder

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?

Labels (2)
Tags (1)
0 Karma
1 Solution

psomeshwar
Path Finder

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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @psomeshwar ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...