Find Multiple Accounts for Sources Redux – Excel Hack


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.
  • Then on another column (Column S in my instance) I created a new header called “ServiceNow” and applied the following formula

Where

  • P2 = The column containing the “Source Accounts” value
  • $S$1 = Column Header “ServiceNow”
  • LOWER() = Used to lowercase both strings as SUBSTITUTE is case sensitive

This will give me count of number of times “ServiceNow” 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.

 

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: