Replicating/Automating Built In Reports using Canvas Data 2

[dhenry636]

We created python scripts using CD2, and import data locally, and would like to create SQL views that are identical to some of the built in reports. 

One report, for instance, is the report named Students Access Report.  Is there any way to get an SQL query, or some design information, regarding how that report can be recreated with CD2 data?

Or is there some way to automate the download of reports without using CD2?

[stimme]

There are a couple of reports about access, and I am not sure which one you mean. There is one in the Account > Settings > Reports tab called "Last Access Report," and its help text says, "This report shows the last login for active users." This data point, I think, is in the canvas.pseudonyms.last_request_at field. It is possible for a user to have more than one login on the pseudonyms table, so grouping by user_id and selecting the maximum value for last_request_at would be advisable.

There is another access report for each course user that instructors can open from their People page. This is substantially more complex. I think that recreating this would involve analyzing the canvas_logs.web_logs data.

[dhenry636]

The report is named "Student Access Report".  It is pretty complex, I think, so I hope that we can automate downloading it directly like Colin said instead of having to recreate it using our own SQL!

[jerry_nguyen]

@dhenry636 

You can access the schema ERD here: https://data-access-platform-api.s3.eu-central-1.amazonaws.com/erd/index.html to design the SQL.

If you could share the column headings of the report, I can also help you with the SQL

[dhenry636]

The RTD Schema will be very helpful.  If we are able to automate downloading the report, though, like Colin said, we might not need to recreate the SQL.

In case we have trouble with auto downloading the report, I was wondering how the columns "content times viewed" and "times participated" are computed.  Do you think they use a Count for each student against the "content participations" table?  Or would it be hardcoded in the "content participations counts" table?

Student Access Report

root account id
content access id
section id
section sis id
section name
course id
course sis id
course name
term id term sis id
user id user sis id
content type
content times viewed
times participated
last viewed

[ColinMurtaugh · Accepted solution]

You can download account-level reports using the regular Canvas API. Docs are here: https://canvas.instructure.com/doc/api/account_reports.html

Note that just like in the UI, you have to initiate the generation of the report, wait for it to complete, and then download it. The API provides methods for all of these steps. 

This seems to me to be a far easier path than trying to recreate these reports using CD2 data.

--Colin

[dhenry636]

If we can find the "Student Access Report" this definitely deems like the best approach for this particular project.  Thank you!