topic Re: Join result of two queries with common field ? in Splunk Search

Join result of two queries with common field ?

ayush8878 — Wed, 04 Dec 2019 02:31:06 GMT

Hi,

I have a use case where i need to join result of two septate logs on the basis of common field(breadcrumbId).
Below is the query i used but i am not getting any results for this query

source="/opt/jboss/jboss-fuse/data/log/access_log" OR "/opt/jboss/jboss-fuse/data/log/fuse.log" ("Audience value in the JWT is*" OR ("path=/rest/cases/" "filename*=")) | stats values(*) as * by breadcrumbId | table breadcrumbId AccessedFrom

While if I try separately I am getting results

Query 1:source="/opt/jboss/jboss-fuse/data/log/fuse.log" "Audience value in the JWT is" | table breadcrumbId AccessedFrom 
Query 2:source="/opt/jboss/jboss-fuse/data/log/access_log" (("path=/rest/cases/" "filename*=")) | stats values(*) as * by breadcrumbId filename

Re: Join result of two queries with common field ?

MuS — Wed, 04 Dec 2019 02:39:45 GMT

Hi ayush8878,

try this:

( source="/opt/jboss/jboss-fuse/data/log/fuse.log" "Audience value in the JWT is" ) OR ( source="/opt/jboss/jboss-fuse/data/log/access_log" ("path=/rest/cases/" "filename*=")) 
| eval filename=if(isnotnull(filename), filename, "none") 
| stats values(*) AS * by breadcrumbid filename

Hope this helps ...

cheers, MuS

Re: Join result of two queries with common field ?

ayush8878 — Wed, 04 Dec 2019 03:05:01 GMT

Thanks MuS but this way I am getting resuls only from fuse.log while I need data from access.log and fuse.log merged on breadcrumbid

Re: Join result of two queries with common field ?

woodcock — Wed, 04 Dec 2019 05:46:25 GMT

It must be that the first source has no events with values for filename so leave it in the values(*) pile like this:

index="YouShouldAlwaysSpecifyAnIndex" AND
((source="/opt/jboss/jboss-fuse/data/log/fuse.log" AND "Audience value in the JWT is") OR
(source="/opt/jboss/jboss-fuse/data/log/access_log" AND "path=/rest/cases/" AND "filename*="))
| stats values(*) AS * BY breadcrumbId

Re: Join result of two queries with common field ?

MuS — Wed, 04 Dec 2019 20:27:24 GMT

Okay, looking at the second search on the access_log you use "filename*=" so you don't actually search for a field called filename. The first thing you need to do here is create a field called filename and then it will work. Assuming the filename* thingy does not contain any spaces, try this:

( source="/opt/jboss/jboss-fuse/data/log/fuse.log" "Audience value in the JWT is" ) OR ( source="/opt/jboss/jboss-fuse/data/log/access_log" ("path=/rest/cases/" "filename*=")) 
 | rex "filename[^=]*=(?<filename>[^\s]+)" 
 | eval filename=if(isnotnull(filename), filename, "none") 
 | stats values(*) AS * by breadcrumbid filename

Hope this helps ...

cheers, MuS