following report present attributes "department" gaps between the identity to the AD accounts.
you can add any atributes you like or change the attribue you wish to find gaps of data.
with diffTable as
(
-- NOTE: IN FIRST SELECT YOU CAN PULL ANY ATTRIBUTE FROM THE IDENTITY LEVEL
select
[identityiq].spt_identity.name,
[identityiq].spt_identity.extended1, -- here you put any attributes you want to compare, in our case should be department
cast ([identityiq].spt_link.attributes as XML).value('(/Attributes/Map/entry[@key="department"]/@value)[1]', 'VARCHAR(255)') AS ADdepartment -- here we case from the XML the department attribute, in our case we compare for AD but we can do same for other targets
from
[identityiq].spt_link, -- TABLE HOLD ACCOUNTS FROM ALL TARGET SYSTEMS
[identityiq].spt_application, -- APPLICATION TABLE, WE CAN CHOOSE FOR EACH QUERY WHICH APPLICTION WE WANT TO CHECK
[identityiq].spt_identity -- IDENTITY TABLE
where
[identityiq].spt_identity.id=[identityiq].spt_link.identity_id -- CONNECT IDENTITY TO ACCOUNTS
and [identityiq].spt_link.application=[identityiq].spt_application.id -- CONNECT LINKS TO THE APPLICATION WE WANT TO QUERY
and [identityiq].spt_application.name='AD SERI' -- SET THE APPLICATION, FOR EXAMPLE ACTIVE DIRECTORY
)
select * from diffTable where extended1!=ADdepartment -- SHOW ONLY THOSE WITH DIFFERENT VALUE OF DEPARTMENT