Braindump in Progress...

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

How To Configure gMSA in AD Connector for ISC

Introduction

So been playing around with the newly release capability of our AD connector to use Group Managed Service Accounts (gMSA). Now, I am no AD expert and this article will evolve but this is a quick test I have done in my homelab AD setup. 

NOTE: These are not official steps and I am not an AD expert. Please test in your SB environment and undertand and adjust each command per Microsoft and your own AD deployment guidelines.

Infrastructure

  • AD Domain: abc.local
  • Functional Level: Windows Server 2016
  • Domain Controller (one): dc1.abc.local
  • IQService: Running on domain controller “dc1”, Non TLS on Port 6050

Steps

  • Generate a root key for it to be available immediately 
  • Create a new managed service account called GMSA_ACCOUNT. DC1 is part of the “IQService Servers” group so we allow computers in that to retrieve password. We can also just mention the server directly if not in a group as serverName$

This will show up the account

  • Add UPN for the account as its missing at this stage by manually editing the attribute in Attribute Editor

  • Run the Install-ADServiceAccount account command

    • Run the following command to purge Kerberos tickets associated with the Local System Account
    • Add the account as a local Administrator to the machine running IQService so that it can start the service

    • Run the Set-ADServiceAccount for it to allow to retrieve its own password

      See Additional Notes to pass IQService server names as well and needs to be passed as a list in a single command.

      • On the IQService service goto properties -> Log On -> Change this to use the gMSA account and click on OK. You can follow my guide on how to install IQService as well from scratch if you need. 

      • Give the gMSA account full rights to the IQService Folder

      • Give the gMSA account Account Operators right or appropriate permissions to do its tasks in AD.
      • Restart IQService (or server for good measure).
      • Now setup a AD source the normal way but slight changes for Domain Settings page (Using 389 SASL for gMSA)

      • On the IQService page configure the non TLS port

      • Give it a test connection and should be all GREEN 🙂 

      Additional Notes

      • You can setup TLS Client Auth with IQService by just creating a local account in the IQService box with a password and adding it to configuration and to IQService via IQService.exe -a abc\gsmatesttls option

      • The Set-ADServiceAccount command property -PrincipalsAllowedToRetrieveManagedPassword can be passed with multiple server names and account names. But we need to pass them as a list. Otherwise it seems to wipe the previous servers and keep the last one

      In the above, we are allowing the GMSA_ACCOUNT$ to retrieve its own password and also allowing multiple IQService servers to use same account to retrieve it (in previous example we had used a group “IQService Servers”)

      Conclusion

      By following these steps, you can successfully configure gMSA in AD Connector for ISC, ensuring better security and management of service accounts. Test in your environment and adjust settings as needed.

      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