Can we fetch the usernames of the users for whom a particular campaign has been launched

[Debankita]

Hi Team,

Please help me with the SQL query with which I can fetch the details of the users for whom a particular campaign has been launched,

[rushikeshvartak]

which certification ?

cert_role_user_account for role owner

[Debankita]

Hi Rushikesh,

Suppose a user-manager campaign named "xyz" got launched for user A. So, my requirement is to get the username of user A. 

Please note: I dont want the username of the certifier for the campaign, I want the username for the user for whom the campaign has been launched.

Please help me with a suitable SQL query.

[sk]

You can use below query and modify accordingly to your requirement. Below will result whole certification report which includes account and their entitlements that got reviewed under specific campaign along with user, certifier and certification status information. I highlighted the columns which will give user information

SELECT DISTINCT
c.CERT_NAME AS 'Cert_name',
users.username AS Certifier,
CASE 
WHEN c.status=0 THEN 'Start Certification'
WHEN c.status=1 THEN 'In Progress'
WHEN c.status=2 THEN 'Ready to Submit'
WHEN c.status=3 THEN 'Locked'
WHEN c.status=4 THEN 'Expired'
WHEN c.status=6 THEN 'Preview'
WHEN c.status=7 THEN 'Discontinued'
WHEN c.status=8 THEN 'Locked and Task Created'
WHEN c.status=10 THEN 'Fully executed'
else c.status
END AS 'Certificationstatus',
cev.Entitlement_Value,
cev.DISPLAYNAME AS 'Entitlement Display Name',
caa.name AS AccountName,
endpoints.ENDPOINTNAME,
u2.email AS Email,
u2.Firstname AS Firstname,
u2.lastname AS Lastname,
u2.SYSTEMUSERNAME AS Systemusername,
concat(u2.username, ' ', '(', u2.firstname, ' ', u2.username, ')') as Username,
cs.AUDIT_TRAIL AS 'Audit_trail',
CASE
WHEN cs.CERTIFIED = 0 THEN 'Rejected' 
WHEN cs.CERTIFIED = 1 THEN 'Approved'
WHEN cs.CERTIFIED = 2 THEN 'Approved'
WHEN cs.CERTIFIED = 3 THEN 'Rejected' 
WHEN cs.CERTIFIED = 5 THEN 'CONDITIONALLY CERTIFIED'
WHEN cs.CERTIFIED = - 1 THEN 'TERMINATED'
ELSE 'NO RESPONSE'
END AS Certified,
cs.Comments AS Comments
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
INNER JOIN entitlement_types et ON cev.ENTITLEMENTTYPEKEY= et.ENTITLEMENTTYPEKEY
WHERE
ca.campaign_name IN ('<CAMPAIGN NAME>')

[Manu269]

Please be mindful the comments column is restricted in analytics.