How to merge two services from same index with common field?

[arunakalla]

I wanted to join services (part of same index) with common field and show chosen fields from both searches..

Index=test service=serv1

Name

RecordID

Version

Index=test service=serv2

State

RecordID

Version

wants to combine two searches by RecordID from Service2  (meaning to optimize query needs to first take RecordID from Service2 and match with Service1)... and notice fieldname Version is common both services. so hence wants to rename version field in service2 to version2. 

And final result is

Name Version1 Version2 

SQL Query:

Select A.Name, A.version, B.version

from Service1 A, Service2 B

where B.RecordID = A.RecordID

[richgalloway]

It would help to know what you've already tried so we don't suggest the same thing.

Since you appear to know SQL you might find this document helpful: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk

Here's one way to do it

index=test service=serv1
| join RecordID [ search index=test service=serv2 | rename version as version2 ]
| table Name RecordID version version2

Here's another that doesn't use join.

index=test (service=serv1 OR service=serv2)
```If Name is null then this is a serv2 event so set version2=version```
| eval version2 = if(isnull(Name), version, null())
| stats values(*) as * by RecordID
| table Name RecordID version version2

[arunakalla]

Thanks for the reply first one worked.. Stats did not work.. I am not getting the  version2 values.. pls see my response to other reply below

Docs says join  only works for 50000 records and after some time it terminates.. hence wants needs to make stats work

[yuanliu]

Just to highlight that join is more expensive than stats.  Meanwhile, the second method (no join) may need to remove original values of version from serv2 if version2 has distinct values from those from serv1.

index=test (service=serv1 OR service=serv2)
| eval version2 = if(service=serv2, version, null())
| eval version = if(service=serv1, version, null())
| stats values(*) as * by RecordID
| table Name RecordID version version2

[arunakalla]

that did not work .. can only see Name and RecordID fields... version is populated because it's able to see that name in the original serv1 .. version1 is null. To test the above I modified your reply to following

index=test (service=serv1 OR service=serv2)
| eval version2 = if(service=serv2, version, null())
| eval version1 = if(service=serv1, version, null())
| stats values(*) as * by RecordID
| table Name RecordID version1 version2

 and now both version1 and version2 is empty..

also tried this with same results as above 

index=test (service=serv1 OR service=serv2)
| eval version2 = case(service=serv2, version)
| eval version1 = case(service=serv1, version)
| stats values(*) as * by RecordID
| table Name RecordID version1 version2

Just to highlight.. End result I want is only values from Serv1, where there is a matching RecordID in the Serv2.. The whole point of this exercise is I want to replace all values of version in the Serv1 with version values from Serv2 where there is a matching RecordID and discard the rest. Serv1 is major data source and Serv2 is minor.

[yuanliu]

My bad.  Literal values should be quoted when use in eval.

index=test (service=serv1 OR service=serv2)
| eval version2 = if(service=="serv2", version, null())
| eval version1 = if(service=="serv1", version, null())
| stats values(*) as * by RecordID
| table Name RecordID version1 version2

[arunakalla]

This is acting like left join with difference.. so basically keeping all records of the version1 and inserting value of the version2 into the version1 (now I can see old value of version1+version2) , along with version2 value in it's column.  it looks like this

Serv1

Name	RecordID	Version
Name1	RecordID1	^5
Name2	RecordID2	5.5
Name3	RecordID3	5.7

Serv2

Place	RecordID	Version
Pl1	RecordID1	5.5
Pl2	RecordID2	5.5
Pl3	RecordID7	4.7

End Result

Name	RecordID	Vesrion1	Version2
Name1	RecordID1	^5 5.5	5.5
Name2	RecordID2	5.5	5.5
Name3	RecordID3	5.7

[yuanliu]

This is indeed very unexpected.  I suspect that your serv1 data already contain multiple Version values.  Could you examine data with this in the same period you tested the other search:

index=test (service=serv1)
| eval Version2 = if(service=="serv2", Version, null())
| eval Version1 = if(service=="serv1", Version, null())
| stats values(*) as * by RecordID
| table Name RecordID Version1 Version2

(I notice that you originally posted Version as capitalized, but my sample code used all lowercase "version".  I guess that you have corrected this in your tests.) In this test, there is no data from serv2.  Therefore, I expect this result

Name	RecordID	Vesrion1	Version2
Name1	RecordID1	^5 5.5
Name2	RecordID2	5.5
Name3	RecordID3	5.7

If the above is the case, the search is working as expected.  To replace Version with version2 where RecordID match in serv1 and serv2, just force it.

index=test (service=serv1 OR service=serv2)
| eval version2 = if(service=="serv2", Version, null())
| eval Version = if(service=="serv1", Version, null())
| stats values(*) as * by RecordID
| eval Version = if(isnull(version2), Version, version2)

[arunakalla]

yes other search is fine, except that it's getting throttled/timed out

[yuanliu]

I am confused.  Did you confirm that from serv1, Version1 gives the exact multiple values as if searching both serv1 and serv2?

[arunakalla]

My requirement is not to merge Version2 and Version1 and display multiple values in Version1.. Merge two services then display only rows that matches RecordID in Version2. Instead

(1) It is displaying all values from Serv1

(2) Version1 is displaying merged values

[yuanliu]

---

@arunakalla wrote:

My requirement is not to merge Version2 and Version1 and display multiple values in Version1.. Merge two services then display only rows that matches RecordID in Version2. Instead

(1) It is displaying all values from Serv1

(2) Version1 is displaying merged values

---

(The forum really mixed up response order:-) Sorry I didn't explain clearly.  I suspect that your data contains multiple values, or the output from the search will not be as illustrated in the final results.  Maybe one step at a time.  Can you confirm the output from serv1 alone:

index=test (service=serv1)
| eval Version2 = if(service=="serv2", Version, null())
| eval Version1 = if(service=="serv1", Version, null())
| stats values(*) as * by RecordID
| table Name RecordID Version1 Version2 service

How does it look like? (Note this search includes nothing from serv2 unless service itself is also multivalued.) Then, substitute for serv2 in the first line to verify whether multiple values occur in serv2.

[arunakalla · Accepted solution]

    index=test (service=serv1 OR service=serv2)
    | eval Version2 = if(service=="serv2", Version, null())
    | eval Version1 = if(service=="serv1", Version, null())
    | stats values(*) as * by RecordID
    | table Name RecordID Version1 Version2
    | where isnotnull(Version2)

the above achieved my expected result.. Thanks you for all your help @yuanliu  

[yuanliu]

Did the previously revised search work?

[arunakalla]

Yes, double quotes worked and hence the above issue..

[arunakalla]

I tried all help and  [used Join command and/or stats command] .. nothing worked for my case