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

[jso1996]

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

[woodcock]

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

[woodcock]

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

[richgalloway]

What are the two searches?

[jso1996]

'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.

[macadminrohit]

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.

[kmorris_splunk]

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.

[jso1996]

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