Splunk Search

How to create union of two searches with help of a common id field present in both searches

jso1996
New Member

example
Result from search 1
XY D 1
AB A 3
CD B 2

Result from search 2
ST K 3
GF L 2

Required Join/Combined Result
AB A 3 ST K
CD B 2 GF L

0 Karma

woodcock
Esteemed Legend

You can also check out the selfjoin command which should work well for this use case:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Selfjoin

0 Karma

woodcock
Esteemed Legend

Here is your PoC (you will need to make some adjustments):

| makeresults 
| eval raw="XY D 1:::AB A 3:::CD B 2" 
| makemv delim=":::" raw 
| mvexpand raw 
| rename raw AS _raw 
| rex "(?<a1>\S+)\s+(?<a2>\S+)\s+(?<a3>\S+)" 
| append 
    [| makeresults 
    | eval raw="ST K 3:::GF L 2" 
    | makemv delim=":::" raw 
    | mvexpand raw 
    | rename raw AS _raw 
    | rex "(?<b1>\S+)\s+(?<b2>\S+)\s+(?<b3>\S+)" ]
| eval joiner=coalesce(a3,b3)
| fields - a3 b3
| stats values(*) AS * BY joiner
0 Karma

richgalloway
SplunkTrust
SplunkTrust

What are the two searches?

---
If this reply helps you, Karma would be appreciated.
0 Karma

jso1996
New Member

'Macro1' rex "PT(?P[0-9.]+)" | eval cil_services=substr(rtrim(service,"PS"),4) | eval respTime=ltrim(rtrim(timeDiff,"S"),"PT") | rename Date_CIL as Date, uri_path as Service, CIL_MethodName as Method, response_time as Response_Time | table CIL_RequestID, Date, Service, Method, Response_Time

'Macro2' | rex "PT(?P[0-9.]+)" | eval cil_services=substr(rtrim(service,"PS"),4) | eval respTime=ltrim(rtrim(timeDiff,"S"),"PT") | rename field1 as Date, field33 as Service, CAM_MethodName as Method, ResponseTime_CAM as Response_Time | table Date, Service, Method, Response_Time

CIL_RequestID field is present in both with different names.

0 Karma

macadminrohit
Contributor

There are couple of Solutions like below authors have mentioned if the format of the output is not important : I am putting what i did with your kind of data .

| makeresults 
| eval Data1="XY D 1,AB A 3,CD B 2" 
| makemv Data1 delim="," 
| mvexpand Data1 
| eval Field1=mvindex(split(Data1," "),0) 
| eval Field2=mvindex(split(Data1," "),1) 
| eval Field3=mvindex(split(Data1," "),2) 
| fields _time Field1 Field2 Field3
| append 
    [| makeresults 
    | eval Data2="ST K 3,GF L 2" 
    | makemv Data2 delim="," 
    | mvexpand Data2 
    | eval Field1=mvindex(split(Data2," "),0) 
    | eval Field2=mvindex(split(Data2," "),1) 
    | eval CIL_ID=mvindex(split(Data2," "),2)
    | fields _time Field1 Field2 CIL_ID
    | rename CIL_ID as Field3 ] 
| stats values(Field1) as Field1 values(Field2) as Field2 count by Field3
| nomv Field1 
| nomv Field2 | where count>1

I think you are trying to get the common ID between the two searches and trying to join the results. The above should show you the concept. Please let us know which of the solutions work for you.

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

Try something like this:

index="scratchpad" sourcetype="ans1129" source="ans_1129*" 
| stats dc(source) as distinctsource values(f1) as f1 values(f2) as f2 values(f3) as f3 values(f4) as f4 by id 
| where distinctsource>1 
| sort f1 
| table f1 f2 id f3 f4

This is the output of this search using your data. I used f1 - f4 as the other fields that weren't the common id.
alt text

jso1996
New Member

The situation is something like this, I am writing a search query and data is coming from a macro, another search query and data is coming from another macro, need to make a join like explained above and data is in 500,000-1000000 count.
Please check the comment section of the question

0 Karma
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...