Combine counts from 2 separate searches using subsearches

[john_byun]

I'm trying to combine two separate searches using the join command, but it's not working. The inner search counts the number of "purchases" using the transaction command. The outer search counts eventtypes by "storeNo". I would like the final table to display errors, purchases, and their ratio by storeNo.

The search below does not display the correct values for "errorcount" and I'm not sure how to add the ratio column.

sourcetype=production eventtype="completedPurchase" | join [search sourcetype=production eventtype="totalErrors" | transaction host maxspan=3m | stats count as errorcount] | eval ratio = count / errorcount | stats count by storeNo, errorcount

Any help would be great appreciated.

[somesoni2]

Extending the solution by "d", replace join by append to get all the rows (where either of the count is null)

sourcetype=production eventtype="completedPurchase" | stats count by storeNo | append
[search sourcetype=production eventtype="totalErrors" | transaction host maxspan=3m | stats count by storeNo]

[somesoni2]

Description:
first stats will give list of all available storeNo, then join that with second subsearch to get PurchaseCount for storeNo (whatever is available). join that result with second subsearch to get ErrorCount. and finally calculate percentage. you can also add "|eval PurchaseCount=coalesce(PurchaseCount,0)" or something like that to assign default value where PurchaseCount or ErrorCount is not available

[somesoni2]

Can you try following

sourcetype=production | stats count by storeNo|fields -count|join storeNo [search sourcetype=production eventtype="completedPurchase" | stats count as PurchaseCount by storeNo] | join storeNo [search sourcetype=production eventtype="totalErrors" | transaction host maxspan=3m | stats count as ErrorCount by storeNo]|table storeNo,PurchaseCount,ErrorCount|eval Perc=PurchaseCount*100/ErrorCount

[john_byun]

Append gives me a blank errorcount column.

[_d_]

Ah ok, you probably need to change the type of join to outer:

... | join storeNo type=outer...

Join Options (http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join):

type=inner | outer | left

Description: Indicates the type of join to perform. Basically, the difference between an inner and a left (or outer) join is how they treat events in the main pipeline that do not match any in the subpipeline. In both cases, events that match are joined. The results of an inner join will not include any events with no matches. A left (or outer) join does not require each event to have matching field values; and the joined result retains each event—even if there is no match with any rows of the subsearch. Defaults to inner.

[sideview]

beware that the type=outer the join command is actually a left outer join, meaning it will still discard null results from one side. For this reason I recommend looking into the append command here and not using join at all.

[john_byun]

This works! Thanks very much for your help!

[_d_]

You can try something like this:

sourcetype=production eventtype="completedPurchase" | stats count by storeNo | join storeNo [search sourcetype=production eventtype="totalErrors" | transaction host maxspan=3m | stats count by storeNo]

[john_byun]

Almost there. The search is working, but it's not showing stores where either count or errorcount has a null value. I've tried using fillnull, but it's not having the desired affect.

How do I get it to show me all stores that have a value in either field?

[_d_]

You need to join on a field - most likely storeNo - and in order to do that you need to expose storeNo in your subsearch's stats pipeline.

[john_byun]

Can you be a little more specific? How can I combine these two searches to show "completedPurchase" and "totalErrors" count in the same table so I can run an eval against them? The individual searches look like this:

sourcetype=production eventtype="completedPurchase" | stats count by storeNo

sourcetype=production eventtype="totalErrors" | transaction host maxspan=3m | stats count by storeNo