Compare Active Directory Environments Users/Groups/Group_Memberships

Hi Anonymous ,

Please try below steps:

Steps1: Data Preparation
Given the complexity and volume of the data you're dealing with, the first step is to ensure your data is well-prepared for comparison. This involves structuring your data in a way that facilitates easy comparison despite the differences in domain names. 

1. Normalize Domain-Specific Attributes: Since OU, DN, and CN have slight differences due to different domains, create calculated columns in Power BI to normalize these attributes. For example, you can create a new column that extracts the last part of the DN that is common across both environments.
2. Consolidate Data: Ensure that your data tables (dev_group, dev_group_members, dev_user, prod_group, prod_group_members, prod_user) are loaded into Power BI. You may need to transform these tables to have a consistent structure for comparison. Use Power Query Editor in Power BI for this purpose.

Steps2: Creating Relationships
Establish relationships between your tables. Since you're dealing with users and groups across two environments, you'll likely need to create relationships based on normalized attributes that are common between development and production.

Steps3: Comparison Logic
To compare the environments:

1. Create Measures for Comparison: Use DAX to create measures that identify discrepancies between the two environments. For example, you can create a measure that counts the number of users in a group in the production environment and compares it with the count in the development environment.
2. Visualize Differences: Use conditional formatting and visuals like tables and bar charts to highlight differences between the two environments. This will make it easier to identify where discrepancies lie.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.