Forum Discussion
Compare Active Directory Environments Users/Groups/Group_Memberships
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.
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 Tables
| dev_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.
DynamicTables
| Production 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 inactive
All 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 names
EVALUATE
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 clusters
EVALUATE
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.