Deploy: Institution Architectures

Michigan Architecture Diagram

Custom queries in cron job for video usage data within env.json:

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": ""}
        }
    },