Tuesday, May 5, 2020

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