Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/26/2023 06:22 AM
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
Solved! Go to Solution.
09/26/2023 10:02 AM
@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;
09/26/2023 10:37 AM
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
09/26/2023 11:14 AM
HI @Sivagami ,
Can you try with the attached query by replacing the campaign key in three union queries?
Thanks.
09/27/2023 04:32 AM
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