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?
-
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.
_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*", "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
Please sign in to leave a comment.
Comments
2 comments