Modules
Browse queries by module.
All Modules
ARS Requests ARS Tasks Accounts Accounts, Endpoint Accounts, Endpoints Analytics Audit Logs Audit Trail Campaign Certification Connections Connections, Job Control Panel Endpoints Job Control Panel Roles Rules Security Systems User Group Users Users, Accounts Users, Accounts, Endpoint Users, Entitlements Users, Roles Users, SAV ROLESCertification Details
Module: Certification
Description
Get the details of all the certifications.
Dynamic Input
nan
Execute in
Data Analyzer, Analytics
SQL Query
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;
Comments
nan
Query copied!