topic How to join two sources based on two search fields? in Splunk Search

How to join two sources based on two search fields?

vmnguyen — Wed, 02 Dec 2015 19:14:22 GMT

I have two sets of data:
1. sourcetype=app "DEBUG A" function=UpdateCartItemStatus status=Rejected
2. sourcetype=app "DEBUG B" function=UpdateCartItemStatus

Set 1 (DEBUG A) also has the fields unitID1, unitID2, and user1
Set 2 (DEBUG B) also has the fields unitID1, unitID2, and user2

I would like to join data set 1 with data set 2 on unitID1 and unitID2 and get a count of the number of instances this occurs per user2. Ideally this would be as efficient as possible as the data sources are large, searches can span long periods of time, and they are constantly being refreshed. A join is not required, it was just the first thing I thought of.

I am using the dashboard editor for Splunk Enterprise.

Re: How to join two sources based on two search fields?

sundareshr — Wed, 02 Dec 2015 19:33:59 GMT

Try this?

search for set 1 data | eval unit=unitd1."::::".unitd2 | append [search for set 2 data | eval  unit=unitd1."::::".unitd2] | chart limit=0 count by unit over user

Re: How to join two sources based on two search fields?

vmnguyen — Wed, 02 Dec 2015 21:02:25 GMT

Hm...let me clarify:

Datapoints:

sourcetype=app "DEBUG A" function=UpdateCartItemStatus status=Rejected unitID1=1008908999 unitID2 = front user=john
sourcetype=app "DEBUG A" function=UpdateCartItemStatus status=Rejected unitID1=1008908999 unitID2 = back user=john
sourcetype=app "DEBUG B" function=UpdateCartItemStatus status=Printed unitID1=1008908999 unitID2 = front user=mary
sourcetype=app "DEBUG B" function=UpdateCartItemStatus status=Printed unitID1=1008908999 unitID2 = back user=mary

I want to count how many times Mary updated the status as "Printed" and DEBUG A reported that it was Rejected. The output should have a count of 2 for user=Mary.
Thus, I want to link datapoint 3 to datapoint 1 on and datapoint 4 to datapoint 2 using unitID1 and unitID2.

Re: How to join two sources based on two search fields?

sundareshr — Wed, 02 Dec 2015 21:24:07 GMT

will the unitid be the same for all (1008908999). how did you know that 3 should be liked to 1?

Re: How to join two sources based on two search fields?

vmnguyen — Wed, 02 Dec 2015 21:34:19 GMT

Yes, all four of those datapoints would have the same unitID1, but there are millions of datapoints with different unitIDs. We know 3 should be linked to 1 because they share the same unitID1 and unitID2.

Re: How to join two sources based on two search fields?

sundareshr — Wed, 02 Dec 2015 21:36:42 GMT

How about this

search to get all data using append | transaction unitID2 unitID1 startswith="debug=A" endswith="debug=B" maxevents=2 keepevicted=f

The transaction command has a few more options you can explore