Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/27/2024 11:31 AM
Hi,
Is there a way to write a report to find the list of accounts which has "Owner Type" mentioned as "User Group" but the "Owner name" is empty.
09/27/2024 12:15 PM - edited 09/27/2024 12:16 PM
SELECT DISTINCT E.ENDPOINTNAME AS 'Application',
A.NAME AS 'Account Name',
A.ACCOUNTTYPE AS 'Account Type',
CASE
WHEN AO.OWNERUSERKEY IS NOT NULL THEN 'User'
WHEN AO.OWNERUSERGROUPKEY IS NOT NULL THEN 'Usergroup'
ELSE 'Blank'
END AS 'Owner Type',
AO.RANK AS 'Owner Rank',
CASE
WHEN AO.OWNERUSERKEY IS NOT NULL THEN UO.USERNAME
WHEN AO.OWNERUSERGROUPKEY IS NOT NULL THEN UG.USER_GROUPNAME
ELSE 'Blank'
END AS 'Owner user',
CASE
WHEN AO.OWNERUSERKEY IS NOT NULL THEN
CONCAT(UO.FIRSTNAME, ' ', UO.LASTNAME)
WHEN AO.OWNERUSERGROUPKEY IS NOT NULL THEN UG.USER_GROUPNAME
ELSE 'Blank'
END AS 'Owner Name',
CASE
WHEN AO.OWNERUSERKEY IS NOT NULL THEN
CASE UO.STATUSKEY
WHEN 1 THEN 'Active'
ELSE 'Inactive'
END
WHEN AO.OWNERUSERGROUPKEY IS NOT NULL THEN 'Not Applicable'
ELSE 'Blank'
END AS 'User Status'
FROM ACCOUNTS A
LEFT JOIN ENDPOINTS E
ON A.ENDPOINTKEY = E.ENDPOINTKEY
LEFT JOIN ACCOUNTOWNERS AO
ON AO.ACCOUNTKEY = A.ACCOUNTKEY
LEFT JOIN USERS UO
ON UO.USERKEY = AO.OWNERUSERKEY
LEFT JOIN USER_GROUPS UG
ON AO.OWNERUSERGROUPKEY = UG.USERGROUPKEY
WHERE A.STATUS NOT IN ( 'Suspended From Import Service' )
AND E.ENDPOINTNAME IN ( 'RUSHI' )
AND A.ACCOUNTTYPE IS NOT NULL