ProTip

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

PSA: Generic Cloud Rule now called Transform Rule

Hi Folks,

It’s been a while since my last post, but I have an important update for you all.

Public Service Announcement (PSA):

The latest release of Rule Validator 3.0.38 introduces a new enforcement: all rule types must now include a tag. If you attempt to run your previous Generic Rule against this validator, you’ll encounter the following error:

To resolve this, you need to make two simple changes to your rule:

  1. Add a type to your rule tag:

Update your rule tag:

          2. Rename your file:

    Change the filename:

    That’s it! Your rule should now pass the validator without any issues.

    Note:

    • This change does not affect the functionality of your rule. Your rule logic will continue to work as before.
    • If you edit an existing Generic rule in your tenant and submit it, you must change it to the Transform type as described above; otherwise, it will not pass the latest rule validator.
    • The rule documentation will be updated soon to reflect these changes.

    Thank you for your attention, and happy coding!

    By Piyush Khandelwal, ago

    Enhancing Logging Efficiency in IDN: Part Two

    I’m delighted to follow up on my previous article on Optimising Log Retrieval in IDN , which garnered positive feedback. In this installment, we’re taking our approach to the next level.

    In the context of our internal cloud system, log lines may not arrive in proper order. Consequently, when these log lines are retrieved and presented, users often face the challenge of manually rearranging them. Moreover, if a logging line is executed multiple times (such as in a loop), managing multiple entries can be cumbersome, making it difficult to discern the chronological sequence.

    Let’s revisit one of the examples from the prior article to illustrate how log lines are currently written:

    By adopting a slight modification to this method, we can write multiple log lines with a standardized prefix to easily identify the associated identity.

    Taking it a step further, we introduce a logNumber and encapsulate the entire logging structure into a method that is repeatedly executed:

    Key differences from the previous code include the introduction of logNumber as a newly initiated counter and the implementation of the logMessage method, which is executed on every line, printing the logNumber and incrementing it accordingly.

    The result of this modified code is a more streamlined output, exemplified as follows:

    While I won’t provide a full code execution example here, it’s evident how this approach simplifies handling logs in loops or complex rules with multiple log lines, making it easier to decipher the order of execution.

    To summarize the main advantages:

    1. Single method to streamline the logging mechanism.
    2. logNumber facilitates pattern-following, making it easy to discern the execution order.
    3. A single instance of log.error, easily adaptable to log.info or other log levels without the need to edit each log line individually.

    I collaborated on this work with my colleague Kenny Li, a Senior Solution Architect at SailPoint, with whom we together transformed my individual works into an easily applicable method.

    As this will likely be my last blog for the year, I wish you all a Merry Christmas and a Happy New Year!!!

    By Piyush Khandelwal, ago

    Optimizing Log Retrieval in IDN Cloud Rules

    When it comes to extracting logs from cloud rules, our usual route involves reaching out to support or ES. However, if these logs lack proper formatting, sifting through them for a specific user run can be quite challenging.

    Here’s a method I employ to streamline the tracking of logs for individual runs, making it easier for you to obtain them via the support team.

    Log Prefixing for Enhanced Clarity

    To facilitate the process, each rule type has access to some identity data, which we utilize as a logPrefix in every log line within the rule.

    While there may be alternative approaches for various rule types, I’ve outlined my preferred methods below.

    IdentityAttribute / AttributeGenerator / AttributeGeneratorFromTemplate / Generic Rule

    For rules with access to the identity object, you can create a logPrefix attribute to append to each log line as follows:

    Now, you can use this logPrefix to append to every log statement, like so:

    BeforeProvisioning Rule

    For rules with access to the plan object, use the following approach:

    Now, you can incorporate the logPrefix in the log lines as mentioned earlier.

    Correlation Rule

    When dealing with the Correlation Rule and access to the account object, fetch a primary identifier (e.g., STAFF_NUMBER) for enhanced identification:

    ManagerCorrelation Rule

    For the ManagerCorrelation Rule and access to the link object, retrieve a primary key (e.g., Userid) for better association:

    BuildMap Rule

    Finally, for the BuildMap Rule and access to cols and record of the accounts, fetch an attribute (e.g., EMP_NO) via a map for logging:

    Streamlined Output Request

    When requesting logs, provide the formatted logPrefix, the organization name, and the timeframe. For example:

     Generate sAMAccountName - [EMP001]

    The logs, once fetched, will be neatly formatted and easily identifiable, even in scenarios where the rule runs for thousands of users but you need information about just one user for troubleshooting:

    I hope this aids you on your rule journey! If you have any questions, feel free to reach out.

    By Piyush Khandelwal, ago

    Fix DNS issue for Domains ending with .local and SailPoint VA

    So I came across a client who has a domain ending with .local and stumbled across a weird issue with our SailPoint Linux VAs.

    Now, I am no DNS / Linux expert and not saying that you will have this issue if you have a .local domain. So YMMY

    The VA could do nslookup on the domain but couldn’t do ping / openssl and other such commands. Thus  couldn’t connect to the server via domain name and SSL verification broke and connector didn’t work. 

    For example, the AD domain was call “abc.local” and after the VA setup, it could do a nslookup but couldn’t do openssl command. Which means the connector couldn’t connect via domain name and verify SSL certificate. Workaround was to connect via IP address but then the certificate didn’t contain IP address and thus SSL config didn’t work. This also affected all other servers we need to connect which are domain joined and had a .local in the end.

    After doing some research, I found many articles which pointed to /etc/nsswitch.conf file and one particular line 

    hosts: files usrfiles resolve [!UNAVAIL=return] myhostname dns

    This line needs to be changed to (remove [!UNAVAIL=return])

    hosts: files usrfiles resolve myhostname dns

    I won’t go into details on why and what it does – plenty of articles explaining DNS and Linux interactions – I am no expert on this.

    Now previously we couldn’t edit the file directly in our VA due to the locked down nature of it. So I worked with our internal team and have finally got a fix out if you are in this situation. 

    For this to work charon version needs to be atleast 1624. You can check your charon version by running the following command

    sudo docker images | grep charon

    Note: If you don’t have the version, don’t worry – will get rolled out per standard updates in coming months.

    Fix

    Run the following commands

    To revert the changes

    That should re-create the original symlink.

    NOTE: A wrong edit to this file can cause DOS. So please do be careful and test it in SB and have direct access to the box if needed. Please be careful and test this out before prod implementation and have direct access to VA to restore file if needed.

    By Piyush Khandelwal, ago