Do math on two count_distinct results
I'm trying to produce a single value for a dashboard. I want to chart the rate between two different count_distinct results for a single query. But Sumo Logic doesn't seem to allow math operations upon aggregate operation results.
<query>
| parse store_id ...
| parse order_id ...
| count_distinct(order_id) as total_orders, count_distinct(store_id) as total_stores
// this doesn't work
// | (total_orders / total_stores) as avg_orders_per_store
Things I've tried:
1. I tried save and lookup, but I think those would only work as a single combined search.
2. So then I tried a subquery, after finding this article - but that's not working.
3. I also found this post, with no responses to it.
Hoping a new post will garner some attention and help. Thank you very much!
-
Hi Matt,
Thanks for your question and detailed explanation. Based on that I tried a similar query in-house. Here is the query for your reference:
(((_sourceCategory=windows_events)))
| parse "RecordNumber = *;" as recordnumber
| parse "EventCode = *;" as eventcode
| count_distinct(recordnumber) as totalrecords , count_distinct(eventcode) as totalevents
| totalrecords + totalevents as FinalsumSimilar to your query, I'm trying to calculate the distinct count of recordnumber, eventcodes & then adding them together in Finalsum. The above query gave me the following result:
# totalrecords totalevents Finalsum
1 520 58 578In order to get single value Finalsum as a result, I will add following line in my query:
| fields Finalsum(((_sourceCategory=windows_events)))
| parse "RecordNumber = *;" as recordnumber
| parse "EventCode = *;" as eventcode
| count_distinct(recordnumber) as totalrecords , count_distinct(eventcode) as totalevents
| totalrecords + totalevents as Finalsum
| fields FinalsumOutput:
# Finalsum
1 578Now, one can produce a single value dashboard for this. Hope this helps, here is the documentation on fields operator for your reference:
https://help.sumologic.com/05Search/Search-Query-Language/Search-Operators/fields_operator
Hope this helps. Let me know if I'm missing anything here.
Regards,
Vishal
Please sign in to leave a comment.
Comments
2 comments