Staging Certifications and Purging a Failed Generation of Certificaitons

  • 7011197
  • 12-Jul-2011
  • 19-Oct-2012

Resolution

Staging Certifications

Often, customers have requirements to "stage" access reviews before they are sent to their respective certifiers.  A future version of Access Governance Suite will support just such a feature natively.  Until that release, one can still effectively do this using the following:

When Access Reviews (aka Certifications) are generated, they are assigned to their designated certifier and an email is sent to that certifier to let them know they have a certification waiting for them.  The first step is to prevent certifiers from being able to access these certifications until they are ready:

1.      Bar access to the server while certifications are being generated and until they've been verified and deemed ready to be "released" to their certifiers.

Barring access is really the most effective step in preventing access to these certifications.  Thus, you'll need to schedule the certification generation task to occur during a period where access to the server is not required by the general population of users.

Next, we need to prevent the certifiers from being notified:

2.      Prevent email notifications from being sent.

This is done by modifying the iiq.properties file and setting the following attribute:

environment.requestSchedulerHosts=INVALIDHOSTNAME

Restart the application server once this change is made.  When this is done, all Request objects are held in the queue and are not processed.  Email notifications are a type of Request objects and thus are not sent out.

You're now ready to generate certifications.  Execute the intended certification generation task and allow it to run through to completion.  When it has completed, make any verification checks that are required of your environment.  At a minimum, you should ensure the certification generation task completed with zero errors.  Warnings may be OK and thus should be reviewed.  However, just one error means the certifications will have to be purged and regenerated.

When the certifications have been verified, they are ready to be "released".

3.      Enable access to the server and the request scheduler to allow pending email notifications to be sent.

This is done by simply undoing the previous steps and restarting the application servers.  Upon restart, the request scheduler will start up and being processing the email request objects.  Certifiers will now get notifications of awaiting certifications and be allowed access to complete them.

 

What I Need To Restart The Certification Generation Task?

The whole purpose for staging the certifications is to allow one to 'restart' the certification should something go wrong and keep from erroneously notifying certifiers of a certification that will now have to be pruned.  If an error occurs, a new certification generation must be run.  Before that can happen, the existing certifications and email notifications just created from the failed attempt must be pruned:

0.      Backup the database!

You are purging live data in this exercise and run the risk of purging the too much data.  Always be able to restore any data deleted by ensuring the data is first backed up.

1.      Prune the incomplete certifications.

As the certification generation task cannot "pick up" where it leaves off, it has to restart from the beginning.  That means if we need to re-run it, we will create duplicate certifications for any it successfully created prior to encountering a problem.   The following is one of several ways one may employ to purge certifications:

1.       Gather the ids of all certifications created from this task.  This task is likely the most difficult and varied as different tactics can be employed for different versions.  In most cases, it's often easiest to acquire this information via a SQL query.  Queries can be run natively against the database using a native database client or via the 'iiq console' using the 'sql' command.  (Usage of the console, the debug pages,  the 'sql command',  and SQL Query syntax is beyond the scope of this article)

 

The most effective way of locating these certifications is by using the associated Certification Definition id.  However, it's not always easy locating this object.  In 5.1 and later, this object will be in the database.  Browse to the Debug page and list all objects of type CertificationDefinition.  Many times, just browsing by name will clearly identify the definition in question.  Open the XML for the definition and locate the id attribute. Ex:

 

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE CertificationDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">

<CertificationDefinition created="1305906519080" id="4028fe81300e038f01300e181c28005f" name="Application Owner Certification [5/20/11 10:48 AM]">

  <Attributes>

    <Map>

      <entry key="activePeriodDurationAmount" value="1"/>

 

For earlier versions of Access Governance Suite, the definition may not be available.  In that situation, identifying the certifications by date will be necessary.  For situations where a definition is reused (this occurs when you have a reoccurring certification schedule), using the date in conjunction with the definition id is required.  So next, determine the java date stamp for the date in which the certifications were generated.   There are several third party utilities that aid in this regard.  One such utility can be found here: http://www.epochconverter.com/.  This utility will convert a human-readable date into the Unix epoch time stamp.  The resulting value will output the number of seconds since January 1st, 1970 to the provided date (GMT).  Java uses this same value, but in milliseconds.  So, be sure to add three additional zeros to the provided value.

 

For example, the Unix epoch timestamp for 7/7/2011 is 1309996800.  The Java timestamp for 7/7/2011 is 1309996800000.  Note:  You can use the 'date' command in the iiq console to confirm your value:

 

> date 1309996800000

Wed Jul 06 19:00:00 CDT 2011  (this is correct as 1309996800000 is 7/7/2011 00:00:00 GMT, which is 5 hours ahead of CDT)

 

You now have enough information to build your query.  Use the following template for your query:

 

select id from spt_certification where

                certification_definition_id = '[DEFINITION_ID]'

and

                created > '[CREATED_TIME_STAMP]'

 

Examples (query syntax varies from platform to platform):

                Definition id: 4028fe81300e038f01300e181c28005f

                Create timestamp:  1309996800000

                SQL: select id from spt_certification where certification_definition =

                                '4028fe81300e038f01300e181c28005f' and created > '1309996800000'

 

(without a definition)

                Create timestamp: 1309996800000

                SQL: select id from spt_certification where created > '1309996800000'

 

Note:  If you know of additional information that can be applied to further narrow the scope of certifications, use that information in the above queries.  For example, if they were created using a naming standard, including a 'starts with' clause for the name column will further narrow down the certification IDs that need to be pruned.  The goal here is to prune ONLY the certifications that derived from the failed task.  Thus, the more restrictive your query is the better.

 

2.       For each ID captured in this manner, that is a certification that must now be cancelled.  In the Access Governance Suite Console, cancel each certification using the 'cancelCertify' command:

> cancelCertify 2c9081a530fab0f70130fab5c38e001d

Deleting Certification Role Membership Access Review for Louise Payne

> cancelCertify 2c9081a530fab0f70130fab5dae6006b

Deleting Certification Role Membership Access Review for Stephanie Coleman

Note: you'll likely have several IDs to cancel in this manner and manually cancelling each one is time consuming and tedious.  Batch them together via the following process:

-          Create a text file and insert the list of ids:

2c9081a530fab0f70130fab5d626005c

2c9081a530fab0f70130fab5d86d0062

2c9081a530fab0f70130fab5dae6006b

2c9081a530fab0f70130fab5dcb50071

2c9081a530fab0f70130fab5dfd5007d

2c9081a530fab0f70130fab5e33f008c

 

-          Prepend each line with 'cancelCertify ':

cancelCertify 2c9081a530fab0f70130fab5d626005c

cancelCertify 2c9081a530fab0f70130fab5d86d0062

cancelCertify 2c9081a530fab0f70130fab5dae6006b

cancelCertify 2c9081a530fab0f70130fab5dcb50071

cancelCertify 2c9081a530fab0f70130fab5dfd5007d

cancelCertify 2c9081a530fab0f70130fab5e33f008c

 

-          Save the text file.  In this example, we'll call it 'cancelCertify.txt'.  This is our batch file.

-          Via the iiq console, use the 'source' command to run our batch file: source /tmp/cancelCertify.txt

 

3.       Pending Request objects must also be pruned.  These are objects where being as judicious of what is pruned as we were for certifications is not always required.  That is, 99% of the Request objects in any given system represent Requests that have already been satisfied and no longer are required.  The remaining 1% exclusively represents the email notifications for the certification generation task you are pruning data for.   Thus, you have the option to simply delete all Requests via the iiq console:

> delete Request *

Note: a mass deletion of Request objects should only be done if you are absolutely sure that the only unprocessed Request objects are those representing notifications for the certification generation task that you are now purging data for.  If you would rather be more selective, use the same procedure as was done for the Certification pruning activity:

-          Gather the IDs for all Request objects created after the noted timestamp:

select id from spt_request where created > 1309996800000

 

-          For each ID, delete the objects individually via the iiq console:

delete Request [REQUEST_ID]

Note:  These commands should be batched together just as you did with the cancelCertify commands as they'll be equally numerous.

 

Once the Certifications have been cancelled and the Request objects pruned, the system is now cleared of the certification data that was generated during the erroneous generation task.  You may now restart a new generation task (after addressing the errors encountered).

 

Staging Certifications

Often, customers have requirements to "stage" access reviews before they are sent to their respective certifiers.  A future version of Access Governance Suite will support just such a feature natively.  Until that release, one can still effectively do this using the following:

When Access Reviews (aka Certifications) are generated, they are assigned to their designated certifier and an email is sent to that certifier to let them know they have a certification waiting for them.  The first step is to prevent certifiers from being able to access these certifications until they are ready:

1.      Bar access to the server while certifications are being generated and until they've been verified and deemed ready to be "released" to their certifiers.

Barring access is really the most effective step in preventing access to these certifications.  Thus, you'll need to schedule the certification generation task to occur during a period where access to the server is not required by the general population of users.

Next, we need to prevent the certifiers from being notified:

2.      Prevent email notifications from being sent.

This is done by modifying the iiq.properties file and setting the following attribute:

environment.requestSchedulerHosts=INVALIDHOSTNAME

Restart the application server once this change is made.  When this is done, all Request objects are held in the queue and are not processed.  Email notifications are a type of Request objects and thus are not sent out.

You're now ready to generate certifications.  Execute the intended certification generation task and allow it to run through to completion.  When it has completed, make any verification checks that are required of your environment.  At a minimum, you should ensure the certification generation task completed with zero errors.  Warnings may be OK and thus should be reviewed.  However, just one error means the certifications will have to be purged and regenerated.

When the certifications have been verified, they are ready to be "released".

3.      Enable access to the server and the request scheduler to allow pending email notifications to be sent.

This is done by simply undoing the previous steps and restarting the application servers.  Upon restart, the request scheduler will start up and being processing the email request objects.  Certifiers will now get notifications of awaiting certifications and be allowed access to complete them.

 

What I Need To Restart The Certification Generation Task?

The whole purpose for staging the certifications is to allow one to 'restart' the certification should something go wrong and keep from erroneously notifying certifiers of a certification that will now have to be pruned.  If an error occurs, a new certification generation must be run.  Before that can happen, the existing certifications and email notifications just created from the failed attempt must be pruned:

0.      Backup the database!

You are purging live data in this exercise and run the risk of purging the too much data.  Always be able to restore any data deleted by ensuring the data is first backed up.

1.      Prune the incomplete certifications.

As the certification generation task cannot "pick up" where it leaves off, it has to restart from the beginning.  That means if we need to re-run it, we will create duplicate certifications for any it successfully created prior to encountering a problem.   The following is one of several ways one may employ to purge certifications:

1.      Gather the ids of all certifications created from this task.  This task is likely the most difficult and varied as different tactics can be employed for different versions.  In most cases, it's often easiest to acquire this information via a SQL query.  Queries can be run natively against the database using a native database client or via the 'iiq console' using the 'sql' command. (Usage of the console, the debug pages,  the 'sql command',  and SQL Query syntax is beyond the scope of this article)

 

The most effective way of locating these certifications is by using the associated Certification Definition id.  However, it's not always easy locating this object.  In 5.1 and later, this object will be in the database.  Browse to the Debug page and list all objects of type CertificationDefinition.  Many times, just browsing by name will clearly identify the definition in question.  Open the XML for the definition and locate the id attribute. Ex:

 

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE CertificationDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">

<CertificationDefinition created="1305906519080" id="4028fe81300e038f01300e181c28005f" name="Application Owner Certification [5/20/11 10:48 AM]">

 <Attributes>

   <Map>

     <entry key="activePeriodDurationAmount" value="1"/>

 

For earlier versions of Access Governance Suite, the definition may not be available.  In that situation, identifying the certifications by date will be necessary.  For situations where a definition is reused (this occurs when you have a reoccurring certification schedule), using the date in conjunction with the definition id is required.  So next, determine the java date stamp for the date in which the certifications were generated.   There are several third party utilities that aid in this regard.  One such utility can be found here: http://www.epochconverter.com/.  This utility will convert a human-readable date into the Unix epoch time stamp.  The resulting value will output the number of seconds since January 1st, 1970 to the provided date (GMT).  Java uses this same value, but in milliseconds.  So, be sure to add three additional zeros to the provided value.

 

For example, the Unix epoch timestamp for 7/7/2011 is 1309996800.  The Java timestamp for 7/7/2011 is 1309996800000.  Note:  You can use the 'date' command in the iiq console to confirm your value:

 

> date 1309996800000

Wed Jul 06 19:00:00 CDT 2011 (this is correct as 1309996800000 is 7/7/2011 00:00:00 GMT, which is 5 hours ahead of CDT)

 

You now have enough information to build your query.  Use the following template for your query:

 

select id from spt_certification where

                certification_definition_id = '[DEFINITION_ID]'

and

                created > '[CREATED_TIME_STAMP]'

 

Examples (query syntax varies from platform to platform):

                Definition id: 4028fe81300e038f01300e181c28005f

                Create timestamp:  1309996800000

                SQL: select id from spt_certification where certification_definition =

                                '4028fe81300e038f01300e181c28005f' and created > '1309996800000'

 

(without a definition)

                Create timestamp: 1309996800000

                SQL: select id from spt_certification where created > '1309996800000'

 

Note:  If you know of additional information that can be applied to further narrow the scope of certifications, use that information in the above queries.  For example, if they were created using a naming standard, including a 'starts with' clause for the name column will further narrow down the certification IDs that need to be pruned.  The goal here is to prune ONLY the certifications that derived from the failed task.  Thus, the more restrictive your query is the better.

 

2.       For each ID captured in this manner, that is a certification that must now be cancelled.  In the Access Governance Suite Console, cancel each certification using the 'cancelCertify' command:

> cancelCertify 2c9081a530fab0f70130fab5c38e001d

Deleting Certification Role Membership Access Review for Louise Payne

> cancelCertify 2c9081a530fab0f70130fab5dae6006b

Deleting Certification Role Membership Access Review for Stephanie Coleman

Note: you'll likely have several IDs to cancel in this manner and manually cancelling each one is time consuming and tedious.  Batch them together via the following process:

-          Create a text file and insert the list of ids:

2c9081a530fab0f70130fab5d626005c

2c9081a530fab0f70130fab5d86d0062

2c9081a530fab0f70130fab5dae6006b

2c9081a530fab0f70130fab5dcb50071

2c9081a530fab0f70130fab5dfd5007d

2c9081a530fab0f70130fab5e33f008c

 

-          Prepend each line with 'cancelCertify ':

 

cancelCertify 2c9081a530fab0f70130fab5d626005c

cancelCertify 2c9081a530fab0f70130fab5d86d0062

cancelCertify 2c9081a530fab0f70130fab5dae6006b

cancelCertify 2c9081a530fab0f70130fab5dcb50071

cancelCertify 2c9081a530fab0f70130fab5dfd5007d

cancelCertify 2c9081a530fab0f70130fab5e33f008c

 

-          Save the text file.  In this example, we'll call it 'cancelCertify.txt'.  This is our batch file.

-          Via the iiq console, use the 'source' command to run our batch file: source /tmp/cancelCertify.txt

 

3.       Pending Request objects must also be pruned.  These are objects where being as judicious of what is pruned as we were for certifications is not always required.  That is, 99% of the Request objects in any given system represent Requests that have already been satisfied and no longer are required.  The remaining 1% exclusively represents the email notifications for the certification generation task you are pruning data for.  Thus, you have the option to simply delete all Requests via the iiq console:

> delete Request *

Note: a mass deletion of Request objects should only be done if you are absolutely sure that the only unprocessed Request objects are those representing notifications for the certification generation task that you are now purging data for. If you would rather be more selective, use the same procedure as was done for the Certification pruning activity:

-          Gather the IDs for all Request objects created after the noted timestamp:

select id from spt_request where created > 1309996800000

 

-          For each ID, delete the objects individually via the iiq console:

delete Request [REQUEST_ID]

Note:  These commands should be batched together just as you did with the cancelCertify commands as they'll be equally numerous.

 

Once the Certifications have been cancelled and the Request objects pruned, the system is now cleared of the certification data that was generated during the erroneous generation task.  

Staging Certifications

Often, customers have requirements to "stage" access reviews before they are sent to their respective certifiers.  A future version of Access Governance Suite will support just such a feature natively.  Until that release, one can still effectively do this using the following:

When Access Reviews (aka Certifications) are generated, they are assigned to their designated certifier and an email is sent to that certifier to let them know they have a certification waiting for them.  The first step is to prevent certifiers from being able to access these certifications until they are ready:

1.      Bar access to the server while certifications are being generated and until they've been verified and deemed ready to be "released" to their certifiers.

Barring access is really the most effective step in preventing access to these certifications.  Thus, you'll need to schedule the certification generation task to occur during a period where access to the server is not required by the general population of users.

Next, we need to prevent the certifiers from being notified:

2.      Prevent email notifications from being sent.

This is done by modifying the iiq.properties file and setting the following attribute:

environment.requestSchedulerHosts=INVALIDHOSTNAME

Restart the application server once this change is made.  When this is done, all Request objects are held in the queue and are not processed.  Email notifications are a type of Request objects and thus are not sent out.

You're now ready to generate certifications.  Execute the intended certification generation task and allow it to run through to completion.  When it has completed, make any verification checks that are required of your environment.  At a minimum, you should ensure the certification generation task completed with zero errors.  Warnings may be OK and thus should be reviewed.  However, just one error means the certifications will have to be purged and regenerated.

When the certifications have been verified, they are ready to be "released".

3.      Enable access to the server and the request scheduler to allow pending email notifications to be sent.

This is done by simply undoing the previous steps and restarting the application servers.  Upon restart, the request scheduler will start up and being processing the email request objects.  Certifiers will now get notifications of awaiting certifications and be allowed access to complete them.

 

What I Need To Restart The Certification Generation Task?

The whole purpose for staging the certifications is to allow one to 'restart' the certification should something go wrong and keep from erroneously notifying certifiers of a certification that will now have to be pruned.  If an error occurs, a new certification generation must be run.  Before that can happen, the existing certifications and email notifications just created from the failed attempt must be pruned:

0.      Backup the database!

You are purging live data in this exercise and run the risk of purging the too much data.  Always be able to restore any data deleted by ensuring the data is first backed up.

1.      Prune the incomplete certifications.

As the certification generation task cannot "pick up" where it leaves off, it has to restart from the beginning.  That means if we need to re-run it, we will create duplicate certifications for any it successfully created prior to encountering a problem.   The following is one of several ways one may employ to purge certifications:

1.       Gather the ids of all certifications created from this task.  This task is likely the most difficult and varied as different tactics can be employed for different versions.  In most cases, it's often easiest to acquire this information via a SQL query.  Queries can be run natively against the database using a native database client or via the 'iiq console' using the 'sql' command.  (Usage of the console, the debug pages,  the 'sql command',  and SQL Query syntax is beyond the scope of this article)

 

The most effective way of locating these certifications is by using the associated Certification Definition id.  However, it's not always easy locating this object.  In 5.1 and later, this object will be in the database.  Browse to the Debug page and list all objects of type CertificationDefinition.  Many times, just browsing by name will clearly identify the definition in question.  Open the XML for the definition and locate the id attribute. Ex:

 

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE CertificationDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">

<CertificationDefinition created="1305906519080" id="4028fe81300e038f01300e181c28005f" name="Application Owner Certification [5/20/11 10:48 AM]">

  <Attributes>

    <Map>

      <entry key="activePeriodDurationAmount" value="1"/>

 

For earlier versions of Access Governance Suite, the definition may not be available.  In that situation, identifying the certifications by date will be necessary.  For situations where a definition is reused (this occurs when you have a reoccurring certification schedule), using the date in conjunction with the definition id is required.  So next, determine the java date stamp for the date in which the certifications were generated.   There are several third party utilities that aid in this regard.  One such utility can be found here: http://www.epochconverter.com/.  This utility will convert a human-readable date into the Unix epoch time stamp.  The resulting value will output the number of seconds since January 1st, 1970 to the provided date (GMT).  Java uses this same value, but in milliseconds.  So, be sure to add three additional zeros to the provided value.

 

For example, the Unix epoch timestamp for 7/7/2011 is 1309996800.  The Java timestamp for 7/7/2011 is 1309996800000.  Note:  You can use the 'date' command in the iiq console to confirm your value:

 

> date 1309996800000

Wed Jul 06 19:00:00 CDT 2011  (this is correct as 1309996800000 is 7/7/2011 00:00:00 GMT, which is 5 hours ahead of CDT)

 

You now have enough information to build your query.  Use the following template for your query:

 

select id from spt_certification where

                certification_definition_id = '[DEFINITION_ID]'

and

                created > '[CREATED_TIME_STAMP]'

 

Examples (query syntax varies from platform to platform):

                Definition id: 4028fe81300e038f01300e181c28005f

                Create timestamp:  1309996800000

                SQL: select id from spt_certification where certification_definition =

                                '4028fe81300e038f01300e181c28005f' and created > '1309996800000'

 

(without a definition)

                Create timestamp: 1309996800000

                SQL: select id from spt_certification where created > '1309996800000'

 

Note:  If you know of additional information that can be applied to further narrow the scope of certifications, use that information in the above queries.  For example, if they were created using a naming standard, including a 'starts with' clause for the name column will further narrow down the certification IDs that need to be pruned.  The goal here is to prune ONLY the certifications that derived from the failed task.  Thus, the more restrictive your query is the better.

 

2.       For each ID captured in this manner, that is a certification that must now be cancelled.  In the Access Governance Suite Console, cancel each certification using the 'cancelCertify' command:

> cancelCertify 2c9081a530fab0f70130fab5c38e001d

Deleting Certification Role Membership Access Review for Louise Payne

> cancelCertify 2c9081a530fab0f70130fab5dae6006b

Deleting Certification Role Membership Access Review for Stephanie Coleman

Note: you'll likely have several IDs to cancel in this manner and manually cancelling each one is time consuming and tedious.  Batch them together via the following process:

-          Create a text file and insert the list of ids:

2c9081a530fab0f70130fab5d626005c

2c9081a530fab0f70130fab5d86d0062

2c9081a530fab0f70130fab5dae6006b

2c9081a530fab0f70130fab5dcb50071

2c9081a530fab0f70130fab5dfd5007d

2c9081a530fab0f70130fab5e33f008c

 

-          Prepend each line with 'cancelCertify ':

 

cancelCertify 2c9081a530fab0f70130fab5d626005c

cancelCertify 2c9081a530fab0f70130fab5d86d0062

cancelCertify 2c9081a530fab0f70130fab5dae6006b

cancelCertify 2c9081a530fab0f70130fab5dcb50071

cancelCertify 2c9081a530fab0f70130fab5dfd5007d

cancelCertify 2c9081a530fab0f70130fab5e33f008c

 

-          Save the text file.  In this example, we'll call it 'cancelCertify.txt'.  This is our batch file.

-          Via the iiq console, use the 'source' command to run our batch file: source /tmp/cancelCertify.txt

 

3.       Pending Request objects must also be pruned.  These are objects where being as judicious of what is pruned as we were for certifications is not always required.  That is, 99% of the Request objects in any given system represent Requests that have already been satisfied and no longer are required.  The remaining 1% exclusively represents the email notifications for the certification generation task you are pruning data for.   Thus, you have the option to simply delete all Requests via the iiq console:

> delete Request *

Note: a mass deletion of Request objects should only be done if you are absolutely sure that the only unprocessed Request objects are those representing notifications for the certification generation task that you are now purging data for.  If you would rather be more selective, use the same procedure as was done for the Certification pruning activity:

-          Gather the IDs for all Request objects created after the noted timestamp:

select id from spt_request where created > 1309996800000

 

-          For each ID, delete the objects individually via the iiq console:

delete Request [REQUEST_ID]

Note:  These commands should be batched together just as you did with the cancelCertify commands as they'll be equally numerous.

 

Once the Certifications have been cancelled and the Request objects pruned, the system is now cleared of the certification data that was generated during the erroneous generation task.  You may now restart a new generation task (after addressing the errors encountered).

 

You may now restart a new generation task (after addressing the errors encountered).