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

Sailpoint IIQ - Item was revoked but has not been removed - Items database query

Many time in certification we see the message "Item was revoked but has not been removed.", this comes when the certifier takes the action on the item and either it get failed (in case of the connected system) or have generated the Workitem or ticket and the file is not aggregated back . below is the query which gives the information of the such items such as the identity , entitlement name , application to which this entitlement belongs , native identity of the user for the application and date on which the certifer took the action . 

This query can we further modified to get more information

SELECT 
SPT_IDENTITY.NAME,
SPT_IDENTITY_ENTITLEMENT.VALUE,
SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION,
SPT_PROVISIONING_TRANSACTION.STATUS,
SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY,
SPT_CERTIFICATION_ACTION.STATUS,
( To_date('1970-01-01 00', 'yyyy-mm-dd hh24') + ( SPT_CERTIFICATION_ACTION.DECISION_DATE) / 1000 / 60 / 60 / 24 )                    AS "CERT_DECISION_DATE" 
FROM 
  SPT_IDENTITY_ENTITLEMENT,
  SPT_CERTIFICATION_ITEM,
  SPT_CERTIFICATION_ACTION,
  SPT_IDENTITY,
  SPT_APPLICATION,
  SPT_MANAGED_ATTRIBUTE,
  SPT_CERTIFICATION_ENTITY,
  SPT_PROVISIONING_TRANSACTION
WHERE 
CERTIFICATION_ITEM IS NOT NULL
AND SPT_CERTIFICATION_ITEM.ID=SPT_IDENTITY_ENTITLEMENT.CERTIFICATION_ITEM
AND SPT_CERTIFICATION_ACTION.ID=SPT_CERTIFICATION_ITEM.ACTION
AND SPT_CERTIFICATION_ACTION.STATUS='Remediated'
AND SPT_IDENTITY.ID=SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID
AND SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION=SPT_APPLICATION.NAME
AND SPT_MANAGED_ATTRIBUTE.APPLICATION=SPT_APPLICATION.ID
AND SPT_MANAGED_ATTRIBUTE.VALUE=SPT_IDENTITY_ENTITLEMENT.VALUE 
AND SPT_CERTIFICATION_ENTITY.TARGET_ID=SPT_IDENTITY.ID 
AND SPT_CERTIFICATION_ENTITY.CERTIFICATION_ID=SPT_PROVISIONING_TRANSACTION.CERTIFICATION_ID 
AND SPT_PROVISIONING_TRANSACTION.SOURCE='Certification' 
AND SPT_PROVISIONING_TRANSACTION.application_NAME=SPT_APPLICATION.NAME 
AND SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY=SPT_PROVISIONING_TRANSACTION.NATIVE_IDENTITY

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