September 10, 2022 at 1:11 pm
I found a nice script that gets AD user data and load to SQL, but is there any way to query all of the different OU's\DC to make sure I'm getting all of my defined users...
Not sure I'm correct terminology
I'm looking for what I would put in the ???
"OU=??,OU=???,OU=User Accounts,DC=???,DC=???,DC=???"
September 10, 2022 at 2:21 pm
there is nothing we can say to help you there - all those values are specific to your own AD structure. It can be whatever your admins decided it would be.
and it can be 3-4 levels or 12 levels.
September 10, 2022 at 5:55 pm
AD queries in SQL are quite annoying to do properly and then you get limited to the number of objects it can return before you need to then start looping around.
Think it is something like 1000 or 2000 objects back from AD and the query then fails with to many objects.
For me this kind of stuff would be done in PowerShell now as it’s much easier.
Get-AdUser from the AD module
then convertto-dbadatatable, write-dbadbdatatable from DBATools would be my route to do this easily.
September 10, 2022 at 9:50 pm
I've found that I really don't need all of that stuff for SQL Server. xp_LoginInfo and sp_validatelogins usually provide what I need. If not, then I go to the Windows Admins as ask them for more info. IIRC, I've only had to do that twice in the last decade.
Also, why not ask the people that you got the script from?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2022 at 1:19 pm
I get this error:
Get-ADUser : The supplied distinguishedName must belong to one of the following partition(s):
any ideas ???
September 14, 2022 at 1:49 pm
Can you share your powershell code?
It may be a simple thing as needing to expand out the distinguisedName as that isn't an array.
get-aduser .... | select-object -expand distinguishedName
September 14, 2022 at 3:35 pm
It does just:
Get-ADUser -SearchBase OU=??,OU=???,OU=User Accounts,DC=???,DC=???,DC=??? (stuff)
Filter * -Properties * | select -property CanonicalName,sAMAccountName,ou,distinguishedName
September 14, 2022 at 3:38 pm
example of what I do on one of my databases with regards to users and groupmembership
note the detail of formatting the dates in a very specific format as well as expanding special dates (unix epoch) onto a valid date.
$allusers=Get-ADUser -Server $domaincontroler -ErrorAction SilentlyContinue -Filter * -Properties SamAccountName,Name,UserPrincipalName,whenCreated,whenChanged,AccountExpirationDate,BadLogonCount,badPwdCount,Deleted,DisplayName,DoesNotRequirePreAuth,Enabled,isDeleted,LastBadPasswordAttempt,LockedOut,lockoutTime,PasswordExpired,PasswordLastSet,PasswordNeverExpires,PasswordNotRequired,accountExpires,Description,DistinguishedName,lastLogonTimestamp,ObjectClass,pwdLastSet,SID,Surname,ServicePrincipalNames,Company,Mail,CanonicalName,userAccountControl,Department,Division|select-object @{n='SourceDomain';e={$domain}},SamAccountName,Name,UserPrincipalName,@{n='whenCreated';e={$_.whenCreated.ToString("yyyy-MM-dd HH:mm:ss")}},@{n='whenChanged';e={$_.whenChanged.ToString("yyyy-MM-dd HH:mm:ss")}},@{n='AccountExpirationDate';e={$_.AccountExpirationDate.ToString("yyyy-MM-dd HH:mm:ss")}},BadLogonCount,badPwdCount,Deleted,DisplayName,DoesNotRequirePreAuth,Enabled,isDeleted,@{n='LastBadPasswordAttempt';e={$_.LastBadPasswordAttempt.ToString("yyyy-MM-dd HH:mm:ss")}},LockedOut,@{n='lockoutTime';e={[DATETIME]::fromFileTime($_.lockoutTime).ToString("yyyy-MM-dd HH:mm:ss")}},PasswordExpired,@{n='PasswordLastSet';e={$_.PasswordLastSet.ToString("yyyy-MM-dd HH:mm:ss")}},PasswordNeverExpires,PasswordNotRequired,@{n='accountExpires';e={[DATETIME]::fromFileTime($_.accountExpires).ToString("yyyy-MM-dd HH:mm:ss")}},Description,DistinguishedName,@{n='lastLogonTimestamp';e={[DATETIME]::fromFileTime($_.lastLogonTimestamp).ToString("yyyy-MM-dd HH:mm:ss")}},ObjectClass,@{n='pwdLastSet';e={[DATETIME]::fromFileTime($_.pwdLastSet).ToString("yyyy-MM-dd HH:mm:ss")}},SID,Surname,@{n='ServicePrincipalNames';e={$_.ServicePrincipalNames -Join "#;#"}},Company,Mail,CanonicalName,userAccountControl,Department,Division
for the membership - as it is an array and it should normaly be normalized a second retrieval is required
$AlluserGroups=Get-ADUser -Server $domaincontroler -ErrorAction SilentlyContinue -Filter * -Properties SamAccountName,MemberOf|select-object SamAccountName,@{n='MemberOf';e={$_.MemberOf -Join "#;#"}}
then a foreach on the $AlluserGroups to split the MemberOf into the individual group DistinguishedName entries.
Regarding a note mentioned above about limits of AD cmdlets - getting memberships through the Get-ADGroupMember is impossible if a group contains more than 5k members (or is it 1K?) so never use it except for ad-hoc cases.
using a searchbase as you have above is not advisable in most cases as it limits your scope greatly and you need to know exactly what to put on the search, but if you do use it you need to enclose it in DoubleQuotes - and you should also add -Filter * to it (example from MS manuals --- Get-ADUser -Filter * -SearchBase "OU=Finance,OU=UserAccounts,DC=FABRIKAM,DC=COM" )
September 14, 2022 at 4:09 pm
I get this error:
Get-ADUser : The supplied distinguishedName must belong to one of the following partition(s):
any ideas ???
You've still not explained what you intend to glean from the AD data. That might help.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply