none
Trying to get the count of unique values from customDimensions Json data RRS feed

  • Question

  • I am trying to get a count of the unique machineid's using our software.  The goal is to be able to report on the number of active clients in a day/week.  We have a customDimention Json entry that contains common.machineid but I am struggling to find the right query to get the results I seek.

    customEvents
    | where timestamp >= datetime(2017-08-16T15:40:20.453Z) and timestamp < datetime(2017-08-23T15:40:20.455Z)
    | where (itemType == 'customEvent' and (timestamp >= datetime(2017-08-16T15:40:20.454Z) and timestamp <= datetime(2017-08-23T15:40:20.454Z)))
    | where * has 'common.machineid'
    | top 101 by timestamp desc
    | extend status = customDimensions.common.machineid

    Data looks like this:

    {"common.machineId":"816376dffd9a29df5d0d218eb159488a0a09f1bdc3f545ed9f2d0384d7ca5f02","timestamp":"2017-08-23T01:18:28.2460000Z","common.nodeArch":"x64","common.nodePlatform":"darwin","common.platform":"Mac","common.osVersion":"16.7.0","version":"1.0.1","commitHash":"4dac403b94f8a34d058308b6310975ade20fb97e","sessionID":"a965c500-5044-42d3-b8e4-6763a69d19871503422190325"}



    • Edited by CodePilot.ai Wednesday, August 23, 2017 8:43 PM Oddness in the editor
    Wednesday, August 23, 2017 4:50 PM

Answers

  • We solved this with the following input:

    
    customEvents
    | where timestamp >= datetime(2017-08-16T20:15:59.999Z) and timestamp < datetime(2017-08-23T20:16:00.001Z)
    | project machineId = customDimensions.["common.machineId"]
    | summarize by tostring(machineId)

    | summarizecount()

    • Marked as answer by CodePilot.ai Wednesday, August 23, 2017 10:22 PM
    Wednesday, August 23, 2017 10:22 PM

All replies

  • We solved this with the following input:

    
    customEvents
    | where timestamp >= datetime(2017-08-16T20:15:59.999Z) and timestamp < datetime(2017-08-23T20:16:00.001Z)
    | project machineId = customDimensions.["common.machineId"]
    | summarize by tostring(machineId)

    | summarizecount()

    • Marked as answer by CodePilot.ai Wednesday, August 23, 2017 10:22 PM
    Wednesday, August 23, 2017 10:22 PM
  • You could use "dcount" (distinct count):

    |summarize dcount(tostring(machineId))

    Then you could also do 

    |summarize dcount(tostring(machineId)) by bin(timestamp, 1d)

    To get the daily distinct counts over time for example.





    • Edited by Assaf N Thursday, August 24, 2017 6:59 AM
    Thursday, August 24, 2017 6:59 AM