Child pages
  • SSP Special Service Group Advisor Email Notification on Course Withdrawal
Skip to end of metadata
Go to start of metadata


The Special Service Group Notify Coach/Advisor on Student Course Withdrawal job was added in SSP 2.8. This job stores course history for students belonging to set special service groups and examines changes to transcript courses. If a withdrawal condition is detected, it will send each coach a summary email of all assigned students belonging to set special service groups that withdrew from a course or courses the previous day. Like most things in SSP, this job is configurable and could be used for other purposes as configuration allows. Please note that the first time this job runs or whenever a new student is added to SSP, existing withdrawal values in transcript courses will not trigger a notification because the job needs to record a baseline history and to eliminate extraneous notifications on withdrawals in the past.

Message Template

There is a new Message Template for this job in (Admin → System Configuration → Message Templates). It’s called “Special Service Group Course Withdrawal Email to Advisor”. Editing the template will alter the summary email for all coaches.

Scheduling Configuration

This job is designed to run in the background and will disrupt other users of SSP. It should always be run during off hours. By default, it’s set to run nightly at 1 a.m which can be changed using the configuration: “task_special_service_group_email_course_withdrawal_trigger” in Configuration Options (Admin → System Configuration → Configuration Options). In most cases, this should be adjusted to the same interval that the table “external_student_transcript_course” is updated or refreshed from SIS data. For example, say the external_student_transcript_course table was only updated weekly Friday at 11 p.m., then the job should be changed to execute on Friday after 12 a.m. or whenever the refresh is finished, but still during off hours. Executing the job more often will not do anything different, because emails are only sent when a withdrawal condition is detected between external_student_transcript_course and the internal history tables. There is one exception to this (adding external students) and that’s listed directly below. Please note this job could take a day or more to complete the first time as it records course history. It's advisable to enable this for the first time on a Friday before a weekend (assuming there are no end users using SSP during the weekend).

Adding External Student Configuration

By default, the job only processes internal students assigned to coaches that belong to set special service groups. However, another feature added in SSP 2.8 was the ability to specify special service groups from external data in the “external_student_special_service_group” table. If the configuration "special_service_group_email_course_withdrawal_add_student_to_ssp" in (Admin → System Configuration → Configuration Options) is set to true, external students with the set special service groups will be added to SSP when the job runs. This is because course history can only be stored for internal students. When external students are added they will be included in the coach summary email. Note, if this configuration is set to true, the job should be scheduled to run as frequently as "external_person" and "external_student_special_service_group" are updated. This is because new students that belong to a set special service group may be added more frequently than transcript course data.

Special Service Group Configuration

A new field was added to Special Service Groups in (Admin → Caseload Assignment → Special Service Groups) called "Notify on Withdrawal". The field must be switched to "Yes" for each group that should be considered during this job. By default, all Special Service Groups are switched to no which disables this job. Please note that the first time this job runs it can take a day or more to finish. It's recommended that anytime "Notify on Withdrawal" is switched to Yes, it's done before a weekend. Also note that that the first time this job runs for any Special Service Group just switched to Yes, it will not notify on any withdrawals found for that group, but rather record them as a baseline. This is because SSP cannot determine which semesters are most relevant and some withdrawals may have been in years past.

Withdrawal Configuration

A new configuration called "course_enrollment_status_code_changes" was added in (Admin → System Configuration → Configuration Options). This configuration specifies transitions for the data field "status_code" in the external_student_transcript_course table in external data. Each transition is comma separated and a "good" value is on the left and the "withdrawal" value is on the right separated by a pipe |. The default is: E|W, E|WF, E|TW. In that example, E means enrolled and when a status_code in external_student_transcript_course goes from "E to W" or "E to WF" or "E to TW", a notification will be generated. If the status_code goes from "E to F" for example, a notification would not be generated, unless that transition is added to the list. If this configuration is left incomplete or invalid, a log message will be recorded in the ssp.log file and the job will not be able to notify on withdrawal. Please note only the "status_code" field is considered for withdrawal. The grade field may be similar or the same at some institutions, but it's not the same at all and is not considered during this job.


By default only Errors and Warn statements are logged. To aid in troubleshooting or understanding which coaches and students the job is processing, the log level for this job can be set to Debug or Trace. Trace will have the most information and it's a good idea to set this level and analyze the logs during the first couple of runs. The Trace level will show which coaches and students were processed in addition to notifications on missing data which Debug also includes. The log level is adjusted by editing the logback.xml file in the ssp configuration directory. That directory is located wherever the SSP_CONFIGDIR environment variable points to. In the logback.xml file, add this line: 

<logger name="org.jasig.ssp.service.external.impl.SpecialServiceGroupCourseWithdrawalAdvisorEmailTaskImpl" level="trace" />

near the others, but before <root level="info">.  The changes take affect immediately although in rare cases Tomcat may need to be restarted. In the ssp.log file, once Debug or Trace is enabled, search for log statements with: "special-service-group-course-withdrawal".

Job Overview

  1. The SSG (Special Service Group) Notify on Withdraw job starts by retrieving the configuration "course_enrollment_status_code_changes" mentioned above. If that is null or empty, the job ends as course transitions can't be compared or recorded.

  2. Next, it retrieves all SSGs with "Notify on Withdrawal" set to yes. If there are none, the job ends because at least one Special Service Group must be set to yes.

  3. Finally, the job retrieves all coaches in SSP. It uses the same configuration for batch size and number of batches as the nightly person sync job. By default, it will batch by 100 and process until all batches are complete. If there are no coaches, the job ends because there must be coaches to notify.

  4. In those batches in #3 above, each coach is processed one by one. If the coach doesn't have an email address, the job will skip the coach because it can't send a summary email for that coach.

  5. If the coach has an email, the next step is to retrieve all assigned students for the coach.

    1. If the configuration "special_service_group_email_course_withdrawal_add_student_to_ssp" mentioned above is true, internal (students added to SSP) and external (students not yet added to SSP) assigned students will be retrieved otherwise only internal students will be retrieved.  If there are no students found, no withdrawals are possible and the coach is skipped.
  6. Once the assigned students are retrieved, it processes each student individually and retrieves all the transcript courses from the external_student_transcript_course table and all internal recorded course history from the person_course_history table. If no transcript courses are found, the student is skipped as the job can neither record history or check for a withdrawal.

  7. Once transcript courses and course history are retrieved, the job checks if history is recorded for each transcript course. If there isn't a record, it adds a new person_course_history record for that course and records the status_code along with formatted_course, term_code, and section_code fields from external_student_transcript_course table. If any of those fields are blank or null, course history can't be recorded as those define a unique transcript course. If the course history already exists, the job checks to see if status_code changed. If it hasn't, nothing is done as the course status has not changed. However, if the status_code is different, the job checks the change. If the change matches one of the transitions mentioned in "course_enrollment_status_code_changes", the student will be added to the coach's summary email. If the change does not match a transition, the changed value will be recorded, but the student will not be added to the summary email.

  8. After all assigned students are processed for a coach, the summary email is created before moving on to the next coach. The summary email contains all students who have at least one course that matches a configured withdrawal transition or if "special_service_group_email_course_withdrawal_add_student_to_ssp" configuration was turned on, it also includes external students that were added to SSP. Those added students should now be visible with an "Active" Program Status in the coach's caseload.


This job is extremely difficult to troubleshoot because it deals with multiple configurations, multiple parts of internal and external data and unlike other jobs in SSP, time. If something doesn't work, it's likely a timing/refresh, data, or configuration issue. Steps and queries are provided below to troubleshoot most scenarios encountered. It's also highly useful to make sure the nightly person sync occurs without error before digging deeper into troubleshooting this job.


Troubleshooting Using the ssp.log File

Using the log file is not necessary, but can make life easier if database access is hard to obtain. To start enable Trace logging level for this job in logback.xml (see Logging section above). After the job runs the next time (default 1 a.m.), check the ssp.log file for "special-service-group-course-withdrawal" log messages. A message may indicate what the issue is, below are a list of messages and work-flow to use the log for troubleshooting:

  1. "No special service groups to notify on withdrawal found":  Set at least one SSG with "Notify on Withdrawal" to Yes
  2. "Special Service Group Course Withdrawal Advisor Email Task will not execute because the property course_enrollment_status_code_changes is not set":  Make sure "course_enrollment_status_code_changes" is set in Configuration Options.

  3. If there are no messages with: "Processing SSG student withdrawals for coach: ", then there are no coaches in SSP or some other error is blocking retrieval of coaches.

  4. Similarly, if other coaches are processed, but a certain coach or student with coach school_id is not found in "Processing SSG student withdrawals for coach: ", then the coach doesn't exist in SSP due to likely a data issue with that coach's external_person record. Fix the coach's external_person record and try again.

  5. "No student withdrawals will be processed for coach: because the coach's email addresses are empty!": The coach with school_id mentioned in this message doesn't have an email address. Add an email address via a database query or to external_person so that all the students assigned with specified ssgs are processed.

  6. If the coach exists in the log, but you see: "No students were found that were assigned to coach", then no students were found assigned to that coach with the specified ssgs set. If the students are external and the configuration "special_service_group_email_course_withdrawal_add_student_to_ssp" is not turned on, those students won't be processed. Otherwise, if the students ssg assignments are in external_data, double check that the code field is the same between "external_student_special_service_group" and the internal Special Service Group configuration.

  7.  Now that we're this far, the student school_id should be found in the log in message such as: "Processing student: ", if it's not, the student isn't showing up as assigned to that coach and with the specified special service group. Double check the student's coach assignment and assigned special service groups (either in internal or external data).

  8. If the student is found, there's still several issues that can block them. First and most serious is: "Failed to retrieve or add internal person by school_id: ". This almost always should occur on an external student being added to SSP, it means the student's external_person record has an issue. If it's on an internal student, pull the student up in the UI and make sure they have all necessary data and save again to hopefully correct the internal person record.

  9. Also on a student, you should see: "Evaluating courses for student: ", if you don't see that, then there should be a message about no courses found. In that case, check external_student_transcript_course for the student's school_id. The school_id must match the student's id in external_person and the internal person tables.

  10. If you do see "Evaluating courses for student: ", and the student was not included on an email, check the following:

    1. Check the external_student_transcript_course table and make sure all student courses that may be withdrawn include section_code, formatted_course, term_code, and status_code. If any that should be considered are null or empty, that course's history won't be recorded.

    2. Also, make sure the course's status_code matches a passing value if it needs to be considered for withdrawal. It also may be a good idea to make sure withdrawal values are being updated into external_data from the SIS.

    3. Finally, if any of the section_code, formatted_course or term_code fields have changed recently from what SSP would have recorded for that course in the past, SSP will think it's different course and the course information won't be compared.

    4. If all of above check out, the reason the student didn't receive a withdrawal was due to timing. If the student was added to SSP with the withdrawal value OR if the student's Special Service Group was just switched to "Notify on Withdrawal" and they weren't part of other SSGs previously synced, then the student's withdrawal value was stored a baseline for that course as the student had no previous history. Similarly, if the course was just added to external_student_transcript_course with a withdrawn value it was also recorded with the withdrawn value as a baseline. In order for a course with a withdrawn value to notify, it first must be switched back to a non-withdrawn "good" value for at least one iteration of this job before it can be switched back to withdrawn.

Troubleshooting Using the Database

Accessing the database is usually necessary for troubleshooting this job. Using a query tool and a connection to the ssp database, the following work-flow should solve most issues encountered:


The most important queries are listed below. Once you know the job is running and processing students, these queries should show why a student didn't receive a withdrawal. Replace 'school_id_here' with the student's school_id in SSP.

1. SELECT formatted_course, term_code, section_code, status_code, previous_status_code FROM person_course_status WHERE person_id IN (SELECT id FROM person WHERE school_id = 'school_id_here'); --If previous_status_code contains something, the student was added to the summary email for coach

2. SELECT school_id, formatted_course, term_code, section_code, status_code FROM external_student_transcript_course WHERE school_id = 'school_id_here';

3. SELECT first_name, last_name, school_id, username, primary_email_address, coach_school_id, coach_first_name, coach_last_name FROM mv_directory_person WHERE school_id = 'school_id_here';




Here are a few less used queries (once everything is up and running), but are still needed in the troubleshooting process.

4. SELECT primary_email_address, secondary_email_address FROM person WHERE id = (SELECT coach_id FROM person WHERE school_id = 'school_id_here');

5. SELECT name, code FROM special_service_group WHERE notify_on_withdraw = true; --Will need to substitute 1 in place of true if using Sql Server

6. SELECT count(*) FROM person_special_service_group WHERE special_service_group_id IN (SELECT id FROM special_service_group WHERE notify_on_withdraw = true) AND person_id = (SELECT id FROM person WHERE school_id = 'school_id_here'); --Will need to substitute 1 in place of true if using Sql Server

7. SELECT count(*) FROM external_student_special_service_group WHERE code IN (SELECT code FROM special_service_group WHERE notify_on_withdraw = true) AND school_id = 'school_id_here';


  1. To start use query #1 above. If there are no courses found for a student, then no history was recorded.

  2. Next use query #2. If there are no courses for the student in external_student_transcript_course, then courses will need to be added as that's the issue.

  3. If courses exist, check that the status_code field is not null or empty, this is the field that needs to match a good value or a withdrawn value to be considered for withdrawal. If it's null or empty, that will block the course from being recorded. If it doesn't match a good or withdrawal value, it will take at least one job iteration to record a new value once external_student_transcript_course is updated. In addition, make sure that formatted_course, term_code, and section_code are also not null and not empty as that is the only way this job can identify unique courses. If any of those are null or empty, it will block the course from being saved. Similarly, if any of those fields are changed in external_student_transcript_course, this job considers the change to be a new course (see #4 below).

  4. If courses exist both in person_course_history and external_student_transcript_course for the student, examine query #1 and #2 to make sure the returned course information matches. If the formatted_course, section_code, or term_code have been changed, this job considers it a new course since those constitute unique course criteria per student.

  5. The last possible issue if a course matches between external_student_transcript_course and person_course_history, but the student wasn't included on the email, is timing. Even though there is a withdrawn value stored for status_code, it usually means the withdrawn value was recorded as a baseline. Either the student was added with the withdrawn value already set or the student's Special Service Group was switched to "Notify on Withdrawal" and the withdrawal was already set or the course was added to external_student_transcript_course with the withdrawal status_code set to a withdrawn value. If any of those conditions occur, the student by design will not be included on the summary email because SSP cannot determine withdrawal transition and in an effort to reduce notifications on withdrawals in past semesters.

  6. At this point, the issue was likely identified, however in certain cases there are other data issues blocking a student or coach from being processed. So, if after performing the steps above there aren't any courses in person_course_history, but there should be (they are valid courses assigned to the correct student), run query #3 above. If there is no coach in the result or if student data doesn't appear to be complete there may be sync issue with the student or coach or otherwise that data is missing. A student needs a coach assigned to be processed and both the student and coaches must have valid data in SSP's internal or external person tables.

  7. If the student looks correct and a coach is specified, run query #4 above. If there are no email addresses returned, that means the coach doesn't have any valid email addresses. The coach will need to be updated with a primary_email_address in order for their assigned students to be processed.

  8. If query #4 returns at least one valid email address, the coach should be processed. So, the next step is to make sure Special Service Group(s) exist with Notify on Withdrawal set to Yes. Run query #5 and if there is no result returned, set a Special Service Group to Yes for Notify on Withdrawal.

  9. If query #4 is good, run queries #6 and #7. If there are no returned results, then the student doesn't belong to a Special Service Group with "Notify on Withdrawal" set to Yes. Add the student to one of those groups. Note that after adding the student, any existing withdrawals won't generate a notification, however future withdrawals will be considered.


  • No labels