@kj460 ,
I don't think you want to do this with the API, see API Call: User Page Views/Submissions within Date Range
For Canvas Data, you can JOIN tables together.
I just pieced this together from some of my samples. This will return:
- Course Name
- If the Request/URL is associated to an assignment or quiz will show the name
- If not, it attempts to parse the title of the wiki page from the URL
-- mssql
-- join requests to course, assignments, quiz and display the title...
-- WHEN the request is associated to those records
SELECT *
FROM (
SELECT
r.user_id,
CONVERT(DATETIME, CONVERT(VARCHAR(20), r.timestamp, 100)) timestamp,
r.url,
--r.course_id,
c.canvas_id AS canvas_course_id,
c.name AS course_name,
--r.quiz_id,
q.canvas_id AS canvas_quiz_id,
q.name AS quiz_name,
--r.assignment_id,
a.canvas_id AS canvas_assignment_id,
a.title AS assignment_name,
CASE
WHEN PATINDEX('/courses/%/pages/%', url) >= 1 AND PATINDEX('%?%', url) >= 1
THEN SUBSTRING(LEFT(url,CHARINDEX('?',url)), CHARINDEX('/pages/',url,0)+7,(CHARINDEX('/', REVERSE(url), 1)-CHARINDEX('?', REVERSE(url), 1))-1)
WHEN PATINDEX('/courses/%/pages/%', url) >= 1 AND PATINDEX('%?%', url) = 0
THEN SUBSTRING(url, CHARINDEX('/pages/',url,0)+7,1000)
WHEN PATINDEX('/courses/%/pages', url) >= 1
THEN 'pages-list'
ELSE ''
END AS wiki_url
FROM dbo.requests r
LEFT JOIN dbo.course_dim c ON (c.id = r.course_id)
LEFT JOIN dbo.quiz_dim q ON (q.id = r.quiz_id)
--LEFT JOIN dbo.discussion_dim d ON (d.id = r.discussion_id)
LEFT JOIN dbo.assignment_dim a ON (a.id = r.assignment_id)
WHERE
user_id IS NOT NULL -- AND user_id =
AND PATINDEX('%ping%',url) = 0
AND PATINDEX('%page_views%',url) = 0
AND PATINDEX('/api/v1/%',url) = 0
AND PATINDEX('%edit%',url) = 0
AND PATINDEX('%.json%',url) = 0
AND web_application_controller NOT IN ('files','folders')
AND web_application_action NOT IN ('backup')
) x
GROUP BY user_id, timestamp, url, canvas_course_id, course_name, canvas_quiz_id, quiz_name, canvas_assignment_id, assignment_name, wiki_url
ORDER BY timestamp
- You can continue to append the user_dim, enrollment_dim, etc.
- There will be duplicate-type rows caused by variations in similar URLs over a short time period, you can add more grouping or trimming things like query strings.
- If you want assignment/submission dates vs due dates, that should be done without the requests table.
I will warn you that every clause or join you create on the requests table starts to slow down your results. Also check out Requests Table, Canvas Data Page Views
