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