SailPoint Queries

 # Joiner Query:-

SELECT firstname, lastname, name, email FROM spt_identity

WHERE employee_status = "Active" AND needs_joiner = "PROCESSING";

SELECT i.firstname, i.lastname, i.name, i.email, i.type FROM spt_identity i, spt_application a, spt_link l, spt_task_result t

WHERE a.id = l.application

AND l.identity_id = i.id

AND t.target_id = t.target_id

AND i.employee_status = 'Active'

AND a.name = 'Active Directory'

AND l.attributes NOT LIKE 'IIQDisabled'

AND t.name LIKE 'Joiner'

AND t.created > 1234567890 (yesterday time in epoch)

AND t.created < 1244567890; (today time in epoch)

# Leaver Query:-

SELECT i.firstname, i.lastname, i.name, i.email, i.type FROM spt_identity i, spt_application a, spt_link l

WHERE a.id = l.application

AND l.identity_id = i.id

AND i.employee_status = 'inactive'

AND i.org_name NOT LIKE '%Employees on Layoff%'

AND a.name = ''Active Directory'

AND l.attributes NOT LIKE 'IIQDisabled';

SELECT i.firstname, i.lastname, i.name, i.email, i.type FROM spt_identity i, spt_application a, spt_link l

WHERE a.id = l.application

AND l.identity_id = i.id

AND t.target_id =i.id

AND l.identity_id = t.target_id

AND i.employee_status = 'inactive'

AND i.org_name NOT LIKE '%Employees on Layoff%'

AND a.name = ''Active Directory'

AND l.attributes NOT LIKE 'IIQDisabled'

AND t.name LIKE 'LEAVER'

AND t.created > 1234567890 (yesterday time in epoch)

AND t.created < 1244567890; (today time in epoch)

=================

SELECT * 

FROM identityiq.spt_link

GROUP BY native_identity, application

HAVING COUNT(*) > 1;