Customize reports
You can generate built-in reports to report on one user, one application, one role, or one certifier, as described in Generate built-in reports. If you want a single report to contain more than one user, application, role, or certifier, you must create a custom report.
Create a custom report
-
In the Identity Administration portal, go to Reports > My Reports > New Report.
-
In the upper left text box, type the name of the new report. For example:
-
Click Edit Script, then Yes to confirm.
-
Copy the query syntax for the report you want from Report query syntax and paste it into the Editor box, as shown in this example.
-
Edit the parameters you want to change. See Report query parameters for details.
-
Click Preview to display the report. When prompted, enter the start and end dates you want the report to include. Click Save when you're done.
Report query syntax
Certification decisions taken by multiple certifiers
select
cc.Name as 'Cycle Name' , cc.CycleStartDate as 'Cycle start date', cc.CycleEndDate as 'Cycle end date',
cr.ResourceType as 'Resource Type',
CASE
WHEN cr.ResourceType='Role' THEN role.Name
WHEN cr.ResourceType='Application' THEN app.DisplayName
END as 'Resource Name',
ccu.Status as 'Action type',
u.DisplayName as Certifier,
COALESCE(mem.Username, mem.Email) as 'Username/Email' ,
ccu.ActionTime as 'Action taken on'
from CycleUsers as ccu
join CampaignCycle as cc on cc.ID=ccu.CycleId and cc.Certifier = ccu.Certifier and cc.CampaignId=ccu.CampaignId
inner join CampaignResource as cr on cr.CampaignId = cc.CampaignId and cr.ResourceId = ccu.ResourceId
left outer join Role as role on role.ID = cr.ResourceId and cr.ResourceType='Role'
join Application as app on app.ID = cr.ResourceId and cr.ResourceType='Application'
join User as u on u.ID = ccu.Certifier
join User as mem on mem.ID = ccu.UserId
where cc.CycleStartDate >=@<StartDate> and cc.CycleEndDate <=@<EndDate>
and u.Username IN( '<certifier1>','<certifier2>' )
Certification decisions taken on multiple users
select
cc.Name as 'Cycle Name' , cc.CycleStartDate as 'Cycle start date',
cc.CycleEndDate as 'Cycle end date',
cr.ResourceType as 'Resource Type',
CASE
WHEN cr.ResourceType='Role' THEN role.Name
WHEN cr.ResourceType='Safe' THEN cr.ResourceId
WHEN cr.ResourceType='Application' THEN app.DisplayName
END as 'Resource Name',
ccu.Status as 'Action type',
cf.DisplayName as 'Certifier',
COALESCE(u.Username, u.Email) as 'Username/Email' ,
ccu.ActionTime as 'Action taken on'
from campaigncycles as cc
join campaigncertifierusers as ccu on ccu.CycleId = cc.ID and ccu.Certifier = cc.Certifier
join users as u on ccu.UserId = u.ID
join users as cf on ccu.Certifier = cf.ID
inner join campaignresources as cr on cr.CampaignId = cc.CampaignId and cr.ResourceId = ccu.ResourceId
left outer join roles as role on role.ID = cr.ResourceId and cr.ResourceType='Role'
left outer join application as app on app.ID = cr.ResourceId and cr.ResourceType='Application'
where cc.CycleStartDate >= @StartDate and cc.CycleEndDate <= @EndDate
and u.Username IN( '<username1>', '<username2>' )
Certification decisions taken on multiple applications
select
cc.Name as 'Cycle Name' , cc.CycleStartDate as 'Cycle start date', cc.CycleEndDate as 'Cycle end date',
cr.ResourceType as 'Resource Type',
app.DisplayName as 'Resource Name',
ccu.Status as 'Action type',
u.DisplayName as Certifier,
COALESCE(mem.Username, mem.Email) as 'Username/Email' ,
ccu.ActionTime as 'Action taken on'
from CycleUsers as ccu
join CampaignCycle as cc on cc.ID=ccu.CycleId and cc.Certifier = ccu.Certifier and cc.CampaignId=ccu.CampaignId
inner join CampaignResource as cr on cr.CampaignId = cc.CampaignId and cr.ResourceId = ccu.ResourceId and cr.ResourceType='Application'
left outer join Application as app on app.ID = cr.ResourceId and cr.ResourceType='Application'
join User as u on u.ID = ccu.Certifier
join User as mem on mem.ID = ccu.UserId
where cc.CycleStartDate >= @StartDate and cc.CycleEndDate <= @EndDate
and app.DisplayName IN( '<app1>','<app2>' )
Certification decisions taken on multiple roles
select
cc.Name as 'Cycle Name' , cc.CycleStartDate as 'Cycle start date', cc.CycleEndDate as 'Cycle end date',
cr.ResourceType as 'Resource Type',
role.Name as 'Resource Name',
ccu.Status as 'Action type',
u.DisplayName as Certifier,
COALESCE(mem.Username, mem.Email) as 'Username/Email' ,
ccu.ActionTime as 'Action taken on'
from CycleUsers as ccu
join CampaignCycle as cc on cc.ID=ccu.CycleId and cc.Certifier = ccu.Certifier and cc.CampaignId=ccu.CampaignId
join CampaignResource as cr on cr.CampaignId = cc.CampaignId and cr.ResourceId = ccu.ResourceId and cr.ResourceType='Role'
join Role as role on role.ID = cr.ResourceId and cr.ResourceType='Role'
join User as u on u.ID = ccu.Certifier
join User as mem on mem.ID = ccu.UserId
where cc.CycleStartDate >= @StartDate and cc.CycleEndDate <= @EndDate
and role.Name IN( '<role1>','<role2>' )
Report query parameters
You must edit the query to specify parameters. In this example, you must specify at least one certifier:
where cc.CycleStartDate >=@<StartDate> and cc.CycleEndDate <=@<EndDate>
and u.Username IN( '<certifier1>','<certifier2>' )
The following table describes how to specify the parameters.
Parameter |
Description |
Report |
---|---|---|
u.Username |
Indicates that you are using a username (instead of an email address) to identify each user. Enclose each username within single quotes and use a comma between each username. For example: 'john@example.com','jane@example.com' |
Users, certifiers |
u.Email |
Indicates that you are using an email address (instead of a username) to identify each user. Enclose each email address within single quotes and use a comma between each email address. For example: 'jsmith@example.com','mroberts@example.com' |
Users, certifiers |
'<certifier1>','<certifier2>' |
Certifiers to include in the report. Specify either usernames with u.Username or email addresses with e.Email. |
Certifiers |
'<username1>','<username2>' |
Users to include in the report. Specify either usernames with u.Username or email addresses with e.Email. |
Users |
'<app1>','<app2>' |
Applications to include in the report. Use the application display name. For example: 'AssetSonar','Office365' |
Applications |
'<role1>','<role2>' |
Roles to include in the report. For example: 'PortalAdmin','Approvers' |
Roles |