query to sum up usage by OS by unique visitors

We have a custom chrome extension that is logging JSON similar to the following on every event

{
"uniqueVisitorId":"some_unique_guid",
"chromeVersion":"62.0.3202.94",
"os":"Windows NT 6.1; Win64; x64"
}

{
"uniqueVisitorId":"another_unique_guid",
"chromeVersion":"60.0.4545.12",
"os":"Macintosh; Intel Mac OS X 10_9_4"
}

{
"uniqueVisitorId":"still_another_unique_guid",
"chromeVersion":"62.0.3202.94",
"os":"Windows NT 10.0; Win64; x64"
}

{
"uniqueVisitorId":"a_4th_unique_guid",
"chromeVersion":"61.0.5555.22",
"os":"Windows NT 6.1; WOW64"
}

I'm currently trying to do two things:
- first I'm trying to get a count of the os usage by unique visitor (so for example if a given visitor logged three events from windows 7, I want to count 1 for Windows 7)
- also as an enhancement I'm trying to role up the os versions so that "Windows NT 6.1; WOW64" and "Windows NT 6.1; Win64; x64" get counted as 'Windows 7'

Here's the search I have so far:

_sourceCategory=prod/extension
| 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*", 1, 0) as Chrome_OS_Family_flag
| if (os_raw_value matches "Macintosh*", 1, 0) as Mac_Family_flag
| if (os_raw_value matches "Windows NT 6.1*", 1, 0) as Windows_7_Family_flag
| if (os_raw_value matches "Windows NT 6.2*", 1, 0) as Windows_8_Family_flag
| if (os_raw_value matches "Windows NT 6.3*", 1, 0) as Windows_8_Family_flag
| if (os_raw_value matches "Windows NT 10*", 1, 0) as Windows_10_Family_flag
| sum (Chrome_OS_Family_flag) as Chrome_OS_Family, sum (Mac_Family_flag) as Mac_Family, sum (Windows_7_Family_flag) as Windows_7_Family, sum (Windows_8_Family_flag) as Windows_8_Family, sum (Windows_10_Family_flag) as Windows_10_Family by _timeslice

this gives me the OS breakdown for all the events that get logged. but it doesn't give me the breakdown by unique visitor.

Any suggestions on how to accomplish this?

Frank Reno

December 07, 2017 14:55

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.

Tom Daggett

December 07, 2017 18:09

thanks Frank, that works perfectly.

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

query to sum up usage by OS by unique visitors

Comments

Didn't find what you were looking for?