Thursday, August 29, 2019

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