Technology Office Query Practice

Updated Jul 13, 2021
Published 07/01/2019

As of July 12, 2021, this "Learn Veracross" site has been deprecated.  It will remain live through December 2022, but will no longer be updated. All knowledge content has moved to the new Veracross Community.  Please update your bookmarks.

Here is the new version of this article in the Veracross Community.

Guided Query Practice

Try answering these questions by reading the prompt and working following the steps to create a query that gets you the results you are looking for. After you’ve completed these exercises, or if you are stuck on any of them, feel free to navigate to the guided query answers below.

  1. “In preparation for the upcoming school year, can you build me a query of all future students and future parents who don’t yet have a user account created?”
    • start from a Find Person query on the System homepage
    • set the criteria for the Role field to be “is one of” and check off the Future Student and Parent of Future Student options
    • pull in the Username field and set the criteria to be “is blank”
  2. “To really solidify our data security, we want to enable Multi-factor Authentication for all of our users with Sys_Admin security roles. Can you build me a query for all people with Sys Admin security roles who aren’t enrolled in MFA?”
    • start from the “Find User Accounts” query on the System homepage
    • pull in the MFA Status field and set the criteria to be “is not one of” and check the Enrolled option
    • pull in the Security Role field and set the criteria to be “is one of” and check the Sys_Admin1 and Sys_Admin2 options
  3. “Some processes seem to be taking a long time. Could you go into the operations log and look up all operations that took 20 seconds or longer to complete?”
    • start from the Operations Log query on the System homepage
    • clear the criteria from the Start Time field
    • set the criteria of the Duration field to be “is greater than or equal to” 20
  4. “I’m trying to see where the nightly scripts have removed relationships. Could you find me all audit log records where the update user is vc.system and relationships are being deleted?”
    • start from the Find Audit Log Records query on the System homepage
    • set the criteria for the Update User field to be “begins with” vc.system
    • set the criteria for the Field Updated field to be “contains” relationship deleted
  5. “We’ve received a report that impersonation hasn’t been working for one of our users for the past couple of weeks. Would you be able to look up all times that impersonation has failed in the past two weeks for that user, and be sure to include the reason why?”
    • start from the Impersonations query on the Identity & Access Management homepage
    • set the criteria for the Login field to be “is” the username of the person in question
      (in this particular example, “@janderson” is used)
    • set the criteria for the Result field to be “is one of” and check the Login Failed option
    • ensure that the Notes field is pulled into the query design
  6. “I need a query of all duplicate people that are in a dupe group that begins with double zeroes.”
    • start from the Duplicate People query on the System homepage
    • adjust the criteria on the Dupe Group field to be “begins with” 00
  7. “Please build a query for me that shows all integrity issues deemed critical that are a part of the Demographic or User Accounts category. Also, if you could group them by category, I’d appreciate it.”
    • start from the Critical Integrity Issues query on the Data Integrity homepage
    • set the criteria for the Category field to be “is one of” and check off the User Accounts and Demographic options
    • subtotal by the Category field
  8. “Could you get me a list of all portal links for the student and faculty portal that are currently enabled? Please group them by which portal they’re in and sort them alphabetically by what the portal link is called.”
    • start from the Portal Links query on the System homepage
    • set the criteria for the Context field to be “is one of” and then check off the Student Portal Links and Faculty Portal Links options
    • set the criteria for the Status field to be “is one of” Enabled
    • subtotal by the Context field
    • set the sort key for the Description field to be 1 and and everything else to be greater than one (or remove the sorting altogether on the other fields)
  9. “Please build a query for me of all integrity checks that belong to the Internal category, group them by severity, and then sort them by how many instances there currently are of these integrity checks.”
    • start from the Integrity Checks query on the Data Integrity homepage
    • set the criteria for the Category field to be “is one of” and check the Internal option
    • subtotal by the Severity field
    • be sure to set the sorting of the # Results field to be lower than all other sort keys (signifying that it is more important), and that it is in descending order
  10. “Could you get me a list of all security roles that have a default username convention of “first initial.last name”?”
    • start from the Security Roles query on the System homepage
    • set the criteria for the Primary Username Convention field to be “is” and then type in {first_initial}.{last_name}

Independent Query Practice

Now that you’ve had some practice building queries, attempt to build the following queries on your own. After you’ve completed this, check your answers here.

  1. “Could you please bring up a list of all audit logged changes made to the person table by the user vc.system?”
  2. “Please build me a query to show which users currently have an MFA status of Enrolled or Required. If you could, please group them by security role and sort by their MFA status, then username. Thanks so much!”
  3. “I need a query showing me all failed Scheduler login attempts with the issue being that the user used the incorrect username or password.”
  4. “Could you get me all the admissions integrity issues where the issue is that the person has multiple applications for the same Year Applying For in 2013, sorted by their last name?”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Guided Query Answers

  1. “In preparation for the upcoming school year, can you build me a query of all future students and future parents who don’t yet have a user account created?”
    • start from a Find Person query on the System homepage
    • set the criteria for the Role field to be “is one of” and check off the Future Student and Parent of Future Student options
    • pull in the Username field and set the criteria to be “is blank”
    • https://axiom.veracross.com/#/query/398759
  2. “To really solidify our data security, we want to enable Multi-factor Authentication for all of our users with Sys_Admin security roles. Can you build me a query for all people with Sys Admin security roles who aren’t enrolled in MFA?”
    • start from the “Find User Accounts” query on the System homepage
    • pull in the MFA Status field and set the criteria to be “is not one of” and check the Enrolled option
    • pull in the Security Role field and set the criteria to be “is one of” and check the Sys_Admin1 and Sys_Admin2 options
    • https://axiom.veracross.com/#/query/398778
  3. “Some processes seem to be taking a long time. Could you go into the operations log and look up all operations that took 20 seconds or longer to complete?”
    • start from the Operations Log query on the System homepage
    • clear the criteria from the Start Time field
    • set the criteria of the Duration field to be “is greater than or equal to” 20
    • https://axiom.veracross.com/#/query/398783
  4. “I’m trying to see where the nightly scripts have removed relationships. Could you find me all audit log records where the update user is vc.system and relationships are being deleted?”
    • start from the Find Audit Log Records query on the System homepage
    • set the criteria for the Update User field to be “begins with” vc.system
    • set the criteria for the Field Updated field to be “contains” relationship deleted
    • https://axiom.veracross.com/#/query/398789
  5. “We’ve received a report that impersonation hasn’t been working for one of our users for the past couple of weeks. Would you be able to look up all times that impersonation has failed in the past two weeks for one of our users, and be sure to include the reason why?”
    • start from the Impersonations query on the Identity & Access Management homepage
    • set the criteria for the Login field to be “is” the username of the person in question
      (in this particular example, “@janderson” is used)
    • set the criteria for the Result field to be “is one of” and check the Login Failed option
    • ensure that the Notes field is pulled into the query design
    • https://axiom.veracross.com/#/query/398797
  6. “I need a query of all duplicate people that are in a dupe group that begins with double zeroes.”
  7. “Please build a query for me that shows all integrity issues deemed critical that are a part of the Demographic or User Accounts category. Also, if you could group them by category, I’d appreciate it.”
    • start from the Critical Integrity Issues query on the Data Integrity homepage
    • set the criteria for the Category field to be “is one of” and check off the User Accounts and Demographic options
    • subtotal by the Category field
    • https://axiom.veracross.com/#/query/398814
  8. “Could you get me a list of all portal links for the student and faculty portal that are currently enabled? Please group them by which portal they’re in and sort them alphabetically by what the portal link is called.”
    • start from the Portal Links query on the System homepage
    • set the criteria for the Context field to be “is one of” and then check off the Student Portal Links and Faculty Portal Links options
    • set the criteria for the Status field to be “is one of” Enabled
    • subtotal by the Context field
    • set the sort key for the Description field to be 1 and and everything else to be greater than one (or remove the sorting altogether on the other fields)
    • https://axiom.veracross.com/#/query/398819
  9. “Please build a query for me of all integrity checks that belong to the Internal category, group them by severity, and then sort them, high to low, by how many instances there currently are of these integrity checks.”
    • start from the Integrity Checks query on the Data Integrity homepage
    • set the criteria for the Category field to be “is one of” and check the Internal option
    • subtotal by the Severity field
    • be sure to set the sorting of the # Results field to be lower than all other sort keys (signifying that it is more important), and that it is in descending order
    • https://axiom.veracross.com/#/query/398840
  10. “Could you get me a list of all security roles that have a default username convention of “first initial.last name”?”
    • start from the Security Roles query on the System homepage
    • set the criteria for the Primary Username Convention field to be “is” and then type in {first_initial}.{last_name}
    • https://axiom.veracross.com/#/query/398843

Independent Query Answers

  1. “Could you please bring up a list of all audit logged changes made to the person table by the user vc.system?”
    • start from a Find Audit Log Records query on the System homepage
    • clear the criteria for the Update Date (Local) field
    • set the criteria for the Update User field to be “begins with” vc.system
    • set the criteria for the Table Name field to be “is one of” and check off the Person option
    • https://axiom.veracross.com/#/query/398847
  2. “Please build me a query to show which users currently have an MFA status of Enrolled or Required. If you could, please group them by security role and sort by their MFA status, then username. Thanks so much!”
    • start from a Find User Accounts query on the System homepage
    • pull in the MFA Status field and set the criteria to be “is one of” and check off the Enrolled and Required options
    • pull in the Security Role field and subtotal by it
    • clear all existing sorting
    • set the sorting for the Username field to be Ascending 2 and the sorting for the MFA Status to be descending 1
    • https://axiom.veracross.com/#/results/398857
  3. “I need a query showing me all failed Scheduler login attempts with the issue being that the user used the incorrect username or password.”
    • start from the Failed Logins query on the Identity & Access Management homepage
    • clear the criteria in the Login Time field
    • set the criteria for the Application field to be “begins with” Scheduler
    • set the criteria for the Notes field to be “begins with” invalid username or password
    • https://axiom.veracross.com/#/query/398863
  4. “Could you get me all the admissions integrity issues where the issue is that the person has multiple applications for the same Year Applying For in 2013, sorted by their last name?”
    • start from the Admissions Integrity Issues query on the Data Integrity homepage
    • set the criteria for the Description field to be “contains” people with multiple applications for the same YAF/GAF
    • set the criteria for the Additional Data 2 field to be 2013
    • clear all sorting and set the sorting for the Description field with the names in it to be Ascending 1
    • https://axiom.veracross.com/#/query/398874
Copyright © 2019 Veracross. All rights reserved.