Query to get all identities with AD link using query

[rishavghoshacc (Rishav Ghosh)]

HI all,

I want to get all the identities who have accounts on Active Directory. I want to run a SQL query in the IIQ database to find all such identities. Can someone help me with writing the query

Thanks in advance

[Harikrishna_06 (Harikrishna Tungala)]

Hi @rishavghoshacc,

Use below sql query to get all identities who have accounts in Active Directory

SELECT DISTINCT i.name AS IdentityName, i.display_name, l.application
FROM spt_identity i
JOIN spt_link l ON i.id = l.identity_id
WHERE l.application = ‘c0a8386e920411ec819208d6d5600119’
ORDER BY i.name;

c0a8386e920411ec819208d6d5600119 = It is Application Id

[msingh900 (Manish Singh)]

@rishavghoshacc

Use the below query to get the desired result by providing the app name in the SQL Query:

SELECT DISTINCT idn.name AS IdentityName, idn.display_name, app.name
FROM spt_identity idn
JOIN spt_link lnk ON idn.id = lnk.identity_id
JOIN spt_application app ON lnk.application = app.id
WHERE lnk.application = (SELECT id from spt_application where name = '<Your_App_Name>')
ORDER BY idn.name;