IDN101

Refined Excel Hack to Find Multiple Accounts for Sources – Improved Formula

Hi All

Happy New Year, Everyone!

It’s been a while since my last post, and I’m excited to return with an update. Back in 2021, I wrote an article on how to find multiple accounts in sources via Excel, which received a great deal of attention. Since then, the UI has changed, and I’ve also come up with a better formula to make the process even easier.

Here are the updated steps to follow:

1. Generate the Identities Report:

Start by navigating to Admin -> Identity Management -> Identities UI and download the CSV file by clicking the Export button.

2. Open the CSV in Excel:

Once you open the CSV file, look for the column labeled “Source Accounts” (Column P in my case). You’ll see data in a format similar to this:

In this example, the user has multiple accounts across various sources: Workday, IdentityNow, Okta, and ServiceNow, with a duplicate ServiceNow account.

3. Count Occurrences of a Specific Source:

In another column (Column S in my case), I created a new header called “ServiceNow” and applied the following formula to count the occurrences of ServiceNow [source]:

Explanation:

  • P2 = The cell containing the “Source Accounts” values (Column P in this case).

  • $S$1 = The cell containing the source name you want to count (in this case, “ServiceNow”).

  • “(“&$S$1&” [source”: This part ensures that the target source name is preceded by an opening parenthesis “(“. This is critical because it avoids matching partial strings, like “Prod ServiceNow”. The “[source” part ensures that we’re only matching the source in the format “(SourceName [source”. The absence of the closing “]” accounts for the fact that the source may have an additional “-xxxx” at the end, such as “[source-3226]”

  • SUBSTITUTE(P2, “(” & $S$1 & ” [source”, “”): This function removes all occurrences of “(<sourcename> [source”from the original “Source Accounts” string. By comparing the length of the original string with the modified string, we can count how many times the target source name appears.

  • LEN(P2): This calculates the length of the original “Source Accounts” value in P2.

  • LEN(SUBSTITUTE(P2, “(” & $S$1 & ” [source”, “”)): This calculates the length of the modified string, where all occurrences of “(<source> [source” are removed.

  • LEN(“(” & $S$1 & ” [source”): This calculates the length of the target source string (e.g., “(ServiceNow [source”). This value is used to divide the difference in lengths, ensuring we get an accurate count of occurrences.

The result of this formula will give you the count of how many times “ServiceNow [source” appears in that line of text.

4. Filter and Analyze:

Once the formula is applied, you can filter the data to find users who have multiple accounts for the same source. For example, if the count for ServiceNow is greater than 1, you know the user has duplicate accounts for this source.

5. Expand to Other Sources:

You can easily expand this method to track multiple sources. Just add new columns for each source (e.g., “Okta”, “Workday”, etc.) and apply the formula for each one, referencing the corresponding source name in each column header.

Final Thoughts:

With this updated formula, you can efficiently track and count accounts across multiple sources, making it easier to identify users with multiple accounts for the same source. This formula works well in dynamic environments and can be adapted to other sources as needed.

I hope this helps improve your Excel workflow, and happy reporting!!!

By Piyush Khandelwal, ago

IDN + Transforms + VS Code + Snippets = Lightning Speed #IDN101

Hiya Folks

Happy New Year!!!  

I have written a few posts before on how to write nested transforms and about a few new transform types available. But it still look like a tedious task when writing a transform – especially a complex one. It is as powerful as its confusing sometimes. I still don’t have full grip on the capabilities or the possibilities I can achieve without the need of rules. 

I love VS Code and especially its features and plugins. I use it extensively and also advocate the same to all my colleagues and clients for doing JSON and CSV files with IDN (and java coding obviously).

Few years ago one of my good mate Thomas Bui had shown me an interesting way to not need to remember syntax of each transforms while writing it. So credits to him.

Here I have built a package and hopefully show you how to deploy it in VS code and using the Snippet feature quickly write transforms with less errors.

It’s pretty simple. View the video I have made in VS Code (Works on Windows / Mac) . Please click on the gif to enlarge it. The embedded one is not rendering properly for some reason.

You will see how quickly I wrote a firstValid transform and replaced few lines with accountAttributes transforms (nested) and created a “Get New Dept” Transforms in couple of sec.

Steps are as below

  1. Open “Configure User Snippets” in VS Code Preferences
  2. Click on “New Global Snippets File”
  3. Call it say “transforms”
  4. In the file opened, replace the entire content with below code snippet
  5. Save and close the file 
  6. Now start typing the name of the transform (e.g. firstValid) and it will show up.
  7. Press Enter and it will show you the code

That’s it

Hopefully this was helpful and makes it easy for you to write transforms for IDN. Feel free to expand the snippet by adding your own shortcuts of complex transforms or code which you use often and feel free to share it below or the article

Cheers!!!

 

By Piyush Khandelwal, ago

How to upload Connector Rules into IDN via API

So I did a post couple of days ago that now we are allowed to upload some rule types via API

Here is a quick guide on how to do so. In this example, I will take a very basic BuildMap rule. 

Previously we would submit the rule like above to ES team to upload. Now you just need to take the code and upload yourself.

Now the real trick – You need to escape the actual java code else you will not be able to upload it and postman will show errors.

So head down to https://www.freeformatter.com/java-dotnet-escape.html and paste the code part of above. There are other such websites or can be some easier local method in your editor.

You will get some output like

Rest is then easy as per the API links

You should get a 201 Created and see a similar output

That is it. You should be able to see and use this rule now on your source. 

Please remember to follow the IDN Rule Guide on what is allowed and what is not.

And if you want you can reverse the process by getting existing rules via API, unescape it via the URL above and get the neat looking java code.

By Piyush Khandelwal, ago

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 [source]” and applied the following formula

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.

 

By Piyush Khandelwal, ago

Find Multiple Accounts from Sources #IDN101

Currently there is no easy way in our default search UI to find all identities who have more than one accounts from a single source. There are other ways like doing account link search or via accounts CSV etc. 

But there is a way to do find them (not pretty but still helpful) via search aggregation API. I found this somewhat easier if the result is small. If its a large set, you can always parse it to you liking. I am no coder 🙂 

Here is the call

The result will be something like this

Here you can see multiple accounts for a user from Azure AD

Similarly another user having duplicate accounts in Google Suite and Active Directory.

You can’t filter to get particular sources only but at least this will give you all users with more than one account from all sources.

Hope this helps!!! 

By Piyush Khandelwal, ago