Problem
When employees or contractors are terminated or marked as inactive within an organization, their downstream application accounts (Links) often remain active in IIQ due to some provisioning failures or exceptions. This discrepancy represents a severe security and compliance risk, known as orphaned access or dormant account accumulation. Administrators lack a native, unified dashboard to easily track and filter active target system accounts bound to inactive identities across specific application footprints or worker categories.
Solution
- Database Approach
For rapid database analysis outside of the UI, use an explicit internal join. Sample Queries:
SELECT
idObj.name AS "InactiveIdentityName",
idObj.display_name AS "DisplayName",
idObj.email AS "Email",
appObj.name AS "AppName",
linkObj.native_identity AS "ActiveAccountID"
FROM spt_link linkObj
JOIN spt_application appObj ON linkObj.application = appObj.id
JOIN spt_identity idObj ON linkObj.identity_id = idObj.id
WHERE linkObj.iiq_disabled = 0
AND idObj.inactive = 1
Note: Please modify the query as per your environment.
Sample Output:
- Custom Report
Use the below report to generate a report from the UI and you can also schedule it. Please make changes to it as per your requirement.
Form:
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE Form PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<Form name="Custom-UserApplicationStatusReportForm" type="Report">
<Section columns="3" label="Report Properties" name="customProperties">
<Field displayName="Inactive" displayType="combobox" name="idStatus" type="String" value="ref:idStatus">
<AllowedValuesDefinition>
<Value>
<List>
<String>True</String>
<String>False</String>
</List>
</Value>
</AllowedValuesDefinition>
</Field>
<Field displayName="Employee Type" displayType="combobox" name="empType" type="String" value="ref:empType">
<AllowedValuesDefinition>
<Value>
<List>
<String>Employee</String>
<String>Contractor</String>
</List>
</Value>
</AllowedValuesDefinition>
</Field>
<Field displayName="Account Status" displayType="combobox" name="accountStatus" type="String" value="ref:accountStatus">
<AllowedValuesDefinition>
<Value>
<List>
<String>Active</String>
<String>Disable</String>
<String>Locked</String>
</List>
</Value>
</AllowedValuesDefinition>
</Field>
<Field displayName="Application" name="appName" type="Application" value="ref:appName"/>
</Section>
</Form>
TaskDefinition (Report):
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE TaskDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<TaskDefinition executor="sailpoint.reporting.LiveReportExecutor" name="Custom-Report-UserApplicationStatus" progressMode="Percentage" resultAction="Rename" subType="Custom" template="true" type="LiveReport">
<Attributes>
<Map>
<entry key="report">
<value>
<LiveReport title="Custom-Report-UserApplicationStatus">
<DataSource objectType="Link" type="Filter">
<QueryParameters>
<Parameter argument="empType" property="identity.type"/>
<Parameter argument="idStatus">
<QueryScript>
<Source>
import sailpoint.object.Filter;
if(value!=null && value.equalsIgnoreCase("True")){
Filter f = Filter.eq("identity.inactive",true);
queryOptions.addFilter(f);
}
else if(value!=null && value.equalsIgnoreCase("False")){
Filter f = Filter.eq("identity.inactive",false);
queryOptions.addFilter(f);
}
log.error("queryOptions1::"+queryOptions);
return queryOptions;
</Source>
</QueryScript>
</Parameter>
<Parameter argument="appName" property="application.id"/>
<Parameter argument="accountStatus">
<QueryScript>
<Source>
import sailpoint.object.Filter;
if(value!=null && value.equalsIgnoreCase("Active")){
Filter f = Filter.eq("iiqDisabled",false);
queryOptions.addFilter(f);
}
else if(value!=null && value.equalsIgnoreCase("Disable")){
Filter f = Filter.eq("iiqDisabled",true);
queryOptions.addFilter(f);
}
else if(value!=null && value.equalsIgnoreCase("Locked")){
Filter f = Filter.eq("iiqLocked",true);
queryOptions.addFilter(f);
}
log.error("queryOptions::"+queryOptions);
return queryOptions;
</Source>
</QueryScript>
</Parameter>
</QueryParameters>
</DataSource>
<ReportForm>
<Reference class="sailpoint.object.Form" name="Custom-UserApplicationStatusReportForm"/>
</ReportForm>
<Columns>
<ReportColumnConfig field="IdentityName" header="Employee ID" property="identity.name" sortable="true" width="110"/>
<ReportColumnConfig field="firstname" header="First Name" property="identity.firstname" sortable="true" width="110"/>
<ReportColumnConfig field="lastname" header="Last Name" property="identity.lastname" sortable="true" width="110"/>
<ReportColumnConfig field="ApplicationName" header="Application Name" property="application.name" sortable="true" width="110"/>
<ReportColumnConfig field="AccountName" header="Account Name" property="nativeIdentity" sortable="true" width="110"/>
<ReportColumnConfig field="email" header="Email" property="identity.email" sortable="true" width="110"/>
<ReportColumnConfig field="type" header="Employee Type" property="identity.type" sortable="true" width="110"/>
<ReportColumnConfig field="inactive" header="Inactive" property="identity.inactive" sortable="true" width="110"/>
<ReportColumnConfig field="linkStatus" header="Account Status" property="attributes" sortable="true" width="110">
<RenderScript>
<Source>
String finalValue = "Active";
if(value!=null){
if(value.get("IIQDisabled")!=null){
if(Boolean.valueOf(value.get("IIQDisabled"))) {
finalValue = "Disabled";
}
}
else if(value.get("IIQLocked")!=null){
if(Boolean.valueOf(value.get("IIQLocked"))) {
finalValue = "Locked";
}
}
}
return finalValue;
</Source>
</RenderScript>
</ReportColumnConfig>
</Columns>
</LiveReport>
</value>
</entry>
</Map>
</Attributes>
<Description>Report to generate a report identities with their inactive status and application account status.</Description>
<RequiredRights>
<Reference class="sailpoint.object.SPRight" name="FullAccessReport"/>
</RequiredRights>
<Signature>
<Inputs>
<Argument name="empType" type="String"/>
<Argument name="idStatus" type="String"/>
<Argument name="accountStatus" type="String"/>
<Argument multi="true" name="appName" type="sailpoint.object.Application"/>
</Inputs>
</Signature>
</TaskDefinition>
Sample Output:
Please try and let me know for any queries.