How to query XML data in SailPoint IdentityIQ database

XML is the primary format used by IdentityIQ to store and exchange data. It is essential to understand how to query XML data from IdentityIQ tables as this one skill could significantly expedite the process of extracting information from IdentityIQ and save you hours during troubleshooting or while performing data analysis.

In this article I’ll share how to query XML data from various IIQ database platforms (particularly MSSQL, Oracle, and MySql). I’ll assume that you have a good understanding of IdentityIQ’s data model and it’s XML data structure.

Before jumping into the technical details, let me share a hypothetical use case where this can come in handy.

Sample Use case:

You have an identity mapping rule configured such that the title attribute from your authoritative application (say Workday) is synchronized to title attribute in Active directory. You notice discrepancies between the data in IIQ and you want to perform some analysis to determine the extent of the discrepancies.

While there are several ways to approach this, one quick way is to write a query that’ll generate the title data from Workday account links and Active Directory account links for each user and then compare the data.

Writing the query in MSSQL

The below query will extract all records in your identityIQ instance where the title field from a user’s Workday account link doesn’t match the title field from the user’s Active directory account link:

SELECT wd.display_name WD_DisplayName, ad.display_name AD_DisplayName,
CAST(wd.Attributes AS XML).value('(/Attributes/Map/entry [@key="JOBTITLE"]/@value)[1]', 'VARCHAR(100)') as WD_Title,
CAST(ad.Attributes AS XML).value('(/Attributes/Map/entry [@key="title"]/@value)[1]', 'VARCHAR(100)') as AD_Title
FROM [identityiq].[identityiq].[spt_link] wd
join [identityiq].[identityiq].[spt_link] ad on (wd.identity_id=ad.identity_id)
where
wd.application='workday_application_id'
and ad.application='AD_application_id'
and CAST(wd.Attributes AS XML).value('(/Attributes/Map/entry [@key="JOBTITLE"]/@value)[1]', 'VARCHAR(100)') <> CAST(ad.Attributes AS XML).value('(/Attributes/Map/entry [@key="title"]/@value)[1]', 'VARCHAR(100)')

Lines 2 and 3 in the above query extract the value of an xml entry from the Attributes column of the link table. The below is an extract of what the Attributes column might look like:

<Attributes>
  <Map>
   <entry key="IIQDisabled">
      <value>
        <Boolean></Boolean>
      </value>
    </entry>    
    <entry key="title" value="Accountant"/>  
  </Map>
</Attributes>

Similar query can be written in Oracle and MySQL as well as below:

MYSQL

ExtractValue(Attributes, '/Attributes/Map/entry[@key="JOBTITLE"]/@value') AS WD_Title

Oracle

EXTRACTVALUE(XMLTYPE(Attributes), '/Attributes/Map/entry[@key="JOBTITLE"]/@value') AS WD_Title

I hope this has been helpful and comes in handy when next you’re troubleshooting.