Modules

Get the details of all the campaigns.

nan

Data Analyzer, Analytics

SELECT
c.id,
c.version,
c.campaign_name,
c.campaign_description,
c.campaign_owner_id,
u_owner.username AS campaign_owner_name,
c.campaign_owner_user_group_id,
ug.user_groupname AS campaign_owner_usergroup_name,
CASE
WHEN c.campaign_type = 1 THEN 'ENTITLEMENTOWNER'
WHEN c.campaign_type = 2 THEN 'USERMANAGER'
WHEN c.campaign_type = 3 THEN 'SELF_CERTIFICATION'
WHEN c.campaign_type = 5 THEN 'ROLEOWNER'
WHEN c.campaign_type = 6 THEN 'SERVICEACCOUNT'
WHEN c.campaign_type = 7 THEN 'ORGANIZATION'
WHEN c.campaign_type = 8 THEN 'APPLICATION_OWNER'
WHEN c.campaign_type = 9 THEN 'APPLICATIONOWNER_METADATA'
ELSE 'UNKNOWN'
END AS Campaign_Type,
c.creation_date,
c.start_date,
c.end_date,
e.endpointkey,
e.endpointname,
c.items_included,
c.last_updated,
c.lock_campaign_progress,
c.progress,
CASE
WHEN c.status = 1 THEN 'NEW'
WHEN c.status = 2 THEN 'COMPLETED'
WHEN c.status = 4 THEN 'PREVIEW'
WHEN c.status = 5 THEN 'INPROGRESS'
WHEN c.status = 6 THEN 'DISCONTINUED'
WHEN c.status = 7 THEN 'EXPIRED'
WHEN c.status = 8 THEN 'LAUNCHING'
ELSE 'UNKNOWN'
END AS Campaign_Status,
c.update_user_id,
u_update.username AS update_user_name,
c.audit_trail
FROM campaign c
LEFT JOIN users u_owner
ON c.campaign_owner_id = u_owner.userkey
LEFT JOIN user_groups ug
ON c.campaign_owner_user_group_id = ug.usergroupkey
LEFT JOIN endpoints e
ON c.endpointids = e.endpointkey
LEFT JOIN users u_update
ON c.update_user_id = u_update.userkey;
                    

nan