PowerShell To Get Active Directory Users And Groups into SQL

Question

PowerShell To Get Active Directory Users And Groups into SQL

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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: 5533 More actions · Answer 2

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 More actions · Answer 3

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: 2522 More actions · Answer 4

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: 2737 More actions · Answer 5

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: 323518 More actions · Answer 6

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: 31885 More actions · Answer 7

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 More actions · Answer 8

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],

vamshi.gopalapuram Newbie Points: 1 More actions · Answer 9

Thank you very much for the awesome work.

When ever I run the powershell scripts I get the below error for AD Users

Exception calling "ExecuteNonQuery" with "0" argument(s): "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Violation of PRIMARY KEY constraint 'PK__GetActiveDirectoryUsers_sAMAccountName'. Cannot insert duplicate key in object 'dbo.GetActiveDirectoryUsers'. The duplicate key value is

And below error for AD Groups

The size limit for this request was exceeded

Exception calling "ExecuteNonQuery" with "0" argument(s): "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The statement has been terminated.