Modules

Displays expired campaigns.

nan

Data Analyzer, Analytics

SELECT campaign_name,end_date FROM campaign WHERE status=7 ORDER BY end_date DESC;
                    

nan

Displays campaigns currently in progress.

nan

Data Analyzer, Analytics

SELECT campaign_name,start_date,end_date FROM campaign WHERE status=5 ORDER BY start_date DESC;
                    

nan

Displays recently created campaigns.

nan

Data Analyzer, Analytics

SELECT
CASE campaign_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 (',campaign_type,')')
END AS Campaign_Type,
COUNT(*) AS Total_Campaigns
FROM campaign
GROUP BY campaign_type
ORDER BY Total_Campaigns DESC;
                    

nan

Displays campaigns grouped by campaign type.

nan

Data Analyzer, Analytics

SELECT
CASE campaign_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 (',campaign_type,')')
END AS Campaign_Type,
COUNT(*) AS Total_Campaigns
FROM campaign
GROUP BY campaign_type
ORDER BY Total_Campaigns DESC;
                    

nan

Displays campaigns grouped by status.

nan

Data Analyzer, Analytics

SELECT
CASE status
WHEN 1 THEN 'New'
WHEN 2 THEN 'Completed'
WHEN 4 THEN 'Preview'
WHEN 5 THEN 'In Progress'
WHEN 6 THEN 'Discontinued'
WHEN 7 THEN 'Expired'
WHEN 8 THEN 'Launching'
ELSE CONCAT('Unknown (',status,')')
END AS Campaign_Status,
COUNT(*) AS Total_Campaigns
FROM campaign
GROUP BY status
ORDER BY Total_Campaigns DESC;
                    

nan

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