Jump to content
Sign in to follow this  

SQL - Attempting to Select one DC

Recommended Posts

So, I have a really cool and useful script I found posted here and it works fantastically for a majority of sites. Unfortunately it doesn't yet work at all sites because at some places there is more than one domain controller. At those sites, only one of the multi-DC's has the AD Domain Naming Master role, but also only one has the NTDS role - so I am confused as to why the script cannot correctly identity this and select just 1 DC from 2 or 3 when c.RoleName = "AD Domain Naming Master"

Is there an easier way to single out systems based on roles installed?


OG post for more info

Share this post

Link to post
Share on other sites
Posted (edited)
On 6/15/2020 at 5:12 PM, WesleyNZ said:

Easy, just add "LIMIT 1" to the end of the SQL (no quotes)

So I did this, but it still returns 'no primary DC' sense the first statement comes back with more than one domain controller.

SELECT COUNT(a.ComputerId) FROM computerroledefinitions as a
join computers as b on a.ComputerId = b.ComputerID
join roledefinitions as c on a.RoleDefinitionId = c.RoleDefinitionIdwhere 
c.RoleName = "AD Domain Naming Master" AND b.ClientID = %clientid% LIMIT 1;

I guess I don't fully understand how this statement works. To me it looks like it singles out a client container from the computers location, then searches for a role definition of "AD Domain Naming Master" in that client ID container, then returns the number it finds; finally if that number is not 1, it comes back with "Primary DC Not Found or Multiple Found. Ending Script". This role is unique to our primary DC's and it still fails unless there is only 1 DC in a site container with the workstation. I think this is a pre-check as the next section actually selects it, and uses that Limit 1.


SELECT DISTINCT REPLACE(domain,'DC:','') FROM computers WHERE domain LIKE 'DC:%' AND clientid = @clientid@ LIMIT 1

EDIT: Solved this one . . . turns out we had a Lab DC setup on the network in a test container. Works!

Edited by Struggles

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...