So after doing the last post on how to find multiple accounts from a single source via API, I was asked by few people and one my clients to see if there is an easy way to do this.
I thought of my trusted Excel to find an easier way to do so. Here are the steps
- Generate Identities Report via Admin -> Identities -> Identity List UI and download CSV
- Open the CSV in Excel and look at Column “Source Accounts” ( Column P when I generated it). You will see some data like this. In this example I have a duplicate ServiceNow account for this user. He also has 1 x Workday, 1 x IDN cube and 1 x Okta account.
|
1 |
22222 (Workday [source]), 22222 (IdentityNow), user.name@email.com (Okta [source]), user.name@email.com (ServiceNow [source]), user.name2@email.com (ServiceNow [source]) |
- Then on another column (Column S in my instance) I created a new header called “ServiceNow [source]” and applied the following formula
|
1 |
=(LEN(P2)-LEN(SUBSTITUTE(LOWER(P2),LOWER($S$1),"")))/LEN($S$1) |
Where
- P2 = The column containing the “Source Accounts” value
- $S$1 = Column Header “ServiceNow [source]”
- LOWER() = Used to lowercase both strings as SUBSTITUTE is case sensitive
This will give me count of number of times “ServiceNow [source]” repeats in that line of text.
Done.
You can filter and find the rows you are interested in and give you all the users who have multiple accounts for a single source. You can expand this to other sources like I did above and create multiple columns.