How do I get the XML of a certification?

[#1]

• How can I get a certification that has the same name as another?
• How can I tell which certification to get for a given individual?
• How to determine the ID of a certification for a given certifier?
• How to determine the ID of a certification for a given individual certified?

If you've spent anytime working with SailPoint Support, you know we like to get XML files for just about anything. For most objects, this activity is rather straightforward:

• Via the Debug page, select the object class from the drop down, input the name of the object in the associated text field, and hit the 'Edit' button.
• Via the IIQ Console, type: get 'className' 'objectName' > filename.xml

Certifications, on the other hand, are a tricky lot and fundamentally can use the simplified steps above, but quite often require a bit more.  This article is broken into two main topics:"How can I get a certification that has the same name as another?" and "How can I tell which certification to get for a given individual?"

How can I get a certification that has the same name as another?

We'll start with the easier sub-topic, which is what to do when certifications have the same name.  The certification generation task allows one to provide information that, in the end, would yield certifications that are identically named.  This poses a problem when you must get the XML for only one certification.  Using either the debug pages or the iiq console as desccribed above will result in an error message of, "More than one object with the same name was found".  When this happens, you must rely on the id of the certification you want to get.  All objects in IdentityIQ have an associated globally unique ID that descretely identifies a given object.  So as a fall-back for when the name is duplicated (an allowable situation), you must use the ID instead of the name:

• Via the Debug page, select the object class from the drop down, input the ID of the object in the associated text field, and hit the 'Edit' button.
• Via the IIQ Console, type: get 'className' 'ID' > filename.xml

The real trick with certifications, then, is how to get the ID.  To get this, you will need to execute a little SQL.  If you have a preferred database client connected to the IdentityIQ repository, you should use that.  If not, the IIQ console provides the 'sql' command that you can use to pass in direct queries to the IIQ repository.  An example use of this command:  sql "select id, name from spt_certification where name = 'Q1 Managers Cert'".  Note: a semi-colon(;) is not neccessary as an end of line delimiter for the SQL statement.

CAUTION: While the following queries are read-only operations, one must always be aware of what they are doing when sending SQL commands directly to the database.  Directly modifying the data with 'Insert', 'update', 'delete', or any other data modification SQL commands may result in unexpected behavior for IdentityIQ!

Finding out the ID of the certifications is rather easy, run a query that returns the ID of the certifications:

select id from spt_certification where name = 'Name of Certification'

However, it's quite often the case you'll need 'hints' as to which certification you're actually looking for.  Consider including contextual information, like the create date:

select id, created from spt_certification where name = 'Name of Certification' order by 'created' desc

How can I tell which certification to get for a given individual?

There are two typical categories a person falls into when it comes to certifications.  They are either the certifier, or an individual being certified.

How to determine the ID of a certification for a given certifier?

To do this, you must join the Certifiers table to the Certification table.  The Certifiers table (spt_certifiers) holds three columns: certification_id, certifiers, idx.  'certifiation_id' references the ID of the certification in question.  'certifier' represents the Identity name of the certifier.  Note: this is the name of the Identity object, which isn't necessarily the certifier's human addressable name.  Ala, if your Identity objects are named based on an employee number, expect to see something like 'tk149779' vs. 'Trey Kirk'.  So, to determine the ID of a certification given the certifier and a name of the certification:

select

spt_certificiation.id as certId,

spt_certification.name as certName,

spt_certification.created as certCreated

from spt_certification

join spt_certifiers on spt_certification.id = spt_certifiers.certification_id

where

spt_certification.name = 'Name of certification'

and

spt_certifiers.certifier = 'Identity name of certifier'

order by spt_certification.created desc

Should you get multiple rows returned, you should use the 'created' value to further isolate the desired certification.

How to determine the ID of a certification for a given individual certified?

Here we have the name of the Identity of an individual being certified (again, name of the Identity object, not the human name of the person).  Here we must join the CertificationEntity table with the Certification table:

select

spt_certification.id as certId,

spt_certification.name as certName,

spt_certification.created as certCreated

from spt_certification

join spt_certification_entity on spt_certification.id = spt_certification_entity.certification_id

where

spt_certification.name = 'Name of certification'

and

spt_certification_entity.identity_id = 'Identity name of certified individual'

order by spt_certification.created desc

Finally, suppose you have both the certifier, the certified, and the name of the cert:

select

spt_certification.id as certId,

spt_certification.name as certName,

spt_certification.created as certCreated

from spt_certification

join spt_certification_entity on spt_certification.id = spt_certification_entity.certification_id

join spt_certifiers on spt_certification.id = spt_certifiers.certification_id

where

spt_certification.name = 'Name of certification'

and

spt_certification_entity.identity_id = 'Identity name of certified individual'

and

spt_certifiers.certifier = 'Identity name of certifier'

order by spt_certification.created desc