Report to fetch entitlement owner information from revoked tasks

[sb17gds]

Hi,

We want to pull a report based on arstasks table of remove access tasks that have been created through certification. This table should have entitlement owner information and revoked user information along with entitlement details.

I came up with this query, but it's showing too long execution time:

select u1.username as 'User', u2.username as 'Entitlement Owner', e.ENDPOINTNAME, ar.taskkey, ar.accountname, ar.comments from users u1, user_accounts ua, accounts a, arstasks ar, endpoints e, entitlement_types et, entitlement_values ev, entitlement_owners evo, users u2 where ua.userkey = u1.userkey and a.accountkey = ua.accountkey and a.accountkey = ar.accountkey and ar.ENDPOINT = e.ENDPOINTKEY and et.ENDPOINTKEY = e.ENDPOINTKEY AND ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY and evo.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY and evo.userkey = u2.userkey and ar.COMMENTS like '%Rejected Access and Certification Locked%'

Does anyone have another query for this usecase? Or know why this isn't working?

Thanks

[Amit_Malik]

Try it with Joins 

select u1.username as 'User', u2.username as 'Enitlement_Owner', ev.entitlement_value,e.ENDPOINTNAME, ar.taskkey, ar.accountname, ar.comments from users u1
JOIN user_accounts ua ON ua.userkey = u1.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
JOIN arstasks ar ON a.accountkey = ar.accountkey
JOIN endpoints e ON ar.ENDPOINT = e.ENDPOINTKEY
JOIN entitlement_types et ON et.ENDPOINTKEY = e.ENDPOINTKEY
JOIN entitlement_values ev ON ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY
JOIN entitlement_owners evo ON evo.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
JOIN users u2 ON evo.userkey=u2.userkey
where ar.COMMENTS like '%Rejected Access and Certification Locked%'

[sb17gds]

Shows the same message

[sb17gds]

Alternatively, I've also made up this certification specific query for the same thing:

SELECT c.CERT_NAME AS 'Certification Name', ca.campaign_name AS 'Campaign Name', users.username AS Certifier, users.Firstname AS Certifier_FName, users.Lastname AS Certifier_LName, u2.username AS User, u2.Firstname AS User_Firstname, u2.lastname AS User_Lastname, c.CREATEDATE AS 'Date_Created', caa.name AS AccountName, endpoints.displayname AS Application, endpoints.ownerkey as OwnerKey, cev.Entitlement_Value, CASE WHEN cs.CERTIFIED = 2 THEN 'CERTIFIED' WHEN cs.CERTIFIED = 3 THEN 'REVOKED' WHEN cs.CERTIFIED = 5 THEN 'CONDITIONALLY CERTIFIED' WHEN cs.CERTIFIED = - 1 THEN 'TERMINATED' ELSE 'NO RESPONSE' END AS FinalAction, REPLACE(cs.AUDIT_TRAIL, ' ', ';') AS Audit_Log, REPLACE(cs.Comments, ' ', ';') AS Comments1 FROM certification c INNER JOIN campaign ca ON c.CAMPAIGNKEY = ca.id INNER JOIN certification_account_entitlement1_status cs ON c.certkey = cs.certkey INNER JOIN certification_account caa ON caa.cert_accountkey = cs.cert_accountkey INNER JOIN certification_entitlement_value cev ON cev.cert_entitlement_valuekey = cs.cert_entitlement_valuekey INNER JOIN users ON users.userkey = c.certifier INNER JOIN user_accounts ua ON ua.accountkey = caa.accountkey INNER JOIN users u2 ON u2.userkey = ua.userkey INNER JOIN endpoints ON endpoints.ENDPOINTKEY = caa.endpointkey WHERE c.CERT_NAME LIKE '%Test_UM_Employee_V19%' and cs.CERTIFIED = 3;

Only issue is I can't get the join to work in a way where it will display the application owner's username - does anyone have a pointer for this query in case?

[rushikeshvartak]

SELECT C.CERT_NAME                       AS 'Certification Name',
       CA.CAMPAIGN_NAME                  AS 'Campaign Name',
       USERS.USERNAME                    AS Certifier,
       USERS.FIRSTNAME                   AS Certifier_FName,
       USERS.LASTNAME                    AS Certifier_LName,
       U2.USERNAME                       AS USER,
       U2.FIRSTNAME                      AS User_Firstname,
       U2.LASTNAME                       AS User_Lastname,
       C.CREATEDATE                      AS 'Date_Created',
       CAA.NAME                          AS AccountName,
       ENDPOINTS.DISPLAYNAME             AS Application,
       ENDPOINTS.OWNERKEY                AS OwnerKey,
       OWNER_USER.USERNAME               AS Application_Owner_Username,
       -- Added Owner's Username
       CEV.ENTITLEMENT_VALUE,
       CASE
         WHEN CS.CERTIFIED = 2 THEN 'CERTIFIED'
         WHEN CS.CERTIFIED = 3 THEN 'REVOKED'
         WHEN CS.CERTIFIED = 5 THEN 'CONDITIONALLY CERTIFIED'
         WHEN CS.CERTIFIED = -1 THEN 'TERMINATED'
         ELSE 'NO RESPONSE'
       END                               AS FinalAction,
       Replace(CS.AUDIT_TRAIL, ' ', ';') AS Audit_Log,
       Replace(CS.COMMENTS, ' ', ';')    AS Comments1
FROM   CERTIFICATION C
       INNER JOIN CAMPAIGN CA
               ON C.CAMPAIGNKEY = CA.ID
       INNER JOIN CERTIFICATION_ACCOUNT_ENTITLEMENT1_STATUS CS
               ON C.CERTKEY = CS.CERTKEY
       INNER JOIN CERTIFICATION_ACCOUNT CAA
               ON CAA.CERT_ACCOUNTKEY = CS.CERT_ACCOUNTKEY
       INNER JOIN CERTIFICATION_ENTITLEMENT_VALUE CEV
               ON CEV.CERT_ENTITLEMENT_VALUEKEY = CS.CERT_ENTITLEMENT_VALUEKEY
       INNER JOIN USERS
               ON USERS.USERKEY = C.CERTIFIER
       INNER JOIN USER_ACCOUNTS UA
               ON UA.ACCOUNTKEY = CAA.ACCOUNTKEY
       INNER JOIN USERS U2
               ON U2.USERKEY = UA.USERKEY
       INNER JOIN ENDPOINTS
               ON ENDPOINTS.ENDPOINTKEY = CAA.ENDPOINTKEY
       INNER JOIN USERS OWNER_USER
               ON ENDPOINTS.OWNERKEY = OWNER_USER.USERKEY
-- Join to get Owner's Username
WHERE  C.CERT_NAME LIKE '%Test_UM_Employee_V19%'
       AND CS.CERTIFIED = 3;