Service Account Metadata SQL

 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',

    COALESCE(CAST(ao.RANK AS CHAR), 'Blank') AS 'Owner Rank',

    COALESCE(uo.username, ug.USER_GROUPNAME, 'Blank') AS 'Owner user',

    COALESCE(CONCAT(uo.firstname, ' ', uo.lastname), ug.USER_GROUPNAME, 'Blank') AS 'Owner Name',

    COALESCE(uo.email, ug.USER_GROUPNAME, 'Blank') AS 'Owner Email',

    CASE 

        WHEN ao.OWNERUSERKEY IS NOT NULL THEN 

            CASE 

                WHEN uo.statuskey = 1 THEN 'Active' 

                WHEN uo.statuskey = 0 THEN 'Inactive' 

                ELSE CAST(uo.statuskey AS CHAR)

            END

        ELSE 'Blank'

    END AS 'Owner Status',

    ao.UPDATEDATE AS updatedon,

    CASE 

        WHEN a.status = 1 THEN 'Active' 

        WHEN a.status = 2 THEN 'Inactive' 

        ELSE CAST(a.status AS CHAR)

    END AS 'Account Status'

FROM accounts a

JOIN endpoints e ON a.endpointkey = e.endpointkey

LEFT JOIN accountowners ao ON ao.accountkey = a.accountkey

LEFT JOIN users uo ON ao.OWNERUSERKEY = uo.userkey

LEFT JOIN user_groups ug ON ao.OWNERUSERGROUPKEY = ug.USERGROUPKEY

WHERE a.status NOT IN ('Suspended From Import Service')

  AND e.endpointname = 'AD Service Accounts';