query to sum up usage by OS by unique visitors



  • Avatar
    Frank Reno

    Hey Thomas:

    Give this a whirl and see how it goes.   Basically, I collapsed the if logic into a single column that tells you what type of OS it is.  Next, I did a count by _timeslice, the id of the visitor and the os type to get one row per user, per day, per OS they logged in with.  Then to get the distinct count, I just re-aggregated removing the visitor id.

    | json field=_raw "os" as os_raw_value
    | json field=_raw "chromeVersion" as chrome_version
    | json field=_raw "uniqueVisitorId" as uniqueVisitorId
    | timeslice 1d
    | if (os_raw_value matches "X11;CrOS*", "Chrome", if (os_raw_value matches "Macintosh*", "Mac", if (os_raw_value matches "Windows NT 6.1*", "Windows 7", if (os_raw_value matches "Windows NT 6.2*", "Windows 8", if (os_raw_value matches "Windows NT 6.3*", "Windows 8", if (os_raw_value matches "Windows NT 10*", "Windows 10", "Other")))))) as os_type
    | count by _timeslice, uniqueVisitorId, os_type
    | count by _timeslice, os_type

    Comment actions Permalink
  • Avatar
    Tom Daggett

    thanks Frank, that works perfectly.

    And now I understand how to use the if statement better -- cool!

    Comment actions Permalink

Please sign in to leave a comment.