Return to Answer

cleaned up query

Source Link

edited Sep 1, 2021 at 16:57

34.7k
72
488
686

let startDate = ago(31d);
let totalCount = toscalar(pageViews | where timestamp > startDate | count);
pageViews
 | where timestamp > startDate
 | project
    Browser = case(client_Browser contains "Firefox", "Firefox",
                    client_Browser contains "Safari", "Safari",
                    client_Browser contains "Chrome", "Chrome",
                    client_Browser contains "Samsung", "Samsung",
                    client_Browser contains "Edg", "Edge",
                    client_Browser contains "Opera", "Opera",
                    client_Browser contains "Internet Explorer", "Internet Explorer",
                    client_Browser contains "Silk", "Amazon Silk",
                    client_Browser contains "Facebook", "Facebook",
                    client_Browser contains "Instagram", "Instagram",
                    client_Browser contains "Apple Mail", "Apple Mail",
                    client_Browser contains "Android WebKit", "Android WebKit",
                    "Other"),
   OS = case(client_OS contains "iOS", "iOS",
                    client_OS contains "Android", "Android",
                    client_OS contains "Mac", "Mac",
                    client_OS contains "Windows", "Windows",
                    client_OS contains "Linux", "Linux",
                    client_OS contains "Chrome OS", "Chrome OS",
                    client_OS contains "Firefox OS", "Firefox OS",
                    "Other")
 | summarize Freq = count() by OS, Browser
 | extend Percent = round(100.0 * Freq / totalCount, 2)
 | order by Freq desc

// scalars
let startDate = ago(31d);
let totalCount = toscalar(pageViews | where timestamp > startDate | count);
// query
pageViews
| where timestamp > startDate
| project
    Browser = case(
        client_Browser has "Firefox", "Firefox",
        client_Browser has "Safari", "Safari",
        client_Browser has "Chrome", "Chrome",
        client_Browser has "Samsung", "Samsung",
        client_Browser has "Edg", "Edge",
        client_Browser has "Opera", "Opera",
        client_Browser has "Internet Explorer", "Internet Explorer",
        client_Browser has "Silk", "Amazon Silk",
        client_Browser has "Facebook", "Facebook",
        client_Browser has "Instagram", "Instagram",
        client_Browser has "Apple Mail", "Apple Mail",
        client_Browser has "Android WebKit", "Android WebKit",
        "Other"),
   OS = case(
        client_OS has "iOS", "iOS",
        client_OS has "Android", "Android",
        client_OS has "Mac", "Mac",
        client_OS has "Windows", "Windows",
        client_OS has "Linux", "Linux",
        client_OS has "Chrome OS", "Chrome OS",
        client_OS has "Firefox OS", "Firefox OS",
        "Other")
| summarize Freq = count() by OS, Browser
| project OS, Browser, Freq, Percent = round(100.0 * Freq / totalCount, 2)
| order by Freq desc

let startDate = ago(31d);
let totalCount = toscalar(pageViews | where timestamp > startDate | count);
pageViews
 | where timestamp > startDate
 | project
    Browser = case(client_Browser contains "Firefox", "Firefox",
                    client_Browser contains "Safari", "Safari",
                    client_Browser contains "Chrome", "Chrome",
                    client_Browser contains "Samsung", "Samsung",
                    client_Browser contains "Edg", "Edge",
                    client_Browser contains "Opera", "Opera",
                    client_Browser contains "Internet Explorer", "Internet Explorer",
                    client_Browser contains "Silk", "Amazon Silk",
                    client_Browser contains "Facebook", "Facebook",
                    client_Browser contains "Instagram", "Instagram",
                    client_Browser contains "Apple Mail", "Apple Mail",
                    client_Browser contains "Android WebKit", "Android WebKit",
                    "Other"),
   OS = case(client_OS contains "iOS", "iOS",
                    client_OS contains "Android", "Android",
                    client_OS contains "Mac", "Mac",
                    client_OS contains "Windows", "Windows",
                    client_OS contains "Linux", "Linux",
                    client_OS contains "Chrome OS", "Chrome OS",
                    client_OS contains "Firefox OS", "Firefox OS",
                    "Other")
 | summarize Freq = count() by OS, Browser
 | extend Percent = round(100.0 * Freq / totalCount, 2)
 | order by Freq desc

// scalars
let startDate = ago(31d);
let totalCount = toscalar(pageViews | where timestamp > startDate | count);
// query
pageViews
| where timestamp > startDate
| project
    Browser = case(
        client_Browser has "Firefox", "Firefox",
        client_Browser has "Safari", "Safari",
        client_Browser has "Chrome", "Chrome",
        client_Browser has "Samsung", "Samsung",
        client_Browser has "Edg", "Edge",
        client_Browser has "Opera", "Opera",
        client_Browser has "Internet Explorer", "Internet Explorer",
        client_Browser has "Silk", "Amazon Silk",
        client_Browser has "Facebook", "Facebook",
        client_Browser has "Instagram", "Instagram",
        client_Browser has "Apple Mail", "Apple Mail",
        client_Browser has "Android WebKit", "Android WebKit",
        "Other"),
   OS = case(
        client_OS has "iOS", "iOS",
        client_OS has "Android", "Android",
        client_OS has "Mac", "Mac",
        client_OS has "Windows", "Windows",
        client_OS has "Linux", "Linux",
        client_OS has "Chrome OS", "Chrome OS",
        client_OS has "Firefox OS", "Firefox OS",
        "Other")
| summarize Freq = count() by OS, Browser
| project OS, Browser, Freq, Percent = round(100.0 * Freq / totalCount, 2)
| order by Freq desc

Source Link

created May 24, 2021 at 13:00

KyleMit ♦

34.7k
72
488
686

You can get a lot of noise due to minor OS and browser variations. Here's a query that pulls the same data, but also simplifies the values so they can be grouped by more easily

let startDate = ago(31d);
let totalCount = toscalar(pageViews | where timestamp > startDate | count);
pageViews
 | where timestamp > startDate
 | project
    Browser = case(client_Browser contains "Firefox", "Firefox",
                    client_Browser contains "Safari", "Safari",
                    client_Browser contains "Chrome", "Chrome",
                    client_Browser contains "Samsung", "Samsung",
                    client_Browser contains "Edg", "Edge",
                    client_Browser contains "Opera", "Opera",
                    client_Browser contains "Internet Explorer", "Internet Explorer",
                    client_Browser contains "Silk", "Amazon Silk",
                    client_Browser contains "Facebook", "Facebook",
                    client_Browser contains "Instagram", "Instagram",
                    client_Browser contains "Apple Mail", "Apple Mail",
                    client_Browser contains "Android WebKit", "Android WebKit",
                    "Other"),
   OS = case(client_OS contains "iOS", "iOS",
                    client_OS contains "Android", "Android",
                    client_OS contains "Mac", "Mac",
                    client_OS contains "Windows", "Windows",
                    client_OS contains "Linux", "Linux",
                    client_OS contains "Chrome OS", "Chrome OS",
                    client_OS contains "Firefox OS", "Firefox OS",
                    "Other")
 | summarize Freq = count() by OS, Browser
 | extend Percent = round(100.0 * Freq / totalCount, 2)
 | order by Freq desc

Collectives™ on Stack Overflow

Return to Answer