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.
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]
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
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
Append gives me a blank errorcount column.
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.
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.
This works! Thanks very much for your help!
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]
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?
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.
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