How To - Create Frequently used Campaign/Certification Reports

[prasannta]

Use Case

This document will provide the consolidated list of some of the frequently used Campaign/Certification Reports. This can be lifted and shifted at any customer environment.

This Document will cover the following scenarios:

• Certifiers with Empty Certifications 
• Certifier with No Email 
• Campaign Status by Application 
• Certification Status by Application 

Pre-requisites

N/A

Applicable Version(s)

All

Solution

Certifiers with Empty Certifications

This report displays the Certifiers who have Empty certifications or who do not have any data in their certifications.

select cm.campaign_name as 'Campaign Name',c.CERT_NAME as 'Certification Name', u.SYSTEMUSERNAME as 'Certifier User Name',
u.FIRSTNAME as 'Certifier First Name',u.LASTNAME as 'Certifier Last Name',c.STARTDATE as 'Certification Start Date',
c.ENDDATE as 'Certification End Date' from certification c,campaign cm,users u where c.CAMPAIGNKEY=cm.id and u.userkey=c.certifier
and c.status=2 and (c.certkey not in (select certkey from certification_account_entitlement1_status caes)
and c.certkey not in (select certkey from certification_role_user_account_status cruas));

Certifier with No Email

This report displays a list of Certifiers with blank or null Email

select cm.campaign_name as 'Campaign Name', c.CERT_NAME as 'Certification Name', CASE
WHEN c.status = 4 THEN 'Expired'
WHEN c.status in (3, 8 )THEN 'Locked'
WHEN c.status in (0, 1) THEN 'Pending'
WHEN c.status = 7 THEN 'Discontinued'
WHEN c.status = 2 THEN 'Ready to Submit'
WHEN c.status = 6 THEN 'Preview'
WHEN c.status = 10 THEN 'Fully Executed'
END AS 'Certification Status' ,u.SYSTEMUSERNAME as 'Certifier UserName',u.FIRSTNAME as 'Certifier FirstName',u.LASTNAME as 'Certifier LastName' from campaign cm, certification c, users u where cm.id=c.CAMPAIGNKEY and c.CERTIFIER=u.USERKEY and (u.EMAIL is null or '');

Campaign Status by Application

This report displays a consolidated list of Campaign Status by Application

SELECT
cm.campaign_name AS 'Campaign Name',
k.CERT_NAME AS 'Certification Name',
k.STARTDATE AS 'Certification StartDate',
k.ENDDATE AS 'Certification EndDate',
CASE
WHEN cm.campaign_type = 1 THEN 'Entitlement Owner'
WHEN cm.campaign_type = 2 THEN 'User Manager'
WHEN cm.campaign_type = 5 THEN 'Role Owner'
WHEN cm.campaign_type = 6 THEN 'Service Account'
END AS 'Certification Type',
u.SYSTEMUSERNAME AS 'Certification Owner',
CASE
WHEN k.status = 4 THEN 'Expired'
WHEN k.status IN (3 , 8)THEN 'Locked'
WHEN k.status IN (0 , 1) THEN 'Pending'
WHEN k.status = 7 THEN 'Discontinued'
WHEN k.status = 2 THEN 'Ready to Submit'
WHEN k.status = 6 THEN 'Preview'
WHEN k.status = 10 THEN 'Fully Executed'
END AS 'Certification Status',
(SELECT
COUNT(*)
FROM
certification_user_status cu
WHERE
k.certkey = cu.certkey) AS 'Total Users',
(SELECT
COUNT(*)
FROM
certification_account_entitlement1_status caes
WHERE
k.certkey = caes.certkey
AND caes.certified IN (0 , 3)
AND caes.taskstatus=1) AS 'Total Revokes',
(select group_concat(e.endpointname) from campaign t, endpoints e
where FIND_IN_SET(e.endpointkey,t.endpointids) and t.id= cm.id
group by t.id) as 'Endpoint Name',
(select concat(round((
(select count(*) from certification_certifier cc,certification ct where cc.CERTKEY=ct.CERTKEY and cc.certkey=k.certkey)/(select count(*) from certification_account_entitlement1_status caes where caes.certkey = k.certkey))*100,2),'%')) as 'Consultation Rate'
FROM
campaign cm,
users u,
certification k
WHERE
k.certifier = u.USERKEY
AND cm.id = k.CAMPAIGNKEY
GROUP BY cm.Campaign_Name , k.CERT_NAME , k.StartDate , k.EndDate , cm.campaign_type ,u.SYSTEMUSERNAME , cm.status;

Certification Status by Application 

This report displays a consolidated list of Certification Status by Application

SELECT
cm.campaign_name AS 'Campaign Name',
k.CERT_NAME AS 'Certification Name',
k.STARTDATE AS 'Certification StartDate',
k.ENDDATE AS 'Certification EndDate',
CASE
WHEN cm.campaign_type = 1 THEN 'Entitlement Owner'
WHEN cm.campaign_type = 2 THEN 'User Manager'
WHEN cm.campaign_type = 5 THEN 'Role Owner'
WHEN cm.campaign_type = 6 THEN 'Service Account'
END AS 'Certification Type',
u.SYSTEMUSERNAME AS 'Certifier',
CASE
WHEN k.status = 4 THEN 'Expired'
WHEN k.status IN (3 , 8)THEN 'Locked'
WHEN k.status IN (0 , 1) THEN 'Pending'
WHEN k.status = 7 THEN 'Discontinued'
WHEN k.status = 2 THEN 'Ready to Submit'
WHEN k.status = 6 THEN 'Preview'
WHEN k.status = 10 THEN 'Fully Executed'
END AS 'Certification Status',
(SELECT
COUNT(*)
FROM
certification_user_status cu
WHERE
k.certkey = cu.certkey) AS 'Total Users',
(SELECT
COUNT(*)
FROM
certification_account_entitlement1_status caes
WHERE
k.certkey = caes.certkey
AND caes.certified IN (0 , 3)
AND caes.taskstatus=1) AS 'Total Revokes',
(select group_concat(e.endpointname) from campaign t, endpoints e
where FIND_IN_SET(e.endpointkey,t.endpointids) and t.id= cm.id
group by t.id) as 'Endpoint Name'
FROM
campaign cm,
users u,
certification k
WHERE
k.certifier = u.USERKEY
AND cm.id = k.CAMPAIGNKEY
GROUP BY cm.Campaign_Name , k.CERT_NAME , k.StartDate , k.EndDate , cm.campaign_type , u.SYSTEMUSERNAME , cm.status;

References

[rushikeshvartak]

Fix emoji character in query 

[Manu269]

Please correct as per below :

Status = 0,1 --> Pending
Status = 2 --> Ready to Submit
status = 3 , 8 --> Locked
Status = 4 --> Expired
status = 6 --> Preview
status = 7 --> Discontinued
status = 10 --> Fully Executed