Can I count_distinct within transactionize groupings?

Follow

david.kirby

• May 10, 2017 15:30

I have a problem in which I want to know when a certain FieldB changes value while another FieldA remains constant across many log entries. I transactionize FieldA to create groupings, and while I can see the FieldB values, I want to then run a query to only show groups where the distinct count of FieldBs is greater than 1. Reading the docs it appears I can run a subquery, but I cannot get it to work. I either get syntax error, or the scope of the count is beyond the grouping The below example yields a distinct count beyond the scope of the group created by the transactionize. "GET /api/transaction/" | parse "GET /api/transaction/*?deviceid=* " as FieldA, FieldB |transactionize FieldA (merge FieldA, FieldB ) | count_distinct(FieldB)

0

• 

  Mario Sanchez

  • June 21, 2017 06:46
  • Edited

  David, I believe your issue might be that you do not need a merge for your query, so your subquery can be your count_distinct.

  Without knowing your data, so making some assumptions, I believe what you are looking for is:

  "GET /api/transaction/" 
  | parse "GET /api/transaction/*?deviceid=* " as FieldA, FieldB 
  |transactionize FieldA, FieldB (count_distinct(FieldB)) 

  Hope this helps!

  Cheers,

  Mario

  0

  Comment actions Permalink
• 

  david.kirby

  • May 10, 2017 18:10

  Thanks Mario, that worked! Just wondering how I can add fields from the log entries that are picked up by the query to the aggregates tab? ( I am new to sumo obviously)

  0

  Comment actions Permalink
• 

  david.kirby

  • May 10, 2017 18:18

  this is my current query: "GET /api/transaction/" | parse "GET /api/transaction/*?deviceid=* " as FieldA, FieldB |transactionize FieldA, FieldB (count_distinct(FieldB)) | where _count_distinct > 1 Which yields a nice little table on the Aggregates tab that looks something like this: # _count_distinct 1 2

  0

  Comment actions Permalink
• 

  david.kirby

  • May 10, 2017 19:46

  Got it displaying the way I wanted using a group by call, so: |transactionize FieldA, FieldB (count_distinct(FieldB) group by FieldA) | where _count_distinct > 1

  0

  Comment actions Permalink
• 

  david.kirby

  • May 10, 2017 19:46

  Thanks again @Mario S nchez

  0

  Comment actions Permalink

Please sign in to leave a comment.