Splunk - join two search queries having common field

[Nidheesh]

Hi,

I need to join two splunk search queries based on a common field (JoinId).

All I would like to have at the output is to return how many values of that particular common field is mapped from both queries and how many are unmapped from query2.

Can someone help me with this?

query1:

index="source1" "layer-monitor" ServiceEvent "debug"| search NOT ErrorMessage=* | stats count(JoinId) as "Total success"

query2:

index="source2" sourcetype="sourceData" "Message=Log Message invoked" JoinId

I would like to know how many JoinId fields of query2 are unmapped with respect to query1 JoinId

[lfedak_splunk]

Hey @Nidheesh, if either of these solutions work for you, please remember to "Accept" the answer. You can upvote posts as well. (Karma points will be awarded for either action.) Happy Splunking!

[gcusello]

Hi Nidheesh,
Try something like this

index="source2" sourcetype="sourceData" "Message=Log Message invoked"  NOT [ search index=source1 "layer-monitor" ServiceEvent debug NOT ErrorMessage=* | dedup JoinId | fields JoinId ]
| dedup JoinId
| table JoinId

Bye.
Giuseppe

[cmerriman · Accepted solution]

maybe something like this:

(index="source1" "layer-monitor" ServiceEvent "debug" NOT ErrorMessage=*) OR (index="source2" sourcetype="sourceData" "Message=Log Message invoked" JoinId)|eval source1JoinId=if(index="source1",JoinId,null())|eval source2JoinId=if(index="source2",JoinId,null())|stats count(source1JoinId) as s1JoinId count(source2JoinId) as s2JoinId by JoinId|eval unmappedSource2=if(s2JoinId=0 AND s1JoinId>0,1,0)|stats sum(unmappedSource2) as unmappedSource2

that should give you how many JoinIds in source2 are not in source1. You can break the syntax apart pipe by pipe to double check it's working properly