Audra,
That's part of my dilemma in pushing any of these queries to an actual solution. Without a full understanding of some of the requests, it spoils the water on any result. Like you said, most mobile traffic would be API calls, but so are some of the desktop requests.
Maybe we should try combining API calls with User Agent and ignore them from Desktop Browsers?
Here's a query I thought might help with understanding user roles viewing content areas of a course.
DECLARE @course_id BIGINT = 100000001234567;
SELECT
user_id,
enrollment_type,
ISNULL(analytics,0) analytics,
ISNULL(announcements,0) announcements,
ISNULL(assignments,0) assignments,
ISNULL(discussions,0) discussions,
ISNULL(enroll_users,0) enroll_users,
ISNULL(external_content,0) external_content,
ISNULL(external_tools,0) external_tools,
ISNULL(gradebook,0) gradebook,
ISNULL(grades,0) grades,
ISNULL(homepage,0) homepage,
ISNULL(modules,0) modules,
ISNULL(pages,0) pages,
ISNULL(quizzes,0) quizzes,
ISNULL(rubrics,0) rubrics,
ISNULL(settings,0) settings,
ISNULL(users,0) users
FROM (
SELECT user_id, enrollment_type, content_area, count(content_area) hits FROM (
SELECT user_id, enrollment_type, content_area FROM (
SELECT
requests.user_id,
ed.type AS enrollment_type,
requests.timestamp_day,
requests.session_id,
CASE
WHEN PATINDEX('/courses/%/analytics', url) >= 1 THEN 'analytics'
WHEN PATINDEX('/courses/%/announcements%', url) >= 1 THEN 'announcements'
WHEN PATINDEX('/courses/%/assignments%', url) >= 1 THEN 'assignments'
WHEN PATINDEX('/courses/%/conversations/%', url) >= 1 THEN 'conversations'
WHEN PATINDEX('/courses/%/discussion_topics%', url) >= 1 THEN 'discussions'
WHEN PATINDEX('/courses/%/enroll_users', url) >= 1 THEN 'enroll_users'
WHEN PATINDEX('/courses/%/external_content/%', url) >= 1 THEN 'external_tools_content'
WHEN PATINDEX('/courses/%/external_tools/%', url) >= 1 THEN 'external_tools_content'
WHEN PATINDEX('/courses/%/grades%', url) >= 1 THEN 'grades'
WHEN PATINDEX('/courses/%/gradebook%', url) >= 1 THEN 'gradebook'
WHEN PATINDEX('/courses/%/modules%', url) >= 1 THEN 'modules'
WHEN PATINDEX('/courses/%/pages%', url) >= 1 THEN 'pages'
WHEN PATINDEX('/courses/%/quizzes/%', url) >= 1 THEN 'quizzes'
WHEN PATINDEX('/courses/%/rubric_associations%', url) >= 1 THEN 'rubrics'
WHEN PATINDEX('/courses/%/settings%', url) >= 1 THEN 'settings'
WHEN PATINDEX('/courses/%/users%', url) >= 1 THEN 'users'
ELSE 'homepage'
END AS 'content_area'
FROM CanvasLMS.dbo.requests
INNER JOIN CanvasLMS.dbo.course_section_dim csd ON requests.course_id = csd.course_id
INNER JOIN CanvasLMS.dbo.enrollment_dim ed ON ed.course_section_id = csd.id AND requests.user_id = ed.user_id
WHERE requests.course_id = @course_id
AND requests.user_id IS NOT NULL
AND PATINDEX('/api/v1/%',url) = 0
AND web_application_controller NOT IN ('files','folders')
AND web_application_action NOT IN ('backup')
) y
GROUP BY user_id, enrollment_type, timestamp_day, session_id, content_area
)x
GROUP BY user_id, enrollment_type, content_area
) s
PIVOT
(
SUM(hits)
FOR content_area IN (
analytics,
announcements,
assignments,
discussions,
enroll_users,
external_content,
external_tools,
gradebook,
grades,
homepage,
modules,
pages,
quizzes,
rubrics,
settings,
users
)
) AS pvt
GO
Sample Result
user_id | enrollment_type | analytics | announcements | assignments | discussions | enroll_users | external_content | external_tools | gradebook | grades | homepage | modules | pages | quizzes | rubrics | settings | users |
1 | observer | 0 | 7 | 36 | 15 | 0 | 0 | 0 | 0 | 43 | 0 | 1 | 0 | 15 | 0 | 0 | 0 |
2 | observer | 0 | 3 | 23 | 4 | 0 | 0 | 0 | 0 | 39 | 31 | 0 | 0 | 7 | 0 | 0 | 0 |
3 | observer | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 7 | 0 | 0 | 0 | 0 | 0 | 0 |
4 | observer | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
5 | observer | 0 | 1 | 5 | 1 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | 3 | 0 | 0 | 0 |
6 | student | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 2 | 1 | 1 | 1 | 0 | 0 | 0 |
7 | student | 0 | 12 | 25 | 19 | 0 | 0 | 0 | 0 | 30 | 41 | 56 | 0 | 55 | 0 | 0 | 0 |
8 | student | 0 | 1 | 69 | 13 | 0 | 0 | 0 | 0 | 55 | 40 | 21 | 0 | 53 | 0 | 0 | 0 |
9 | student | 0 | 1 | 43 | 6 | 0 | 0 | 0 | 0 | 44 | 41 | 19 | 0 | 39 | 0 | 0 | 0 |
10 | student | 0 | 3 | 52 | 4 | 0 | 0 | 0 | 0 | 10 | 18 | 51 | 0 | 58 | 0 | 0 | 2 |
11 | student | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 4 | 3 | 0 | 0 | 0 | 0 | 0 |
12 | student | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 0 |
13 | student | 0 | 2 | 68 | 6 | 0 | 0 | 0 | 0 | 66 | 4 | 27 | 0 | 47 | 0 | 0 | 1 |
14 | student | 0 | 12 | 42 | 15 | 0 | 0 | 0 | 0 | 21 | 20 | 48 | 1 | 42 | 0 | 0 | 0 |
15 | student | 0 | 2 | 29 | 7 | 0 | 0 | 0 | 0 | 2 | 65 | 82 | 0 | 63 | 0 | 0 | 0 |
16 | student | 0 | 10 | 95 | 27 | 0 | 0 | 0 | 104 | 31 | 134 | 52 | 7 | 37 | 26 | 7 | 19 |
17 | student | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
18 | student | 0 | 19 | 38 | 24 | 0 | 0 | 0 | 0 | 47 | 33 | 39 | 0 | 36 | 0 | 0 | 0 |
19 | student | 0 | 1 | 38 | 6 | 0 | 0 | 0 | 0 | 41 | 41 | 18 | 0 | 36 | 0 | 0 | 0 |
20 | student | 0 | 4 | 8 | 4 | 0 | 0 | 0 | 0 | 10 | 10 | 6 | 3 | 5 | 0 | 0 | 1 |
21 | teacher | 0 | 10 | 95 | 27 | 0 | 0 | 0 | 104 | 31 | 134 | 52 | 7 | 37 | 26 | 7 | 19 |