IGI Report Building

Wednesday, 5 April 2017
IBM Security Identity Governance is undoubtedly a powerful tool. However, the documentation supplied with the tool could certainly do with some improvement and there is a desperate need for a lot more "technotes" to help deployment consultants get the best value out of the tool.

As an example, IGI ships with almost 100 canned reports but one important report that seems to be missing is a report listing all the orphan/unmatched accounts within the system.

Generating such a report should be a breeze using IGI's Report Designer module. But here comes another "however". The documentation doesn't really give you any kind of clue as to how the underlying database schema has been put together and therefore it is difficult to understand which tables, attributes and values should be used to construct such a report. It also doesn't help that in some cases, the naming convention used is somewhat confusing.

That said, here is how you might construct a simple Orphan Accounts report.

Step 1 - Create Query
Create a new query called "Orphan Accounts"  with the following as the SQL Query:

select
  t.name as APPNAME,
  pwdm.code as USERID,
  pwdm.name as FIRSTNAME,
  pwdm.surname as LASTNAME,
  pwdm.email as EMAIL,
  pwdm.dn as DN,
  pwdm.lastlogin as LASTLOGIN
from
  #pmschema#.pwdmanagement pwdm
  left join #pmschema#.target t on pwdm.pwdcfg=t.pwdcfg
where
  pwdm.state > 0

This query pulls out the Application Name, User ID, First & Last Names, Email Address, DN and Last Login Date/Time for those accounts that are in an orphan/unmatched state. The STATE attribute having a value greater than 0 means that the account has not been matched to an identity.

Make sure that you you select the "Query Column" link at the bottom of the screen and click on IMPORT in order to be left with the following:


Step 2 - Create Report
Now, create a new report called "Orphan Accounts" using the query that you have just created.

Reorder the columns as necessary within the Columns tab. Under Additional Data, ensure that report output formats are selected. In this case, CSV and XSLX are great options.


Under the Localization tab, ensure the column names are assigned appropriate business friendly names.

Step 3 - Report Assignment
The report has now been built, but it needs to be made available to a set of users. Within the Report Designer, navigate to Configure > Assignment > Report/Dashboard -> Entitlement and select the report you have just created.

Now assign the user community to the report. When clicking on Add, you will be presented by a list of default administrative IT Roles rather than Business Roles. If you want to assign the report to a particular Business Role (i.e. Application Manager), then click on Filter, select Type and select Business Role to see a list of roles.

Once the report has been assigned to your business role, you can now log in to the Service Centre as a user with that Business Role entitlement and you will see the "Orphan Accounts" report available for execution.

Summary
Hopefully a comprehensive definition of the schema will be documented and made available at some point in the near future. Meanwhile, hacking around the SQL Query definitions that are provided out of the box is probably going to be your best option for constructing your own reports.