PowerShell To Get Active Directory Users And Groups into SQL

  • Lowell

    SSC Guru

    Points: 323444

    Ray Herring (4/1/2016)


    andre.quitta (3/31/2016)


    when I tried to run the code in Powershell, I got the following error.

    [font="Courier New"]Import-Module : The specified module 'ActiveDirectory' was not loaded because no valid module file was found in any module directory.

    At line:3 char:1

    + Import-Module ActiveDirectory

    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : ResourceUnavailable: (ActiveDirectory:String) [Import-Module], FileNotFoundException

    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

    [/font]

    So I tried to download the module ActiveDirectory from this site and got a page not found. Can someone suggest a download location for this module?

    [font="Courier New"]https://blogs.msdn.microsoft.com/rkramesh/2012/01/17/how-to-add-active-directory-module-in-powershell-in-windows-7/[/font]

    Thanks much

    I receive a similar error when I try to Import-Module ActiveDirectory.

    WARNING: Error initializing default drive: 'Unable to find a default server with Active Directory Web Services running.'.

    What do I need to set up to resolve this issue?

    this error: Import-Module : The specified module 'ActiveDirectory' was not loaded

    is definitely this is due to the Active Directory module not being installed; it's not in a typical workstation configuration(win7/8/10).

    the feature would be part of a Server installation, but even then, you might have to go to program features and add it; i have not had to do that myself; it seems all my 208R2 operating systems and above have it enabled by default.

    grab the remote administration tools here:

    https://www.microsoft.com/en-us/download/details.aspx?id=7887

    and this link has step by step screenshots of adding the windows feature :

    https://www.itsupportguides.com/windows-7/windows-7-how-to-install-the-active-directory-users-and-computers-tools/

    this error:WARNING: Error initializing default drive: 'Unable to find a default server with Active Directory is something i might expect if you ran the code on a local machine/laptop, that was not on a domain; is that what might be happening for you?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Ray Herring

    SSCertifiable

    Points: 5399

    Thanks for the reply Lowell.

    I received the "WARNING: Error initializing default drive: 'Unable to find a default server with Active Directory Web Services running.'." message.

    I was trying to run the script from my Windows 7 Pro SP1 (64Bit) workstation which is on the domain. I do have the Remote Admin tools installed and use the "Active Directory Users and Computers" tool frequently. I have Read access to all of the A/D and Write access to certain groups.

    Obviously, my computer is joined to the domain.

    I checked the web references you provided and verified that KB958830 is installed on my workstation.

    I tried the script on one of my Windows 2008R2 EE servers and received the exact same error message.

    Is there something my SysAdmin needs to do on the Domain Controller? I ask because the error message refers to a Web Service and I assume that is where the service would be installed or configured.

    Thanks,

    Ray

  • kristof.dm

    SSC Enthusiast

    Points: 165

    Hello Everybody,

    First a big thank you for the very nice scripts Lowell !

    My 2 cents:

    About GetActiveDirectoryGroups details script:

    I found that Get-ad *.* querying resulted in the size limit request error

    => the AD webservice unable to handle more than 500 pages; I adapted the AD webservice on on of our dc's only to find another error.

    Also; There is a HARD 5 minute timeout you can not change on the ad webservice...

    Moving to the Quest commandlets.

    =>the quest cmdlets return a systemobject for the objectclass rather than the class itself (Group, User, Computer) so in the script, Objectclass needed to changed to Type.

    => The data length for the canonical names was also to short. I extended it. Some people tend to write entire novels in ad using OU's and way too deep paths and way to long groupnames... Anyway.

    The script runs for about 6-7 hours on my 12 GB surface pro 3 i7 to fail spawning a executeNonquery error 0 arguments.

    Printing the $sq var to screen shows a + 3Kwords sql query ... Maybe its too long ?

    I moved away from the bulk principle and divided the get-Qadgroup (and Qresult into smaller bits using the where-object filter. This worked !

    So now i have nice tables having a lot of info on our users but more important, for me at least, our groups.

    Is there anyone who's made some ssrs or some queries yet on these tables ?

    Nesting and looped nesting / heat map etc ...

    I'm planning to pull the data in PowerBi too

    Best Regards !

    Kristof

  • r5d4

    SSCrazy

    Points: 2499

    My AD groups are too large to use Get-ADGroupMember.
    Going to attempt it using the Members property of Get-ADGroup and iterate that results set.

    Anyone attempted this?  Or got a better idea?

    Thanks
    R

  • qbrt

    SSCrazy

    Points: 2422

    Good stuff!! Thank you for sharing.
    Any reason for using a verb in the SQL table names? I know, picky. I apologize ahead of time. But, but, but it just hurt my eyez looking at it. :crying:😀

    wth! just noticed. 2 year old post. damn.

  • Lowell

    SSC Guru

    Points: 323444

    the verb was just so it mirrored the filename and process; it just seemed right;
    This was republished today , as it had some relevancy for new folks.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • webrunner

    SSC-Dedicated

    Points: 30068

    Thanks for this article. I'm looking forward to going over it in detail, but FYI, I notice that the images appear to be broken. For example: http://www.stormrage.com/SQLStuff/Get-ADuser_Img2.png.
    If you do have screen shots that help explain the process, it would be great if you could have the image links updated.

    Thanks again.
    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • jay.raman

    Newbie

    Points: 7

    The following seems incorrect.
    CREATE VIEW vwActiveDirectorySummary
    AS
    SELECT
    ad.DomainName,
    COUNT(*) AS TotalADUsers,
     SUM(CASE
               WHEN ( AD.PasswordisExpired = 'True'
                      AND AD.AccountIsEnabled = 'True' )
                     OR AD.AccountIsEnabled = 'False' THEN 1
               ELSE 0
             END) AS [IsDisabledOrLockedOut],
    SUM(CASE WHEN AD.PasswordisExpired = 'True' AND AD.AccountIsEnabled = 'False' THEN 1 ELSE 0 END) As [LockedOut],
    SUM(CASE WHEN AD.AccountIsEnabled = 'True' THEN 1 ELSE 0 END) As [IsDisabled],
    SUM(CASE WHEN (AD.PasswordisExpired = 'False' AND AD.AccountIsEnabled = 'False') THEN 1 ELSE 0 END) As [NotDisabledOrLockedOut],
    SUM(CASE WHEN (AD.PasswordisExpired = 'False' AND AD.AccountIsEnabled = 'False') AND IsDate(AD.LastLogonTimestamp) = 0 THEN 1 ELSE 0 END) As [NeverLoggedIn],
    SUM(CASE WHEN (AD.PasswordisExpired = 'False' AND AD.AccountIsEnabled = 'False') AND IsDate(AD.LastLogonTimestamp) = 1 AND CONVERT(datetime,AD.LastLogonTimestamp) >= dateadd(dd,-30,getdate()) THEN 1 ELSE 0 END) As [ActiveLast30Days],
    SUM(CASE WHEN (AD.PasswordisExpired = 'False' AND AD.AccountIsEnabled = 'False') AND IsDate(AD.LastLogonTimestamp) = 1 AND CONVERT(datetime,AD.LastLogonTimestamp) <= dateadd(dd,-30,getdate()) AND AD.PasswordisExpired = 'False'AND AD.AccountIsEnabled = 'False' THEN 1 ELSE 0 END) As [NotActiveInMoreThan30Days],
    '' AS Filler
    FROM [dbo].[GetActiveDirectoryUsers] AD
    GROUP BY ad.DomainName
    GO

    For example 'NotDisabledrLockedOut' is computed as shown below.
    SUM(CASE WHEN (AD.PasswordisExpired = 'False' AND AD.AccountIsEnabled = 'False') THEN 1 ELSE 0 END) As [NotDisabledOrLockedOut],

    Should it not be

    SUM(CASE WHEN (AD.PasswordisExpired = 'False' AND AD.AccountIsEnabled = 'True') THEN 1 ELSE 0 END) AS [NotDisabledOrLockedOut]

    ,

    Also

    SUM(CASE WHEN AD.AccountIsEnabled = 'True' THEN 1 ELSE 0 END) As [IsDisabled],
    should be

    SUM

    (CASE WHEN AD.AccountIsEnabled = 'False' THEN 1 ELSE 0 END) AS [IsDisabled],

Viewing 8 posts - 16 through 23 (of 23 total)

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