How Do I Get The XML Of A Certification?

  • Document ID:7011105
  • Creation Date:03-Feb-2011
  • Modified Date:19-Oct-2012
    • Micro Focus Products:
      Access Governance

Resolution

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

  • 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 Access Governance Suite Console, type: get 'className' 'objectName' > filename.xml

Certifications, on the other hand, are a tricky lot and fundamentially 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 Access Governance Suite 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 Access Governance Suite 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 Access Governance Suite repository, you should use that.  If not, the Access Governance Suite console provides the 'sql' command that you can use to pass in direct queries to the Access Governance Suite 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 Access Governance Suite!

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

 

Note: for more information about how to interpret the java date stamps, see: https://community.sailpoint.com/wiki/howcaniinterpretthedatetimestamp

A more in-depth discussion on using contextual information to identify a certification is provided in the next sub-topics.

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 neccessarily 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 futher 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