Do math on two count_distinct results

Follow

Matt Wilson

• July 14, 2021 15:38

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!

0

• 

  Vishal Choudhary

  • July 20, 2021 00:50
  • Edited

  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 Finalsum

  Similar 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              578
  
  

  In 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 Finalsum

  Output:
  #   Finalsum
  1   578

  Now, 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

  1

  Comment actions Permalink
• 

  Matt Wilson

  • July 20, 2021 16:15

  Thank you Vishal!
  
  This approach did work.  I am not sure why my first approach did not, my guess is that the parenthesis around the division was not acceptable for some reason.  

  0

  Comment actions Permalink

Please sign in to leave a comment.