Useful CourseGraph Queries!

Help The Community! Whenever you come up with a new query, add it here. Even if you think the use case is obscure.

Table of Contents

Orphans

All non-orphan problems in a course

All non-orphan problems in a course
MATCH (course)-[:PARENT_OF*]->(p:problem) WHERE p.course_key = <course_key> RETURN p

All root orphans in a course

All root orphans in a course
match (n:item) where NOT () -[:PARENT_OF]-> (n) AND n.course_key ="<course_key>" AND not n.detached AND NOT n.block_type='course' return n.location

Number of Non-orphan Items in the modulestore

Number of non-orphan blocks
MATCH (c:course) -[:PARENT_OF*]-> (n) RETURN count(distinct(c)) + count(distinct(n))

Note that this query isn't perfect. There are some "expected" orphans, like static tabs or about blocks. In a future version of neo4j, we'll include a "detached" parameter for these detached xblock classes.

Number of non-orphan blocks in each course

XBlocks

What XBlocks do we have? (total count)

XBlock counts

What xBlocks do we have? (across all courses, with counts by type)

This was used in 2016 and June 2020 for tracking / visualization.

XBlock counts

Which courses have a specific XBlock field enabled?

XBlock field matches

 

Where are all the XBlock types used in my course? (with direct link to where they are used)

 

What courses contain deprecated advanced modules? 

What courses contain specific advanced modules? 

What courses are using known third-party discussion forums?

Courses with creative-common licenses

Orgs with creative-commons courses, sorted

All creative-commons courses

Cohorts and differentiated content

Courses with cohorts

Courses with cohorts

This query is a little complex, and only works because the string representation of a user partition contains a string ( scheme=<class 'openedx.core.djangoapps.course_groups.partition_scheme.CohortPartitionScheme'> ) that we can use to determine if a user partition is a cohort. The list comprehension, 

gives a filtered list of the cohorts in a course. We use the size function to see if there's more than one of them in a course.

Which courses use group visibility settings (i.e. has differentiated content)?

Courses with cohorts

Courses with graded differentiated content

Problems

What types of "problem" XBlocks do we have?

XBlock field matches

All jsinput problems, counted by org

All jsinput problems, counted by org

Courses with new drag and drop XBlocks

new drag and drop problems by course

Courses with old drag and drop problems

old drag and drop problems by course

What courses use xqueue (code graders)?

We wanted to identify the open courses with code graders, and to find an example problem from each course so we could look up the queuename. Since code graders aren't a unique problem type, we looked for a phrase specific to code graders in the content of the problem.

 

What are the locations of code grader problems in a course? (Same as entry above but returns list of block + vertical locations that match

 

Where are a handful of modules that use MathJax?

Course Structure

Components that are direct children of subsections instead of units/verticals

Useful to find Components on old XML-authored courses that are children of the Subsection instead of the Unit (based on the Studio-enforced Course->Section->Subsection->Unit->Component hierarchy).

Assumes that the course(s) follows Course->Section->Subsection, though.

You can omit the c.course_key = "<course key>" and line if you want to query across all courses.

Subsections that contain word clouds and peer instruction

This query might be useful in when setting a bug priority - in this particular scenario, the two components would conflict when in the same subsection, but a query showed they were rarely used together.

Length of the longest Subsection title

Length of the longest Section title

Length of the longest Section title

How many pages contain nested XBlocks? 

Find and count the XBlocks (p) that contain other XBlocks ( n), that appear in units (v). For all these searches, you'll get more real results by adding  constraints like "vertical in a recent course" or "is published to learners" 

Nested XBlocks in Units

Note for the below that we're not eliminating cyclical relationships; no guarantee that n is a leaf node.

Units with Nested XBlocks

Useful toolkit for searching path depth:  

Searching path depth

Subsection settings from the Course Outline

These queries relate to settings that can be configured from the Course Outline via the gear icon next to a subsection.

What courses have used the "hide after due" feature?
How many active or upcoming courses use the "Limit Access" feature?

This refers to subsection prerequisites under the advanced tab.

How many gated subsections are there in active or upcoming courses using the “Limit Access” feature?

Order of content on a page ([ :PRECEDES] relationship)

Particular layouts of a page might be of interest - for instance: What courses interleave exercises and text on a page? Does any page start with a discussion prompt before offering more guidance on how to participate? -[:PRECEDES]-> will help search for a specific order of children.

Search for pages that contain graded problem-text-problem on a page.

Length of the longest Section title

 

Videos

Where are all the video components in a course?

What are the video titles and Video IDs in a course?


Where are all in-use videos located in my course? (with direct link)

What courses have not enabled video upload? 

Which courses allow video download?

Where are all my org's videos and what video source / URL are they being served from?

How many videos use start/end times (i.e. trim the video)?

Simple query:

Note that this is an over-estimate–many videos that have an explicit end_time actually specify that end time to be the same as what the normal end of the video would be anyway (so there is effectively no trimming happening). Unfortunately, data around total video duration lives in edx-val, and is not available from CourseGraph.

How many creative-commons licensed videos are there?

ORA

What courses have ORA with misconfigured dates?

For course teams, ORA due dates are set in a slightly different workflow than other assignments (in Unit as opposed to Subsection). On occasion, teams will forget to update ORA due dates in a course rerun, which blocks learners from accessing the assessment. 

Which courses use peer assessment ORAs?

ORAs can use a combination of peer-assessment, self-assessment, and staff-assessment.

What settings are used for file uploads in ORA?

ORAs can be configured to allow file uploads, allowing for a preset or set of custom file types.

Advanced settings

Which courses use teams?

Exams

Which courses have timed exams?

When are all the upcoming finals?

Which timed exams are upwards of 24 hours?

 

Proctored Exams

Which courses have practice proctored exams?

Which courses have official proctored exams?

Link and start date for the first proctored exam in a course

Which courses have currently available proctored exams?

What are the release and due dates of the proctored exams?

What is the proctoring backend in use by a course?

Course quality & integrity

What courses have randomized problem banks?

What are the locations for randomized content blocks in a course?

Which courses use discussion blackout dates?

Discussion forums

What courses don't use the built-in discussion forums?

Which courses have in-context discussion blocks? 

Course updates

Number of courses with visible updates, by pacing type

Note: we have to "join" on the course block, because course_info blocks aren't children of course blocks and so don't inherit the self_paced attribute from them.

LTI

Which courses have LTI components?

Where are graded LTI components - that are embedded in the course / that open an external link?

Which courses use X LTI? 

What are all the LTIs used on edX and in what volume are they being used?

Due dates

Which courses had assignments due between X and X dates?

This query might be useful in situations where learners are unable to access the platform for a period of time (e.g. platform outage, natural disaster) which affects their ability to submit key assignments.

Content: HTML and Problems

Which courses contain a certain keyword anywhere in their course content?

Example: we might be curious which courses mention the g2crowd tool anywhere in their HTML content.

Example: we want to know whether a course has links to content on other websites:

What courses contain scripts with python code?

Example: An org would like to know all their courses that require review for the python3 upgrade

Where all the python-graded problems?

Example: A partner needs guidance on what content to review for the python3 upgrade - give them links to all the content.

Which HTML modules have (illegal) self-closing <script> and <iframe> tags?

Example: We need to find which HTML modules and courses use <script /> or <iframe /> when they should use <script></script> or <iframe></iframe> instead. Using the self-closing version is invalid HTML, but worked because of some data munging we did in the old courseware view. It does not work in the new Courseware MFE.


Which HTML modules use X iframe in them and where?

Age of Course Data

When was the course last updated in Coursegraph?

This won't capture courses added since the last update, but this query can be used to see when Coursegraph itself was last updated, based on the last update date.


Constructing Specific URLs from Your Results

If you're looking for specific blocks and want to quickly generate the url you can go to to see the block in action in the LMS, add this statement to your return values:

In this case, ITEM would be the alias of the block you're looking for

Filtering a specific list

If you're looking for results in a specific list of items (courses), you can use the list syntax