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.

[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