Deploy: Institution Architectures
Code Hugger (Matthew Jones) edited this page Dec 22, 2022
·
21 revisions
These are queries Michigan uses for Lecture Capture (A locally tool) and MiVideo (Kaltura) that should be put in the RESOURCE_ACCESS_CONFIG.
You can create your own queries for your own Caliper events. You need to identify
resource_type, resource_id, user_id, user_login_name, course_id, name and access_time similar to these queries. Anything that generates a caliper event that the resource was accessed could potentially be used as a view here. Those are typically events like NavigatedTo and Started.
When you add something here, you also need to add it into the RESOURCES_VALUES object in the appropriate type. The RESOURCE_VALUES for this would look like:
"RESOURCE_VALUES": {
"files": {"types": ["canvas"], "icon": "fas fa-file fa-lg"},
"pages": {"types": ["canvas_pages"],"icon": "fas fa-file-code fa-lg"},
"videos": {"types": ["leccap", "mivideo"], "icon": "fas fa-video fa-lg"}
},
And this would be the query: (Note values in his like umich.instructure.com need to get changed to match your institution domains.
# Institute specific urls add or remove to your needs, will be used for Resources view
# cron queries to pull the data from various caliper data sources replace if not relevant with your institution
# More sample queries are provided in the MyLA GitHub wiki page: https://github.com/tl-its-umich-edu/my-learning-analytics/wiki/Deploy:-Institution-Architectures
"RESOURCE_ACCESS_CONFIG": {
"canvas": {
"app_display_name": "Canvas Files",
"canvas_course_id_format": "LONG",
"urls": {"prefix": "https://umich.instructure.com/files/" , "postfix": "/download?download_frd=1"},
# BigQuery event queries
"query":
'''
SELECT 'canvas' AS resource_type,
REGEXP_EXTRACT(object.id, r'.*:(.*)') AS resource_id,
CAST(REGEXP_EXTRACT(membership.id, r'.*:(.*)') AS INT64) AS user_id,
cast(null as string) AS user_login_name,
CAST(REGEXP_EXTRACT(`group`.id, r'.*:(.*)') AS INT64) AS course_id,
COALESCE(
JSON_EXTRACT_SCALAR(object.extensions, '$[\'com.instructure.canvas\'][asset_name]'),
JSON_EXTRACT_SCALAR(object.extensions, '$[\'com.instructure.canvas\'][filename]'),
object.name,
'attachment'
) as name,
datetime(EVENT_TIME) as access_time
FROM event_store.expanded
where
JSON_EXTRACT_SCALAR(ed_app.json, '$.id') IN UNNEST(['http://m.canvas.umich.edu/' , 'http://umich.instructure.com/' ])
and type = 'NavigationEvent'
and STARTS_WITH(object.id, 'urn:instructure:canvas:attachment')
and action = 'NavigatedTo'
and membership.id is not null
and REGEXP_EXTRACT(`group`.id, r'.*:(.*)') IN UNNEST(@course_ids)
'''
},
"canvas_pages": {
"query":
'''
SELECT 'canvas_pages' AS resource_type,
CONCAT(IFNULL(SAFE_CAST(REGEXP_EXTRACT(`group`.id, r'.*:(.*)') AS INT64), @canvas_data_id_increment) - @canvas_data_id_increment,
'/pages/',
IFNULL(SAFE_CAST(JSON_EXTRACT_SCALAR(object.extensions, '$[\'com.instructure.canvas\'][entity_id]') as INT64), @canvas_data_id_increment) - @canvas_data_id_increment) as resource_id,
CAST(REGEXP_EXTRACT(membership.id, r'.*:(.*)') AS INT64) AS user_id,
cast(null as string) AS user_login_name,
CAST(REGEXP_EXTRACT(`group`.id, r'.*:(.*)') AS INT64) AS course_id,
JSON_EXTRACT_SCALAR(object.extensions, '$[\'com.instructure.canvas\'][asset_name]') as name,
datetime(EVENT_TIME) as access_time
FROM event_store.expanded
where
JSON_EXTRACT_SCALAR(ed_app.json, '$.id') IN UNNEST(['http://m.umich.instructure.com/', 'http://umich.instructure.com/' ])
and type = 'NavigationEvent'
and object.type = 'Page'
and action = 'NavigatedTo'
and membership.id is not null
-- filtering out Caliper events with '/api/v1/' substring in request_url field,
-- which duplicated the counterpart Caliper events with request_url of pattern 'https://<site_url>/courses/<course_id>/pages/<page_url>'
and not CONTAINS_SUBSTR(JSON_EXTRACT_SCALAR(extensions_json, '$[\'com.instructure.canvas\'][request_url]'), '/api/v1/')
and REGEXP_EXTRACT(`group`.id, r'.*:(.*)') IN UNNEST(@course_ids)
''',
"app_display_name": "Canvas Files",
"canvas_course_id_format": "LONG",
"urls": {
"prefix": "https://umich.instructure.com/courses",
"postfix": ""
}
},
"leccap": {
"query":
'''
select 'leccap' AS resource_type,
REGEXP_EXTRACT(object.id, r'([^/]+$)') AS resource_id,
@canvas_data_id_increment + CAST(JSON_EXTRACT_SCALAR(federated_session_json, '$.messageParameters.custom_canvas_user_id') AS INT64) AS user_id,
cast(null as string) AS user_login_name,
@canvas_data_id_increment + CAST(JSON_EXTRACT_SCALAR(federated_session_json, '$.messageParameters.custom_canvas_course_id') AS INT64) AS course_id,
object.name as name,
datetime(EVENT_TIME) as access_time
FROM event_store.expanded
where ed_app.id = 'https://leccap.engin.umich.edu/#applicationName=Lecture+Capture'
and type = 'MediaEvent' and action = 'Started'
and JSON_EXTRACT_SCALAR(federated_session_json, '$.messageParameters.custom_canvas_course_id') is not null
and JSON_EXTRACT_SCALAR(federated_session_json, '$.messageParameters.custom_canvas_course_id') IN UNNEST(@course_ids_short)
''',
"app_display_name": "Lecture Capture",
"/* mention if the caliper event data is providing long or short canvas id and change the query to store long id as in this case": "*/",
"canvas_course_id_format": "SHORT",
"urls": {
"prefix": "https://leccap.engin.umich.edu/leccap/player/r/" ,
"postfix": ""
}
},
"mivideo": {
"query":
'''
SELECT 'mivideo' AS resource_type,
replace(object.id, 'https://aakaf.mivideo.it.umich.edu/caliper/info/media/' , '') AS resource_id,
cast(-1 as INT64) AS user_id,
replace(
replace(actor.id, 'https://aakaf.mivideo.it.umich.edu/caliper/info/user/' , ''), '+', '@'
) AS user_login_name,
@canvas_data_id_increment + CAST(JSON_EXTRACT_SCALAR(object.extensions, '$.kaf:course_id') AS INT64) AS course_id,
object.name AS name,
datetime(EVENT_TIME) AS access_time
FROM event_store.expanded WHERE
ed_app.id = 'https://aakaf.mivideo.it.umich.edu/caliper/info/app/KafEdApp'
AND TYPE = 'MediaEvent'
AND action = 'Started'
AND JSON_EXTRACT_SCALAR(object.extensions, '$.kaf:course_id') IN UNNEST(@course_ids_short)
''',
"app_display_name": "MiVideo",
"/* mention if the caliper event data is providing long or short canvas id and change the query to store long id as in this case": "*/",
"canvas_course_id_format": "SHORT",
"urls": {"prefix": "https://aakaf.mivideo.it.umich.edu/caliper/info/media/" , "postfix": ""}
}
},