Forum Discussion
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.
- 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.
- 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:
- 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.
- 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.
Thank you for your response. I have done both steps 1 and 2 thus far and I am stuck on step 3. I am definately not that skilled wtih DAX and having to research each logical comparison with little luck. If there is any assistantace you can help with this, I would greately appreacite it.
- Anonymous2 years agoNot applicable
Hi Anonymous ,
In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?
Thanks for your efforts & time in advance.
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.- Anonymous2 years agoNot applicable
So here is an overall view of the data layout.
Top side we have Group Tables
Bottom side we have User TablesLeft Side is Development Environment
Right Side is Production Environment
Here is a list of the tables
All Tablesdev_user dev_group dev_group_members prod_user prod_group prod_group_members dev_user-user dev_user-organizationalPerson dev_user-person dev_user-top dev_user-msExchCertificateInformation dev_user-msExchBaseClass dev_user-msExchMultiMediaUser dev_user-msExchMailStorage dev_user-msExchCustomAttributes dev_user-msExchOmaUser dev_user-shadowAccount dev_user-posixAccount dev_user-msDS-CloudExtensions dev_user-securityPrincipal dev_user-mailRecipient dev_group-group dev_group-top dev_group-msExchIMRecipient dev_group-msExchBaseClass dev_group-msExchCustomAttributes dev_group-msExchMailStorage dev_group-posixGroup dev_group-mailRecipient dev_group-securityPrincipal prod_user-user prod_user-organizationalPerson prod_user-person prod_user-top prod_user-msExCertificateInformation prod_user-msExchBaseClass prod_user-msExchMultiMediaUser prod_user-msExchMailStorage prod_user-msExchCustomAttributes prod_user-msExchOmaUser prod_user-shadowAccount prod_user-posixAccount prod_user-msDS-CloudExtensions prod_user-securityPrincipal prod_user-mailRecipient prod_group-group prod_group-top prod_group-msExchIMRecipient prod_group-msExchBaseClass prod_group-msExchCustomAttributes prod_group-msExchMailStorage prod_group-posixGroup prod_group-mailRecipient prod_group-securityPrincipal
Every User tables contains the Users DisplayName and DistinguishedName
Every Group table contains the Group DisplayName and DistinguishedName
I also created List tables so that I can narrow down work to just users or just groups or a specific environment.
DynamicTablesProduction Tables Development User Tables Development Group Tables Production User Tables Production Group Tables Development Tables All Tables DynamicTables
I have created relationships from each
prod_group-*** and prod_user-*** to the corresponding dev_group-*** and dev_user-***
each ***_group-*** is connected to ***_group
each ***_user-*** is connected to ***_user
***_group-members are connected to ***_group and ***_user
All relationships except the following are inactiveAll relationships are linked to column distinguishedName
I was able to create a measure that would provide me with a comparison of the users in dev and prod using this:Comparison = VAR BothExist = COUNTROWS( INTERSECT( VALUES('dev_user-user'[distinguishedName]), VALUES('prod_user-user'[distinguishedName]) ) ) VAR OnlyInProd = COUNTROWS( EXCEPT( VALUES('prod_user-user'[distinguishedName]), VALUES('dev_user-user'[distinguishedName]) ) ) VAR OnlyInDev = COUNTROWS( EXCEPT( VALUES('dev_user-user'[distinguishedName]), VALUES('prod_user-user'[distinguishedName]) ) ) RETURN CONCATENATEX ( { "Both Exist: " & FORMAT(BothExist, "#,##0"), "Only in Prod: " & FORMAT(OnlyInProd, "#,##0"), "Only in Dev: " & FORMAT(OnlyInDev, "#,##0") }, [Value], UNICHAR (10) )
I thought I could use the DAX query to get me more information on all the column namesEVALUATE SUMMARIZE(COLUMNSTATISTICS(),[Table Name],[Column Name])However, its to large of a query and excedes 1024.
I tried to break it down into their perspective data clustersEVALUATE VAR DevUsers = SUMMARIZE( FILTER( COLUMNSTATISTICS(), LEFT([Table Name], 9) = "dev_user-" ), [Table Name], [Column Name] ) VAR ProdUsers = SUMMARIZE( FILTER( COLUMNSTATISTICS(), LEFT([Table Name], 10) = "prod_user-" ), [Table Name], [Column Name] ) VAR DevGroups = SUMMARIZE( FILTER( COLUMNSTATISTICS(), LEFT([Table Name], 10) = "dev_group-" ), [Table Name], [Column Name] ) VAR ProdGroups = SUMMARIZE( FILTER( COLUMNSTATISTICS(), LEFT([Table Name], 11) = "prod_group-" ), [Table Name], [Column Name] ) RETURN UNION(DevUsers, ProdUsers, DevGroups, ProdGroups)But it still exceeded 1024mb. I even tried to granuarly break them down blocking out 5 tables at a time, and it still exceded.
So now I am left with trying to figure out a way to compare 2 tables at a time, however Im still not sure how to accomplish this efficiently and not have to write multiple queries and measures per pair of tables.
Again the end goal here is to be able to determine the data that is missing in Development Environment that is in Production.