Modules

Displays certification progress.

nan

Data Analyzer, Analytics

SELECT cert_name,progress FROM certification ORDER BY progress DESC;
                    

nan

Displays completed certifications.

nan

Data Analyzer, Analytics

SELECT cert_name,enddate FROM certification WHERE status=2 ORDER BY enddate DESC;
                    

nan

Displays expired certifications.

nan

Data Analyzer, Analytics

SELECT cert_name,enddate FROM certification WHERE status=4 ORDER BY enddate DESC;
                    

nan

Displays recently created certifications.

nan

Data Analyzer, Analytics

SELECT cert_name,startdate,enddate FROM certification ORDER BY startdate DESC;
                    

nan

Displays certifications grouped by type.

nan

Data Analyzer, Analytics

SELECT
CASE type
WHEN 1 THEN 'Entitlement Owner'
WHEN 2 THEN 'User Manager'
WHEN 3 THEN 'Self Certification'
WHEN 5 THEN 'Role Owner'
WHEN 6 THEN 'Service Account'
WHEN 7 THEN 'Organization'
WHEN 8 THEN 'Application Owner'
WHEN 9 THEN 'Application Owner Metadata'
ELSE CONCAT('Unknown (',type,')')
END AS Certification_Type,
COUNT(*) AS Total_Certifications
FROM certification
GROUP BY type
ORDER BY Total_Certifications DESC;
                    

nan

Displays certifications grouped by status.

nan

Data Analyzer, Analytics

SELECT
CASE status
WHEN 0 THEN 'New'
WHEN 1 THEN 'In Progress'
WHEN 2 THEN 'Completed'
WHEN 3 THEN 'Locked'
WHEN 4 THEN 'Expired'
WHEN 5 THEN 'Discontinued'
WHEN 6 THEN 'Preview'
WHEN 7 THEN 'Discontinued'
WHEN 8 THEN 'Locked and Task Created'
WHEN 9 THEN 'Cancelled'
WHEN 10 THEN 'Fully Executed'
ELSE CONCAT('Unknown (',status,')')
END AS Certification_Status,
COUNT(*) AS Total_Certifications
FROM certification
GROUP BY status
ORDER BY Total_Certifications DESC;
                    

nan

Get the details of all the certifications.

nan

Data Analyzer, Analytics

SELECT
c.CERTKEY,
c.cert_name AS Certification_Name,
c.CAMPAIGNKEY,
camp.campaign_name,
c.CERTIFIER AS Certifier_UserKey,
u_cert.username AS certifier_name,
c.CREATEDATE,
c.ENDDATE,
c.CREATEUSER,
c.PROGRESS,
ss.systemname AS security_system,
CASE
WHEN c.STATUS = 0 THEN 'NEW'
WHEN c.STATUS = 1 THEN 'INPROGRESS'
WHEN c.STATUS = 2 THEN 'COMPLETED'
WHEN c.STATUS = 3 THEN 'LOCKED'
WHEN c.STATUS = 4 THEN 'EXPIRED'
WHEN c.STATUS = 5 THEN 'DISCONTINUED'
WHEN c.STATUS = 6 THEN 'PREVIEW'
WHEN c.STATUS = 7 THEN 'DISCONTINUED'
WHEN c.STATUS = 8 THEN 'LOCKED_AND_TASK_CREATED'
WHEN c.STATUS = 9 THEN 'CANCELLED'
WHEN c.STATUS = 10 THEN 'FULLY_EXECUTED'
ELSE 'UNKNOWN'
END AS Status,
c.STARTDATE,
CASE
WHEN c.TYPE = 1 THEN 'ENTITLEMENT_OWNER'
WHEN c.TYPE = 2 THEN 'USER_MANAGER'
WHEN c.TYPE = 3 THEN 'SELF_CERTIFICATION'
WHEN c.TYPE = 5 THEN 'ROLE_OWNER'
WHEN c.TYPE = 6 THEN 'SERVICE_ACCOUNT'
WHEN c.TYPE = 7 THEN 'ORGANIZATION'
WHEN c.TYPE = 8 THEN 'APPLICATION_OWNER'
WHEN c.TYPE = 9 THEN 'APPLICATIONOWNER_METADATA'
ELSE 'UNKNOWN'
END AS Certification_Type,
c.UPDATEDATE,
c.UPDATEUSER,
c.AUDIT_TRAIL
FROM certification c
LEFT JOIN campaign camp
ON c.CAMPAIGNKEY = camp.id
LEFT JOIN users u_cert
ON c.CERTIFIER = u_cert.userkey
LEFT JOIN users u_create
ON c.CREATEUSER = u_create.userkey
LEFT JOIN users u_update
ON c.UPDATEUSER = u_update.userkey
LEFT JOIN securitysystems ss
ON c.SECURITYSYSTEMKEY = ss.systemkey;
                    

nan