Sailpoint Identity IQ SQL Query to get the Users Details for Identity Having Multiple Account in same Application

 SQL Query to get the Users Having multiple Account in Same Application 

Select count(*) ,
SPT_APPLICATION.NAME AS APPLICATION_NAME ,
SPT_IDENTITY.name AS IDENTITY_NAME , 
SPT_IDENTITY.ID AS IDENTITY_ID , 
SPT_APPLICATION.ID AS APPLICATION_ID
from SPT_LINK , SPT_IDENTITY , SPT_APPLICATION 
where 
SPT_IDENTITY.ID=SPT_LINK.IDENTITY_ID and
SPT_APPLICATION.ID=SPT_LINK.APPLICATION and 
SPT_IDENTITY.CORRELATED=1 and 
SPT_APPLICATION.NAME not in ('Active Directory Corp')
--and SPT_IDENTITY.NAME like 'VKEJRIWAL'
group by SPT_IDENTITY.name , SPT_APPLICATION.NAME  ,SPT_IDENTITY.ID , SPT_APPLICATION.ID
HAVING COUNT(*)>1
order by SPT_APPLICATION.NAME ,SPT_IDENTITY.name

Query to get the Status of above Account

SELECT SPT_LINK.NATIVE_IDENTITY ,VIS_TEMP.APPLICATION_NAME,EXP_TEMP.IDENTITY_NAME,
SPT_LINK.DISPLAY_NAME ,EXP_TEMP.IDENTITY_ID,EXP_TEMP.APPLICATION_ID,
EXTRACT(xmltype(ATTRIBUTES),'/Attributes/Map/entry[@key=''IIQDisabled'']/value') AS STATUS
from SPT_LINK , VIS_TEMP
where SPT_LINK.IDENTITY_ID=VIS_TEMP.IDENTITY_ID
and SPT_LINK.APPLICATION=VIS_TEMP.APPLICATION_ID
--and  EXP_TEMP.IDENTITY_NAME like 'VKEJRIWAL'

 SQL Query to get Entitlement tied to above Accounts 

Select 
VIS_TEMP.APPLICATION_NAME,
VIS_TEMP.IDENTITY_NAME,
SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY,
SPT_IDENTITY_ENTITLEMENT.DISPLAY_NAME,
VIS_TEMP.IDENTITY_ID ,
SPT_MANAGED_ATTRIBUTE.VALUE,

from SPT_IDENTITY_ENTITLEMENT,VIS_TEMP,SPT_MANAGED_ATTRIBUTE, SPT_APPLICATION
where
SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID=VIS_TEMP.IDENTITY_ID
and SPT_MANAGED_ATTRIBUTE.VALUE=SPT_IDENTITY_ENTITLEMENT.VALUE
and SPT_MANAGED_ATTRIBUTE.APPLICATION=SPT_IDENTITY_ENTITLEMENT.APPLICATION
and SPT_APPLICATION.ID=SPT_MANAGED_ATTRIBUTE.APPLICATION
and SPT_APPLICATION.ID=SPT_IDENTITY_ENTITLEMENT.APPLICATION
and SPT_APPLICATION.NAME=VIS_TEMP.APPLICATION_NAME

order by VIS_TEMP.IDENTITY_NAME,VIS_TEMP.APPLICATION_NAME

Sailpoint Identity IQ List of Entitlement with Sunrise and Sunset Mapped for Particular User using DB Query

SQL to get the list of Entitlement with sunrise and sunset date  in Sailpoint IIQ which is mapped for the particular User , This query will give the information such as the Application Name , Entitlement Name , Entitlement Value , Sunrise and Sunset date , identity , Account ID

Select SPT_IDENTITY.NAME AS "USER ID" ,SPT_APPLICATION.NAME AS "APPLICATION NAME", 
SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY AS "ACCOUNT ID", 
SPT_IDENTITY_ENTITLEMENT.VALUE AS "ENTITLEMENT VALUE",
(
TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_IDENTITY_ENTITLEMENT.END_DATE) / 1000 / 60 / 60 / 24
) AS "SUNSET DATE"
from SPT_IDENTITY_ENTITLEMENT , 
SPT_MANAGED_ATTRIBUTE , 
SPT_APPLICATION ,
SPT_IDENTITY 
where 
SPT_MANAGED_ATTRIBUTE.APPLICATION=SPT_IDENTITY_ENTITLEMENT.APPLICATION and
SPT_MANAGED_ATTRIBUTE.VALUE = SPT_IDENTITY_ENTITLEMENT.VALUE and 
SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID = SPT_IDENTITY.ID and
SPT_APPLICATION.ID=SPT_MANAGED_ATTRIBUTE.APPLICATION and
SPT_IDENTITY.CORRELATED ='1' and
SPT_IDENTITY_ENTITLEMENT.END_DATE is not null

SQL ACCESS REVIEW CERTIFICATION ACTION ITEMS DETAILS SAILPOINT IDENTITY IQ

This query is used to get the details about the Access Review , Certification which is generated and will give all the details 
for all the items for which the action is already taken.

SELECT SPT_CERTIFICATION.ID AS "CERT_ID",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.ACTIVATED) / 1000 / 60 / 60 / 24
   )
   AS "CREATE DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.EXPIRATION) / 1000 / 60 / 60 / 24
   )
   AS "EXPIRATION DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.SIGNED) / 1000 / 60 / 60 / 24
   )
   AS "SIGNED DATE",
  SPT_CERTIFICATION.SHORT_NAME AS "CERT_NAME",
  SPT_CERTIFICATION_ITEM.SUMMARY_STATUS AS "CERT_STATUS",
  SPT_CERTIFICATION.MANAGER AS "CERT_ASSIGNED_TO_ID",
  SPT_IDENTITY.EMAIL AS "CERT_ASSIGNED_TO_EMAIL",
  SPT_IDENTITY.DISPLAY_NAME AS "CERT_ASSIGNED_TO_NAME",
  
  user2.DISPLAY_NAME AS "MANAGER_DISPLAY_NAME",
  USER2.EMAIL AS "MANAGER_EMAIL",
  
  SPT_CERTIFICATION_ENTITY.TARGET_NAME AS "USER_ID",
  SPT_CERTIFICATION_ENTITY.TARGET_DISPLAY_NAME AS "USER_DISPLAY_NAME",
  SPT_CERTIFICATION_ENTITY.FIRSTNAME AS "USER_FIRST_NAME",
  SPT_CERTIFICATION_ENTITY.LASTNAME AS "USER_LAST_NAME",
  user1.EMAIL AS "USER_EMAIL",
  
  SPT_CERTIFICATION.TOTAL_ENTITIES,
  SPT_CERTIFICATION.EXCLUDED_ENTITIES,
  SPT_CERTIFICATION.COMPLETED_ENTITIES,
  SPT_CERTIFICATION.PERCENT_COMPLETE,
  SPT_CERTIFICATION.CERTIFIED_ENTITIES,
  SPT_CERTIFICATION.TOTAL_ITEMS,
  SPT_CERTIFICATION.EXCLUDED_ITEMS,
  SPT_CERTIFICATION.COMPLETED_ITEMS,
  SPT_CERTIFICATION.ITEM_PERCENT_COMPLETE,
  SPT_CERTIFICATION.CERTIFIED_ITEMS,
  SPT_CERTIFICATION.REMEDIATIONS_KICKED_OFF,
  SPT_CERTIFICATION.REMEDIATIONS_COMPLETED,
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.MODIFIED) / 1000 / 60 / 60 / 24
   )
   AS "CERT_UPDATE_DATE",
   
  SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION AS "ACCOUNT_APPLICATION_NAME",
  SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_NAME AS "RECORD_TYPE",
  SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY AS "ACCOUNT_NAME",
  SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE AS "ACCOUNT_ENTITLEMENT_NAME",
  SPT_CERTIFICATION_ACTION.STATUS AS "CERT_DECISION",
  SPT_CERTIFICATION_ACTION.DESCRIPTION AS "CERT_DECISION_COMMENTS",
  SPT_CERTIFICATION_ACTION.REMEDIATION_ACTION AS "REMEDIATION_ACTION",
  TO_CHAR(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS) AS "REMEDIATION_DETAILS",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION_ACTION.DECISION_DATE) / 1000 / 60 / 60 / 24
   )
   AS "CERT_DECISION_DATE",
  TO_CHAR(Regexp_substr(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS, 'requestID="(.*?)"\sstatus', 1, 1, NULL, 1)) AS TICKET,
  TO_CHAR(Regexp_substr(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS, 'status="(.*?)"\/', 1, 1, NULL, 1)) AS TICKET_STATUS,
  SPT_CERTIFICATION_ITEM.ACTION AS "ACTION"
FROM SPT_CERTIFICATION_ENTITY,
  SPT_CERTIFICATION,
  SPT_CERTIFICATION_ITEM,
  SPT_IDENTITY,
  SPT_CERTIFICATION_ACTION,
  SPT_IDENTITY user1,
  SPT_IDENTITY user2,
  SPT_IDENTITY_ENTITLEMENT,
  SPT_APPLICATION
WHERE SPT_CERTIFICATION_ENTITY.CERTIFICATION_ID = SPT_CERTIFICATION.ID
  AND SPT_CERTIFICATION_ITEM.CERTIFICATION_ENTITY_ID = SPT_CERTIFICATION_ENTITY.ID
  AND SPT_IDENTITY.NAME = SPT_CERTIFICATION.MANAGER
  AND SPT_CERTIFICATION_ACTION.ID = SPT_CERTIFICATION_ITEM.ACTION
  AND SPT_CERTIFICATION_ENTITY.TARGET_NAME = user1.NAME
  AND user1.MANAGER = user2.ID
  AND SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION = SPT_APPLICATION.NAME
  AND SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE = SPT_IDENTITY_ENTITLEMENT.VALUE
  AND user1.ID = SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID
  AND SPT_APPLICATION.ID = SPT_IDENTITY_ENTITLEMENT.APPLICATION

SQL IDENTITY ENTITLEMENT DETAILS SAILPOINT IDENTITY IQ

This will get list of all the Entitlement details for the particular user

Select  SPT_IDENTITY.NAME AS "USER ID" ,SPT_APPLICATION.NAME AS "APPLICATION NAME", 
        SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY AS "ACCOUNT ID", 
        SPT_IDENTITY_ENTITLEMENT.VALUE  AS "ENTITLEMENT VALUE"
        from SPT_IDENTITY_ENTITLEMENT , 
             SPT_MANAGED_ATTRIBUTE , 
             SPT_APPLICATION ,
             SPT_IDENTITY 
        where 
            SPT_MANAGED_ATTRIBUTE.APPLICATION=SPT_IDENTITY_ENTITLEMENT.APPLICATION and
            SPT_MANAGED_ATTRIBUTE.VALUE = SPT_IDENTITY_ENTITLEMENT.VALUE and 
            SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID = SPT_IDENTITY.ID and
            SPT_APPLICATION.ID=SPT_MANAGED_ATTRIBUTE.APPLICATION and
            SPT_IDENTITY.CORRELATED ='1' and
            SPT_IDENTITY.NAME=<NAME ATTRIBUTE>



This will get list of all the Entitlement details for the all the user's

Select  SPT_IDENTITY.NAME AS "USER ID" ,SPT_APPLICATION.NAME AS "APPLICATION NAME", 
        SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY AS "ACCOUNT ID", 
        SPT_IDENTITY_ENTITLEMENT.VALUE  AS "ENTITLEMENT VALUE"
        from SPT_IDENTITY_ENTITLEMENT , 
             SPT_MANAGED_ATTRIBUTE , 
             SPT_APPLICATION ,
             SPT_IDENTITY 
        where 
            SPT_MANAGED_ATTRIBUTE.APPLICATION=SPT_IDENTITY_ENTITLEMENT.APPLICATION and
            SPT_MANAGED_ATTRIBUTE.VALUE = SPT_IDENTITY_ENTITLEMENT.VALUE and 
            SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID = SPT_IDENTITY.ID and
            SPT_APPLICATION.ID=SPT_MANAGED_ATTRIBUTE.APPLICATION and

            SPT_IDENTITY.CORRELATED ='1' and


Same Query can be modified and can be used to get the data with more details like extended attribute from Entitlement catalog or the Identity Attributes.


SPT_IDENTITY_ENTITLEMENT --> Contain relation between the Identity and Identity Entitlement.
SPT_MANAGED_ATTRIBUTE --> Contains the managed attributes details
SPT_APPLICATION --> Contain the Application related details
SPT_IDENTITY --> Contains all the User Identity Attributes