Get AD data and load to SQL

  • 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=???"

  • 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.

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I get this error:

    Get-ADUser : The supplied distinguishedName must belong to one of the following partition(s):

    any ideas ???

  • 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

  • It does just:

     

    Get-ADUser -SearchBase  OU=??,OU=???,OU=User Accounts,DC=???,DC=???,DC=??? (stuff)

    Filter * -Properties * | select -property CanonicalName,sAMAccountName,ou,distinguishedName

  • 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" )

     

  • Bruin wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply