I’d like to ask for your help in creating a Custom Report that can display Orphan Accounts — accounts that are not linked to any Identity.
My goal is to generate a report that shows all accounts in the system that are currently not associated with any Identity object. If anyone has experience with this or can point me in the right direction, I’d really appreciate your support.
Thank you!
I tried the default report named Uncorrelated Accounts Report and it doesn’t work
you dont need a custom report using HQL. you can simply search in the advance analitics with the field correlated = false. this will pull all the non-corelated accounts.
However if you are not pulling those, will need a custom report using QueryOptions to find the uncorrelated Links.
@pattabhi if the devrel team wanted AI/bot-generated responses, they could just have their bots auto-reply to posts. please stop posting chatGPT-generated responses with incorrect answers.
The report which I have used is: Uncorrelated Accounts Report
JFYI:
on the second page: Uncorrelated Accounts Parameters of the report allows to select only non-authoritative applications, so selected non authoritative application which are having orphan accounts.
Hi @pattabhi, Thanks for your code, this looks work well on my lab, but I wonder know if we can do a filter on the default xml to make it can do to all application, by not adding for the application id?
From your code I can find my application after I put it on your code
<entry key="correlatedApps">
<value>
<List>
<String>7f0001019616110f81961662c5f3000b</String>
// more 40 applications and can add or edit anytime
</List>
</value>
</entry>
I found the easy logic to fetch all applications, it works perfectly fine, I have tested against 8.4p2
The out-of-the-box “Uncorrelated Accounts Report” in SailPoint IIQ already has the capability to include all applications. The key is in how you configure its parameters.
Uncorrelated Account Parameters This is the crucial parameter for the Uncorrelated Accounts Report.
If you leave this parameter blank or unselected, the report will, by default, include ALL applications in your IdentityIQ environment.** This is the simplest way to achieve your goal without any custom coding.
Below is the Task Definition for All Applications.
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE TaskDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<TaskDefinition name="Uncorrelated Accounts Report - ALL APPLICATIONS" resultAction="Rename" subType="Identity and User Reports" type="LiveReport">
<Attributes>
<Map>
<entry key="TaskSchedule.host"/>
<entry key="disableDetail" value="false"/>
<entry key="disableHeader" value="false"/>
<entry key="disableSummary" value="false"/>
<entry key="dontEmailEmptyReport"/>
<value>
<Boolean></Boolean>
</value>
<entry key="enableCsvHeader" value="false"/>
<entry key="reportColumnOrder" value="identity, account, firstName, lastName, applicationName"/>
</Map>
</Attributes>
<Description>A detailed view of the uncorrelated user accounts in the system for all application</Description>
<Owner>
<Reference class="sailpoint.object.Identity" name="spadmin"/>
</Owner>
<Parent>
<Reference class="sailpoint.object.TaskDefinition" name="Uncorrelated Accounts Report"/>
</Parent>
</TaskDefinition>
Hi @fewthiraphat, it is unusual to have an account link that is not associated with an identity cube. Essentially, it shouldn’t happen.
Are you sure that is what you are wanting to report on?
You could try a query in your IdentityIQ database to see if you have any orphan accounts. eg: select * from identityiq.spt_link where identity_id is null;
The query will not give any result, because identity_id attribute always has values in spt_link table and it has object id values of identity.
For the correct orphan(uncorrelated) accounts use the above-mentioned report: Uncorrelated Accounts Report. these accounts are from non-authoritative source/application which are uncorrelated with Authoritative source: application identity. {refers to an account in a managed application (like Active Directory, Salesforce, Workday, etc.) that does not have a corresponding, active identity in SailPoint. Essentially, it's an account that doesn't have a known, valid owner within your organization's authoritative source (like an HR system).}
uncorrelated accounts from non-authoritative application which are basically orphan accounts, we call it as orphan accounts because these accounts not mapped with authoritative source identities, these details we can find it in spt_identity table
if we look at Identities – > Identity Correlation
Here you find the uncorrelated accounts once you select the application from the drop down.
select * from spt_identity where correlated=0;
{it includes extra records likes work groups and other objects as well}
Hi @fewthiraphat No, spt_link table doesn’t contain the uncorrelated accounts, as explained above those are available in the spt_identity table, only correlated accounts are available in the spt_link table.
it is little bit confusing, in UI(report-uncorrelated account report or Identites → Identity Correlation) it says uncorrelated accounts, in backend these objects are available in the spt_identity table.
