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, an upvote 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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!