UM Campaign Data - Analytics Report

[Sivagami]

I have user manager certification that was launched but the certifiers are not able to close the certifications as it shows pending items post all the items are certified. Please note that the manager is trying to certify their Direct report's access only. 

Total items displayed in the page is not matching the number of items pending to be certified even if no action is taken on the certification. 

So, I'm trying to create a report that shows all the records (Base account + Entitlements) associated in the certification but I'm able to query only the entitlements in the certification. Which table shows the base account records in the certification?

SELECT 
    u.USERNAME AS 'Certifier Username',
    u.FIRSTNAME AS 'Certifier First Name',
    u.LASTNAME AS 'Certifier Last Name',
  a.ACCOUNTID as 'Account ID', 
  a.NAME as 'Account Name' ,
    cm.campaign_name AS 'Campaign Name',
    c.CERT_NAME AS 'Certification Name',
    cev.ENTITLEMENT_VALUE AS 'Entitlement Value',
    CASE
       WHEN aces.certified = 2 THEN 'Certified'
       WHEN aces.certified = 3 THEN 'Revoked'
       WHEN aces.certified = 5 THEN 'Conditionally Certified'
       ELSE 'No Response'
    END AS 'Certifier Action',
    c.CREATEDATE AS 'Certification Start Date',
    c.ENDDATE AS 'Certification End Date'
FROM
    certification c left join 
    campaign cm on c.CAMPAIGNKEY = cm.id left join 
    users u on  c.CERTIFIER = u.USERKEY left join 
    certification_account_entitlement1_status aces on c.certkey = aces.certkey left join
  certification_account ca on  ca.CERT_ACCOUNTKEY = aces.CERT_ACCOUNTKEY left join
  accounts a on ca.ACCOUNTKEY = a.ACCOUNTKEY left join
    certification_entitlement_value cev on cev.CERT_ENTITLEMENT_VALUEKEY = aces.CERT_ENTITLEMENT_VALUEKEY 
WHERE cm.campaign_type=2
         AND c.certkey=<SpecificCertKey>;

Saviynt Support Ticket Reference - https://saviyntsupport.saviynt.com/support/tickets/2001643  

-Siva

[nimitdave]

@Sivagami , see if below query helps

SELECT DISTINCT certification_user.USERNAME, certification_user.FIRSTNAME, certification_user.LASTNAME, certification_user.EMAIL, certification_user.STATUSKEY, certification_account.NAME, certification_account.STATUS, certification_entitlement_value.ENTITLEMENT_VALUE, ENTITLEMENTNAME, endpoints.ENDPOINTNAME FROM campaign INNER JOIN certification ON campaign.id = certification.CAMPAIGNKEY INNER JOIN certification_account_entitlement1_status ON certification.CERTKEY = certification_account_entitlement1_status.CERTKEY INNER JOIN certification_account ON certification_account_entitlement1_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY INNER JOIN certification_entitlement_value ON certification_account_entitlement1_status.CERT_ENTITLEMENT_VALUEKEY = certification_entitlement_value.CERT_ENTITLEMENT_VALUEKEY INNER JOIN entitlement_types ON certification_entitlement_value.ENTITLEMENTTYPEKEY = entitlement_types.ENTITLEMENTTYPEKEY INNER JOIN endpoints ON entitlement_types.ENDPOINTKEY = endpoints.ENDPOINTKEY LEFT JOIN certification_user_account_status ON certification_user_account_status.CERT_ACCOUNTKEY = certification_account.CERT_ACCOUNTKEY LEFT JOIN certification_user ON certification_user.CERT_USERKEY = certification_user_account_status.CERT_USERKEY WHERE campaign.campaign_name = 'INSERT CAMPAIGN NAME HERE' -- Modifiable Parameter ORDER BY certification_user.CREATEDBY DESC;

[Sivagami]

Even this query is not listing out the base account entries in the campaign. It only lists out all the entitlements reviewed in the campaign.

-Siva

[DaanishJawed · Accepted solution]

HI @Sivagami ,

Can you try with the attached query by replacing the campaign key in three union queries?

Thanks.

[Sivagami · Accepted solution]

Thanks Daanish! The query did help to identify the base account records that weren't getting displayed in UI. It also shows the Step1 status as well which I removed for my use case.

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,
       users.departmentname              AS Certifier_Departmentname,
       cu.username                       AS USER,
       u2.firstname                      AS User_Firstname,
       u2.lastname                       AS User_Lastname,
       u2.departmentname                 AS User_Departmentname,
       c.progress                        AS 'Percentage',
       CASE
         WHEN c.status = 0 THEN 'New'
         WHEN c.status = 1 THEN 'In Progress'
         WHEN c.status = 2 THEN 'Completed'
         WHEN c.status = 3 THEN 'Locked'
         WHEN c.status = 4 THEN 'Expired'
         WHEN c.status = 7 THEN 'Discontinued'
         WHEN c.status = 8 THEN 'Locked and Task Created'
         ELSE NULL
       END                               AS 'Status of Certification',
       c.createdate                      AS 'Date_Created',
       c.enddate                         AS 'Due _Date_of_Review',
       caa.NAME                          AS AccountName,
       endpoints.endpointname            AS Application,
       'Base Account'                    AS Entitlement_Value,
       CASE
         WHEN cs.certified = 2 THEN 'CERTIFIED'
         WHEN cs.certified = 3 THEN 'REVOKED'
         WHEN cs.certified = 5 THEN 'CONDITIONALLY CERTIFIED'
         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_user_account_status cs
               ON c.certkey = cs.certkey
       INNER JOIN certification_account caa
               ON caa.cert_accountkey = cs.cert_accountkey
       INNER JOIN certification_user cu
               ON cu.cert_userkey = cs.cert_userkey
       LEFT JOIN users
              ON users.userkey = c.certifier
       LEFT JOIN users u2
              ON u2.username = cu.username
       LEFT JOIN endpoints
              ON endpoints.endpointkey = caa.endpointkey
WHERE  c.campaignkey IN ('INSERT CAMPAIGN KEY HERE')
UNION ALL
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,
       users.departmentname              AS Certifier_Departmentname,
       u2.username                       AS USER,
       u2.firstname                      AS User_Firstname,
       u2.lastname                       AS User_Lastname,
       u2.departmentname                 AS User_Departmentname,
       c.progress                        AS 'Percentage',
       CASE
         WHEN c.status = 0 THEN 'New'
         WHEN c.status = 1 THEN 'In Progress'
         WHEN c.status = 2 THEN 'Completed'
         WHEN c.status = 3 THEN 'Locked'
         WHEN c.status = 4 THEN 'Expired'
         WHEN c.status = 7 THEN 'Discontinued'
         WHEN c.status = 8 THEN 'Locked and Task Created'
         ELSE NULL
       END                               AS 'Status of Certification',
       c.createdate                      AS 'Date_Created',
       c.enddate                         AS 'Due_Date_of_Review ',
       caa.NAME                          AS AccountName,
       endpoints.endpointname            AS Application,
       cev.entitlement_value,
       CASE
         WHEN cs.certified = 2 THEN 'CERTIFIED'
         WHEN cs.certified = 3 THEN 'REVOKED'
         WHEN cs.certified = 5 THEN 'CONDITIONALLY CERTIFIED'
         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.campaignkey IN ('INSERT CAMPAIGN KEY HERE'); 

 -Siva