Custom role report via SQL (business to IT to entitlement)

[#1]

We were unable to find a way to get a report that showed the Business Role to IT Role to Entitlement in one view. and in an easy to search/consume format.

I was able to create my own using SQL... no warranties here, so please verify your results!

To use this --- change the last line to correspond with your business role type/name (if you have multiple defined).

Below is configured for the out of box "business" type:

SELECT        spt_bundle.type AS [Role Type], spt_bundle.display_name AS [Business Role], spt_bundle.name AS [(Business Role Name)],
                         spt_bundle_1.display_name AS [IT Role], spt_bundle_1.name AS [(IT Role Name)], spt_profile_constraints.elt AS [Direct Entitlement]
FROM            spt_bundle INNER JOIN
                         spt_bundle_requirements ON spt_bundle.id = spt_bundle_requirements.bundle INNER JOIN
                         spt_bundle AS spt_bundle_1 ON spt_bundle_requirements.child = spt_bundle_1.id INNER JOIN
                         spt_profile ON spt_bundle_1.id = spt_profile.bundle_id INNER JOIN
                         spt_profile_constraints ON spt_profile.id = spt_profile_constraints.profile
WHERE        (spt_bundle.type = N'business')

[tylerduffy]

Great post! Quick note -- if you are on an Oracle DB... you will need to make the following updates:

1) replace brackets with double quotes

2) Remove the "AS" keyword from the inner join section

See below:

SELECT spt_bundle.type AS "Role Type", spt_bundle.display_name AS "Business Role", spt_bundle.name AS "(Business Role Name)", spt_bundle_1.display_name AS "IT Role", spt_bundle_1.name AS "(IT Role Name)", spt_profile_constraints.elt AS "Direct Entitlement"

FROM spt_bundle INNER JOIN
spt_bundle_requirements ON spt_bundle.id = spt_bundle_requirements.bundle INNER JOIN
spt_bundle spt_bundle_1 ON spt_bundle_requirements.child = spt_bundle_1.id INNER JOIN
spt_profile ON spt_bundle_1.id = spt_profile.bundle_id INNER JOIN
spt_profile_constraints ON spt_profile.id = spt_profile_constraints.profile

WHERE (spt_bundle.type = N'business')

[SharonW]

Thank you for this.  A further question...

Where we use "spt_profile_constraints.elt" - this gives a concatenation of all of the entitlements for the given combination of Business Role and IT Role. 

Is there any way to return 1 row for each combination of Business Role, IT Role and Entitlement?  (i.e. where an IT role has many entitlements). 

I've found the spt_managed_attribute table, which appears to show the entitlements, but am wondering how you join to this table (if that is the appropriate way to do this). 

[keith_hicks]

It's a heavy option, but you can use the below

LEFT JOIN spt_managed_attribute ON spt_profile_constraints.ELT LIKE '%"' + spt_managed_attribute.VALUE + '"%'

[russellreimann-lm]

You can do this with a custom report. Get the business roles as the data source, then use custom columns to pull the business role identity selectors and related IT role entitlements. All the entitlements are listed in the same row as the business role, and the IT role names are not specifically listed. To have one row per entitlement, maybe this can be flipped where the entitlements are the data source, filtered by their inclusion in IT roles, then the custom columns can get the role information.

<LiveReport title="Combined Role Report">
    <DataSource objectType="Bundle" type="Filter">
        <QueryParameters>
            <Parameter defaultValue="business" property="type" valueClass="string" />
        </QueryParameters>
    </DataSource>
    <Columns>
        <ReportColumnConfig field="displayName" header="Role Name" property="displayName" sortable="true" width="110" />
        <ReportColumnConfig field="selector" header="Job Functions" property="id" sortable="true" width="110">
            <RenderScript>
                <Source>
                    import sailpoint.object.*;
                    import sailpoint.object.IdentitySelector.MatchExpression;
                    Bundle bundle = null;
                    try{
                      bundle = context.getObjectById(Bundle.class,value);
                      IdentitySelector sel = bundle.getSelector();
                      if (null != sel){
                        CompoundFilter filter = sel.getFilter();
                        if (filter!=null){
                          return filter.render();
                        }
    
                        MatchExpression me = sel.getMatchExpression();
                        if (me!=null){
                          return me.render();
                        }
    
                        GroupDefinition pop = sel.getPopulation();
                        if (pop != null){
                          Filter f = pop.getFilter();
                          if (f!=null){
                            return f.getExpression();
                          }
                        }  
    
                        Rule rule = sel.getRule();
                        if (rule!=null){
                          return rule.getSource();
                        }
    
                        Script script = sel.getScript();
                        if (script!=null){
                          return script.getSource();
                        }
  
                        return sel.toXml();
                      }
                      return "no selector found";
                    }
                    catch (Exception e){
                      if (null != bundle &amp;&amp; null != bundle.getName()){
                        return "error getting selector for " + bundle.getName() + ": " + e.getMessage();
                      }
                      else{
                        return "null role";
                      }
                    }
                  </Source>
            </RenderScript>
        </ReportColumnConfig>
        <ReportColumnConfig field="entitlementProfiles" header="Privileges" property="id" sortable="true" width="110">
            <RenderScript>
                <Source>
                        import sailpoint.object.*;
                        Bundle bundle = null;
                        try{
                            bundle = context.getObjectById(Bundle.class,value);
                            Collection requirements = bundle.getFlattenedRequirements();
                            List profileStrings = new ArrayList();
                            for (Bundle itRole: requirements){
                                List profiles = itRole.getProfiles();
                                for (Profile p: profiles){
                                    String applicationName = p.getApplication().getName();
                                    List filters = p.getConstraints();
                                    for (Filter f: filters){
                                        profileStrings.add(applicationName + ":" + f.getExpression());
                                    }
                                }
                            }
                            return String.join("\n",profileStrings);
                        }
                        catch (Exception e){
                            if (null != bundle &amp;&amp; null != bundle.getName()){
                                return "error getting profiles for " + bundle.getName() + ": " + e.getMessage();
                            }
                            else{
                                return "null role";
                            }
                        }
                    </Source>
            </RenderScript>
        </ReportColumnConfig>
    </Columns>
</LiveReport>