Where I work, we have a number of applications that use Active Directory and a Single Sign On methodology. Each application has a table with the user's login and mappings to various internal roles, used determine what parts of the application the user can access. Also, except for a handful of applications that need SQL logins, SQL Server access is granted via AD Groups or AD Logins.
As a result, when a user reports an issue, we have to start in Active Directory to determine if the user is locked out, disabled, or password expired, before we can even start looking to see if the problem is in an application.
Having a list of Active Directory users in a SQL table can really open up your ability to analyze your systems, and produce smart relevant reports about your user base. In my case, I’m using PowerShell to query Active Directory for several different items, and insert the results into a suite of tables. I’m not going to go deep into PowerShell ins and outs, but hope to give you an incredible tool to put in your toolbox.
The Rabbit Hole
When you put your SQL Hat on, since you grant access to both groups and users, you realize you need tables filled with AD Users, AD groups and a AD Group Members table featuring who belongs in those groups. As you start pulling the AD Groups, you end up getting groups that contain other groups, objects like servers and computers and so much more. Add in multiple domains or forests, and it starts getting scary! The whole process goes pretty deep down that rabbit hole, so you get to benefit from the investigation and solution I’ve built so far.
So for my project, I’m gathering quite a bit of information; I’m getting a list of all Active Directory Users, all the groups, and all the group members. Groups can contain groups, so you might need to create a recursive CTE to enumerate just which GroupMembers is in a given group.
The Core Commands
Here’s a script example that is getting the Top 10 of users and Top 100 groups as an example. The last is enumerating the members in the “users” group, which is often another group name.
Each example is using the Out-GridView command to throw the data into a grid, which keeps us SQL nerds in our comfort zone.
Import-Module ActiveDirectory Get-ADUser -Filter * -ResultSetSize 100 -Properties * | Out-GridView Get-ADGroup -Filter * -ResultSetSize 100 -Properties * | Out-GridView Get-ADGroupMember -Identity "Users" | Out-GridView
Review the Results
Let’s look at the results for Get-ADUser, since that’s the most important item. Everything else is just more information about our core investigation: the Users in AD.
A side note that is probably relevant: if you ask for specific properties, and you misspell it or it does not exist, you get a blank answer, which can be confusing. In my example below, I included a column “Email”, but AD does not have a property for it; it does have EmailAddress though. In my world, we end up populating that address outside of this process for other reasons.
Get-ADUser -Filter {samaccountname -eq " Leonard_Hofstadter"} -ResultSetSize 10 -Properties * | select -property sAMAccountName,ou,GivenName,SurName,DisplayName,email,emailaddress | Out-GridViewFor the most part, what I’m grabbing is very straightforward, since the values are reasonably self describing; GivenName and Surname(FirstName/LastName) are a bit of unusual vernacular, but all other values are pretty understandable.
I’ve added a pair of inline calculations to calculate when an account or password expires, which can be helpful. The values stored in AD are not a datetime value, but number of ticks, which is something that is not easily consumable, so I’m converting them up front.
The final command I actually end up looks like this for reference, and has the properties you see here:
Get-ADUser -Filter * -Properties * | select -property
CanonicalName,
sAMAccountName,
ou,
GivenName,
SurName,
DisplayName,
email,
emailaddress,
StreetAddress,
City,
State,
PostalCode,
HomePhone,
MobilePhone,
OfficePhone,
Fax,
Company,
Organization,
Department,
Title,
Description,
Office,
extensionAttribute1,
extensionAttribute2,
extensionAttribute3,
extensionAttribute4,
extensionAttribute5,
@{Name='AccountExpires';Expression={[DATETIME]::fromFileTime($_.accountExpires)}},
Enabled,
PasswordLastSet,
@{n="PasswordExpirationDate";e={$_.PasswordLastSet.AddDays($maxPasswordAge)}},
PasswordNeverExpires,
PasswordExpired,
LastLogonDate,
whenCreatedCaveats and Gotchas for Folks New to PowerShell
An interesting difference between T-SQL and PowerShell: asterisk (*) does not mean “all” in PowerShell! As a SQL developer, you expect asterisk (*) to mean everything, but instead, there is a default set of Properties that is returned as part of the asterisk results. You’ll see why in a minute.
The command below returns 30 columns in our grid; that might be an artifact of the Out-Grid command, because you don’t get all values in the grid!
Get-ADUser -Filter * -ResultSetSize 10 -Properties * | Out-GridView
The same command sent to the text viewer, in my environment, returns 142 values!
Get-ADUser -Filter * -ResultSetSize 10 -Properties *
But wait, there’s more! In my shop, I know we use some of the twelve optional properties related to the ADUser to store information; extensionAttribute1 through extensionAttribute12 exist, but are empty unless you populate them. I know they exist, but they are not in that 142 items I just grabbed. So instinctively, I already know there are more properties that I’m currently seeing. It depends on your AD environment, but there are actually more than 700 properties related to an ADUser.
If you need to go down that tangent, I’ve added a text file (Get-ADUser_All_Properties.txt) that enumerates all the Properties I saw in my domain. Also, two resources for you to make it easy:
Download Oneidentity (Formerly Quest, formerly Dell) ActiveRoles Management Shell for Active Directory 1.7: https://www.oneidentity.com/products/active-roles/
Dmitry Sotniko’s blog has an example using the above script to get a list of ALL user properties: https://dmitrysotnikov.wordpress.com/2007/06/28/get-a-list-of-all-user-properties/
My own script adaptation of those two resources is below:
###################################################################################################
#load snapin if not already loaded
# https://support.software.dell.com/download-install-detail/5024645
###################################################################################################
if ( (Get-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin Quest.ActiveRoles.ADManagement
}
Get-QADUser -ReturnPropertyNamesOnly -IncludeAllPropertiesWhat if we have multiple forests or child domains?
My company was recently absorbed by another company. Get-ADUser has an optional –Server parameter that you can use to identify the other domains. In my example, My Home domain is the BigBang domain was absorbed by the Disney domain.
I know that fully qualified SamAccountName are actually something like BigBang.com\Leonard_Hofsteader, but the other domain is actually users like Disney.local\Aladdin_StreetRat. So, I could add the parameter to the GetADUser call to pull the other domain:
Get-ADUser -Filter * -ResultSetSize 10 -Server bigbang.com -Properties * | Out-GridView Get-ADUser -Filter * -ResultSetSize 10 -Server disney.local -Properties * | Out-GridView
I don’t have child domains set up, but it would be the same logic; identify the child domain, and pass that to the server;
Get-ADUser -Filter * -ResultSetSize 10 -Server Avengers.MarvelUniverse.com -Properties * | Out-GridView Get-ADUser -Filter * -ResultSetSize 10 -Server XMen.MarvelUniverse.com -Properties * | Out-GridView
PowerShell to SQL Time!
OK, so I rambled on about how there is a LOT of info out there, but what is relevant? I recommend you start with what I found relevant to me, and then add or subtract other properties that might be useful. I went through a lot of iterations to determine what I wanted to actually capture, and the SQL table that captures it is in the shape below. However, I built a suite of views to make the information even more relevant.
The key things for most people are the classics; FirstName/LastNname/EmailAddress and whether they are locked out, disabled or password expired. I’ve included a lot of values that we actually use regularly;
I’ve got a companion process that updates information in Active directory, so that if we needed to say, update everyone’s address or contact phone numbers, or Department, I can use Set-ADUser and update those values from my SQL Table. If that seems relevant, I’ll add a companion article to this one. It can be really scary updating Active Directory in monster batches though, a lot of people don’t have that level of comfort.
Since this information is not related to one specific application, I put the tables and views related to this in a separate database, DBA_Utilities; when you install the two scripts, you will need to create the objects in the right database, and change just two parameters the PowerShell script
################################################################################################### #SQL Connection Setup ################################################################################################### $SQLServer = "localhost"; $SQLDBName = "DBA_Utilities";
ActiveDirectoryTablesAndViews Details
The tables here are what I felt we needed at the time; you can strip out or add columns, but you’ll also need to strip out or add parameters and columns in the matching PowerShell script. All in all, it’s a robust collection of items to start your baseline with.
There’s a few views I created that help gather the data together for convenience, and they are not mandatory or anything, just some suggestions on how to visualize the data a little better.
{Placeholder for FullScript ActiveDirectoryTablesAndViews.sql}
FootPrint?
The PowerShell below gets All AD Users every time, I haven’t bothered to look into trying to get incremental changes; my local domain environment only has ~3000 ADUsers, many of them past employees, so I didn’t see a need to streamline the amount; if you worked for a big company, you might want to look at tuning it to get only new records. Here’s the core of the Get-ADUsers script; get the users in memory, and loop through each, calling an update-insert SQL command to update/insert the data into the table.
--#################################################################################################
-- Three core tables to hold, Users,Groups and Group Members
--#################################################################################################
IF OBJECT_ID('[dbo].[GetActiveDirectoryUsers]') IS NOT NULL
DROP TABLE [dbo].[GetActiveDirectoryUsers]
GO
CREATE TABLE [dbo].[GetActiveDirectoryUsers] (
[ID] INT IDENTITY(1,1) NOT NULL,
[CanonicalName] VARCHAR(128) NOT NULL,
[DomainName] AS LEFT([CanonicalName],CHARINDEX('/',[CanonicalName])-1),
[sAMAccountName] VARCHAR(128) NOT NULL,
[OperationalUnit] VARCHAR(128) NULL,
[FirstName] VARCHAR(128) NULL,
[LastName] VARCHAR(128) NULL,
[DisplayName] VARCHAR(128) NULL,
VARCHAR(128) NULL,
[EmailAddress] VARCHAR(128) NULL,
[ImpliedAcount] AS (case when charindex('@',[EmailAddress])>(0) AND charindex('.',[EmailAddress])>charindex('@',[EmailAddress]) then (substring([EmailAddress],charindex('@',[EmailAddress])+(1),(charindex('.',[EmailAddress])-charindex('@',[EmailAddress]))-(1))+'\')+substring([EmailAddress],(1),charindex('@',[EmailAddress])-(1)) else '' end) PERSISTED,
[StreetAddress] VARCHAR(128) NULL,
[City] VARCHAR(128) NULL,
[State] VARCHAR(128) NULL,
[PostalCode] VARCHAR(128) NULL,
[HomePhone] VARCHAR(128) NULL,
[MobilePhone] VARCHAR(128) NULL,
[OfficePhone] VARCHAR(128) NULL,
[Fax] VARCHAR(128) NULL,
[Company] VARCHAR(128) NULL,
[Organization] VARCHAR(128) NULL,
[Department] VARCHAR(128) NULL,
[Title] VARCHAR(128) NULL,
[Description] VARCHAR(128) NULL,
[Office] VARCHAR(128) NULL,
[extensionAttribute1] VARCHAR(128) NULL,
[extensionAttribute2] VARCHAR(128) NULL,
[extensionAttribute3] VARCHAR(128) NULL,
[extensionAttribute4] VARCHAR(128) NULL,
[extensionAttribute5] VARCHAR(128) NULL,
[AccountExpires] VARCHAR(128) NULL,
[AccountIsEnabled] VARCHAR(128) NULL,
[PasswordLastSet] VARCHAR(128) NULL,
[PasswordAge] AS (case when isdate([PasswordLastSet])=(1) then datediff(day,[PasswordLastSet],getdate()) else (0) end),
[PasswordExpires] VARCHAR(128) NULL,
[PasswordNeverExpires] VARCHAR(128) NULL,
[PasswordIsExpired] VARCHAR(128) NULL,
[LastLogonTimestamp] VARCHAR(128) NULL,
[CreatedDate] DATETIME NULL,
[DWCreatedDate] DATETIME NULL CONSTRAINT [DF__GetActiveDirectoryUsers__DWCreatedDate] DEFAULT (getdate()),
[DWUpdatedDate] DATETIME NULL,
CONSTRAINT [PK__GetActiveDirectoryUsers_sAMAccountName] PRIMARY KEY CLUSTERED ([CanonicalName],[sAMAccountName] asc))
IF OBJECT_ID('[dbo].[GetActiveDirectoryGroups]') IS NOT NULL
DROP TABLE [dbo].[GetActiveDirectoryGroups]
GO
CREATE TABLE [dbo].[GetActiveDirectoryGroups] (
[ID] INT IDENTITY(1,1) NOT NULL,
[CanonicalName] VARCHAR(128) NOT NULL,
[DomainName] AS LEFT([CanonicalName],CHARINDEX('/',[CanonicalName])-1),
[SamAccountName] VARCHAR(128) NOT NULL,
[DisplayName] VARCHAR(128) NULL,
[Description] VARCHAR(128) NULL,
[DistinguishedName] VARCHAR(128) NULL,
[GroupCategory] VARCHAR(128) NULL,
[GroupScope] VARCHAR(128) NULL,
[CreatedDate] DATETIME NULL,
[DWCreatedDate] DATETIME NULL CONSTRAINT [DF__GetActiveDirectoryGroups__DWCreatedDate] DEFAULT (getdate()),
[DWUpdatedDate] DATETIME NULL,
CONSTRAINT [PK__GetActiveDirectoryGroups_SamAccountName] PRIMARY KEY CLUSTERED ([CanonicalName],[SamAccountName] asc))
IF OBJECT_ID('[dbo].[GetActiveDirectoryGroupMembers]') IS NOT NULL
DROP TABLE [dbo].[GetActiveDirectoryGroupMembers]
GO
CREATE TABLE [dbo].[GetActiveDirectoryGroupMembers] (
[ID] INT IDENTITY(1,1) NOT NULL,
[GroupCanonicalName] VARCHAR(128) NOT NULL,
[GroupSamAccountName] VARCHAR(128) NOT NULL,
[DomainName] AS LEFT([GroupCanonicalName],CHARINDEX('/',[GroupCanonicalName])-1),
[SamAccountName] VARCHAR(128) NOT NULL,
[ObjectClass] VARCHAR(128) NOT NULL,
[DWCreatedDate] DATETIME NULL CONSTRAINT [DF__GetActiveDirectoryGroupMembers__DWCreatedDate] DEFAULT (getdate()),
[DWUpdatedDate] DATETIME NULL,
[DWIsDeleted] BIT NULL CONSTRAINT [DF__GetActiveDirectoryGroupMembers__DWIsDeleted] DEFAULT ((0)),
[DWDeletedDate] DATETIME NULL,
CONSTRAINT [UQ_GetActiveDirectoryGroupMembers_GroupUserObject] UNIQUE CLUSTERED ([GroupCanonicalName] asc,[GroupSamAccountName] asc,[SamAccountName] asc, [ObjectClass] asc))
--#################################################################################################
-- Some Handy Views to make the data a wee bit more accessible.
--#################################################################################################
IF OBJECT_ID('[dbo].[vwActiveDirectoryUsers]') IS NOT NULL
DROP VIEW [dbo].[vwActiveDirectoryUsers]
GO
--#################################################################################################
-- vwActiveDirectoryUsers, subset of data, underlying table populated by a powershell script and Scheduled job three times a week
--#################################################################################################
--SELECT * FROM vwActiveDirectoryUsers
CREATE View vwActiveDirectoryUsers
AS
SELECT CASE
WHEN AD.AccountIsEnabled = 'False'
THEN 1
WHEN AD.PasswordisExpired = 'True'
AND AD.PasswordNeverExpires = 'False'
THEN 1
ELSE 0
END AS [IsDisabledOrLockedOut],
CASE
WHEN ( AD.PasswordisExpired = 'False'
AND AD.AccountIsEnabled = 'True' )
AND IsDate(AD.LastLogonTimestamp) = 1
AND CONVERT(DATETIME, AD.LastLogonTimestamp) >= Dateadd(dd, -30, Getdate()) THEN 1
ELSE 0
END AS [ActiveLast30Days],
AD.ID AS ID,
LEFT(AD.CanonicalName,CHARINDEX('/',CanonicalName)-1) As DomainName,
AD.FirstName,
AD.LastName,
AD.DisplayName AS Name,
AD.EmailAddress AS email,
AD.sAMAccountName,
AD.ImpliedAcount,
AD.AccountExpires,
AD.PasswordLastSet,
AD.PasswordAge,
AD.PasswordExpires,
AD.PasswordNeverExpires,
AD.PasswordIsExpired,
'' AS PasswordStatus,
AD.AccountIsEnabled,
AD.LastLogonTimestamp,
AD.DWCreatedDate,
AD.DWUpdatedDate
FROM GetActiveDirectoryUsers AD
GO
IF OBJECT_ID('[dbo].[vwActiveDirectoryGroupMembers]') IS NOT NULL
DROP VIEW [dbo].[vwActiveDirectoryGroupMembers]
GO
--#################################################################################################
-- vwADGroupMembers, underlying table populated by a powershell script and Scheduled job once a month
--#################################################################################################
CREATE VIEW vwActiveDirectoryGroupMembers
AS
select
LEFT(g.CanonicalName,CHARINDEX('/',g.CanonicalName)-1) As GroupDomainName,
g.SamAccountName As GroupSamAccountName,
g.DisplayName AS GroupDisplayName,
u.*
FROM GetActiveDirectoryGroupMembers gm
INNER JOIN GetActiveDirectoryGroups g on gm.GroupSamAccountName = g.SamAccountName
AND gm.DomainName = g.DomainName
INNER JOIN GetActiveDirectoryUsers u on gm.SamAccountName = u.sAMAccountName
AND gm.DomainName = u.DomainName
GO
IF OBJECT_ID('[dbo].[vwGetActiveDirectoryUsers]') IS NOT NULL
DROP VIEW [dbo].[vwGetActiveDirectoryUsers]
GO
--#################################################################################################
-- vwGetActiveDirectoryUsers, full column list,underlying table populated by a powershell script and Scheduled job three times a week
--#################################################################################################
CREATE VIEW vwGetActiveDirectoryUsers
AS
SELECT CASE
WHEN ( AD.PasswordisExpired = 'True'
AND AD.AccountIsEnabled = 'True' )
OR AD.AccountIsEnabled = 'False' THEN 1
ELSE 0
END AS [IsDisabledOrLockedOut],
CASE
WHEN ( AD.PasswordisExpired = 'False'
AND AD.AccountIsEnabled = 'True' )
AND IsDate(AD.LastLogonTimestamp) = 1
AND CONVERT(DATETIME, AD.LastLogonTimestamp) >= Dateadd(dd, -30, Getdate()) THEN 1
ELSE 0
END AS [ActiveLast30Days],
AD.[ID],
AD.[CanonicalName],
AD.[DomainName],
AD.[sAMAccountName],
AD.[OperationalUnit],
AD.[FirstName],
AD.[LastName],
AD.[DisplayName],
AD.,
AD.[EmailAddress],
AD.[ImpliedAcount],
AD.[StreetAddress],
AD.[City],
AD.[State],
AD.[PostalCode],
AD.[HomePhone],
AD.[MobilePhone],
AD.[OfficePhone],
AD.[Fax],
AD.[Company],
AD.[Organization],
AD.[Department],
AD.[Title],
AD.[Description],
AD.[Office],
AD.[extensionAttribute1],
AD.[extensionAttribute2],
AD.[extensionAttribute3],
AD.[extensionAttribute4],
AD.[extensionAttribute5],
AD.[AccountExpires],
AD.[AccountIsEnabled],
AD.[PasswordLastSet],
AD.[PasswordAge],
AD.[PasswordExpires],
AD.[PasswordNeverExpires],
AD.[PasswordIsExpired],
AD.[LastLogonTimestamp],
AD.[CreatedDate],
AD.[DWCreatedDate],
AD.[DWUpdatedDate]
FROM GetActiveDirectoryUsers AD
GO
IF OBJECT_ID('[dbo].[vwActiveDirectorySummary]') IS NOT NULL
DROP VIEW [dbo].[vwActiveDirectorySummary]
GO
--#################################################################################################
-- vwActiveDirectorySummary, rollup of data, underlying table populated by a powershell script and Scheduled job three times a week
--#################################################################################################
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
GOTimewise, it takes less than three minutes to run the script for users, but the script for Groups and Group members often takes more than ten minutes; it’s grabbing a lot more records and making a lot more calls.
In a real life scenario, for our combined domain, here’s some rowcounts for my tables, which contains two domain forests, Each with roughly 3K users ; you can see the Group Members is roughly 10X the User Count, because users exist in multiple groups.
| SchemaName | ObjectName | TheCount | cmd |
| dbo | GetActiveDirectoryGroupMembers | 62054 | SELECT * FROM [dbo].[GetActiveDirectoryGroupMembers] |
| dbo | GetActiveDirectoryUsers | 6602 | SELECT * FROM [dbo].[GetActiveDirectoryUsers] |
| dbo | GetActiveDirectoryGroups | 2464 | SELECT * FROM [dbo].[GetActiveDirectoryGroups] |
GetActiveDirectoryUsers Details
The logic in the script below is to basically call a parameterized upsert command for each row returned from the Get-ADUser Call. A Get-String function simply returns empty strings for Nothing/NULL values. While techically a RBAR kind of action, It made sense to me at the time to do it this way, so I won't second guess myself and try to build something faster for now.
#GetActiveDirectoryUsers
Import-Module ActiveDirectory
###################################################################################################
# PowerShell to grab all active directory users incrementally
#inserting or updating them into a SQL table.
###################################################################################################
function Get-String ($obj){
if ([string]::IsNullOrEmpty($obj))
{
return ""
}
else
{
return $obj.ToString();
}
}
###################################################################################################
#SQL Connection Setup
###################################################################################################
$SQLServer = "localhost";
$SQLDBName = "DBA_Utilities";
$SqlQuery = @"
UPDATE [DBA_Utilities].[dbo].[GetActiveDirectoryUsers]
SET
[OperationalUnit] = @OperationalUnit,
[FirstName] = @FirstName,
[LastName] = @LastName,
[DisplayName] = @DisplayName,
= @email,
[EmailAddress] = @EmailAddress,
[StreetAddress] = @StreetAddress,
[City] = @City,
[State] = @State,
[PostalCode] = @PostalCode,
[HomePhone] = @HomePhone,
[MobilePhone] = @MobilePhone,
[OfficePhone] = @OfficePhone,
[Fax] = @Fax,
[Company] = @Company,
[Organization] = @Organization,
[Department] = @Department,
[Title] = @Title,
[Description] = @Description,
[Office] = @Office,
[extensionAttribute1] = @extensionAttribute1,
[extensionAttribute2] = @extensionAttribute2,
[extensionAttribute3] = @extensionAttribute3,
[extensionAttribute4] = @extensionAttribute4,
[extensionAttribute5] = @extensionAttribute5,
[AccountExpires] = @AccountExpires,
[AccountIsEnabled] = @AccountIsEnabled,
[PasswordLastSet] = @PasswordLastSet,
[PasswordExpires] = @PasswordExpires,
[PasswordNeverExpires]= @PasswordNeverExpires,
[PasswordIsExpired] = @PasswordIsExpired,
[LastLogonTimestamp] = @LastLogonTimestamp,
[CreatedDate] = @CreatedDate,
[DWUpdatedDate] = getdate()
WHERE[sAMAccountName] = @sAMAccountName
AND [CanonicalName] = @CanonicalName
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [DBA_Utilities].[dbo].[GetActiveDirectoryUsers]
(CanonicalName,[sAMAccountName],[OperationalUnit],[FirstName],[LastName],[DisplayName],,[EmailAddress],[StreetAddress],[City],[State],[PostalCode],[HomePhone],[MobilePhone],[OfficePhone],[Office],[Fax],[Company],[Organization],[Department],[Title],[Description],[extensionAttribute1],[extensionAttribute2],[extensionAttribute3],[extensionAttribute4],[extensionAttribute5],[AccountExpires],[AccountIsEnabled],[PasswordLastSet],[PasswordExpires],[PasswordNeverExpires],[PasswordIsExpired],[LastLogonTimestamp])
SELECT @CanonicalName,@sAMAccountName ,@OperationalUnit ,@FirstName ,@LastName ,@DisplayName ,@email ,@EmailAddress ,@StreetAddress ,@City ,@State ,@PostalCode ,@HomePhone ,@MobilePhone ,@OfficePhone ,@Office ,@Fax ,@Company ,@Organization ,@Department ,@Title ,@Description ,@extensionAttribute1 ,@extensionAttribute2 ,@extensionAttribute3 ,@extensionAttribute4 ,@extensionAttribute5 ,@AccountExpires ,@AccountIsEnabled ,@PasswordLastSet ,@PasswordExpires ,@PasswordNeverExpires ,@PasswordIsExpired ,@LastLogonTimestamp
END
"@
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True";
$SqlConnection.Open();
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.Connection = $SqlConnection;
$SqlCmd.CommandType = [System.Data.CommandType]::Text ;
##$SqlCmd.CommandText = "SELECT COUNT(*) FROM [DBA_Utilities].[dbo].[ActiveDirectoryUsers]";
##$RecordCount = $SqlCmd.ExecuteScalar();
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CanonicalName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@sAMAccountName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@OperationalUnit", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@FirstName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DisplayName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@email", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@EmailAddress", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@StreetAddress", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@City", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@State", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PostalCode", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@HomePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@MobilePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@OfficePhone", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Fax", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Company", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Organization", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Department", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Title", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Description", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Office", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute1", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute2", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute3", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute4", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@extensionAttribute5", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@AccountExpires", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@AccountIsEnabled", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordLastSet", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordExpires", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordNeverExpires",[Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PasswordIsExpired", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastLogonTimestamp", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CreatedDate", [Data.SQLDBType]::VarChar, 128))) | Out-Null
###################################################################################################
#Get the Results From AD
###################################################################################################
#$Results = Get-ADUser -Filter * -Server "BigBang.com" -ResultSetSize 10 -Properties * | select -property sAMAccountName,ou,
$Results = Get-ADUser -Filter * -Properties * | select -property CanonicalName,sAMAccountName,ou,
GivenName,SurName,DisplayName,email,emailaddress,
StreetAddress,City,State,PostalCode,
HomePhone,MobilePhone,OfficePhone,Fax,
Company,Organization,Department,Title,Description,Office,
extensionAttribute1,extensionAttribute2,extensionAttribute3,extensionAttribute4,extensionAttribute5,
@{Name='AccountExpires';Expression={[DATETIME]::fromFileTime($_.accountExpires)}},Enabled,PasswordLastSet,
@{n="PasswordExpirationDate";e={$_.PasswordLastSet.AddDays($maxPasswordAge)}},PasswordNeverExpires,PasswordExpired,
LastLogonDate,whenCreated
foreach ($item in $Results) {
$SqlCmd.Parameters[0].Value = Get-String($item.CanonicalName)
$SqlCmd.Parameters[1].Value = Get-String($item.sAMAccountName)
$SqlCmd.Parameters[2].Value = Get-String($item.ou)
$SqlCmd.Parameters[3].Value = Get-String($item.GivenName)
$SqlCmd.Parameters[4].Value = Get-String($item.SurName)
$SqlCmd.Parameters[5].Value = Get-String($item.DisplayName)
$SqlCmd.Parameters[6].Value = Get-String($item.email)
$SqlCmd.Parameters[7].Value = Get-String($item.emailaddress)
$SqlCmd.Parameters[8].Value = Get-String($item.StreetAddress)
$SqlCmd.Parameters[9].Value = Get-String($item.City)
$SqlCmd.Parameters[10].Value = Get-String($item.State)
$SqlCmd.Parameters[11].Value = Get-String($item.PostalCode)
$SqlCmd.Parameters[12].Value = Get-String($item.HomePhone)
$SqlCmd.Parameters[13].Value = Get-String($item.MobilePhone)
$SqlCmd.Parameters[14].Value = Get-String($item.OfficePhone)
$SqlCmd.Parameters[15].Value = Get-String($item.Fax)
$SqlCmd.Parameters[16].Value = Get-String($item.Company)
$SqlCmd.Parameters[17].Value = Get-String($item.Organization)
$SqlCmd.Parameters[18].Value = Get-String($item.Department)
$SqlCmd.Parameters[19].Value = Get-String($item.Title)
$SqlCmd.Parameters[20].Value = Get-String($item.Description)
$SqlCmd.Parameters[21].Value = Get-String($item.Office)
$SqlCmd.Parameters[22].Value = Get-String($item.extensionAttribute1)
$SqlCmd.Parameters[23].Value = Get-String($item.extensionAttribute2)
$SqlCmd.Parameters[24].Value = Get-String($item.extensionAttribute3)
$SqlCmd.Parameters[25].Value = Get-String($item.extensionAttribute4)
$SqlCmd.Parameters[26].Value = Get-String($item.extensionAttribute5)
$SqlCmd.Parameters[27].Value = Get-String($item.AccountExpires)
$SqlCmd.Parameters[28].Value = Get-String($item.Enabled)
$SqlCmd.Parameters[29].Value = Get-String($item.PasswordLastSet)
$SqlCmd.Parameters[30].Value = Get-String($item.PasswordExpirationDate)
$SqlCmd.Parameters[31].Value = Get-String($item.PasswordNeverExpires)
$SqlCmd.Parameters[32].Value = Get-String($item.PasswordExpired)
$SqlCmd.Parameters[33].Value = Get-String($item.LastLogonDate)
$SqlCmd.Parameters[34].Value = Get-String($item.whenCreated)
$SqlCmd.ExecuteNonQuery();
}
if ($SqlConnection.State -eq "Open") {$SqlConnection.Close()}
GetActiveDirectoryGroups Details
The logic in the script below is similar to the first example : call a parameterized upsert command for each row returned from the Get-ADGroup Call., but for each group, we also Call Get-ADGroupMembers and upsert those into a third table.
So this one proc is populating two tables for us. The Get-ADGroupMembers call gives us four different ObjectClass values; user/group/computer or blank values.
It’s pretty handy to now have a list of all known computers in active directory, as it might be the basis for scanning your computers ofr services, putting them in workstation vs server logical groups, etc.
select * from [GetActiveDirectoryGroupMembers] where objectclass = 'computer'
{Placeholder for FullScript Get-ActiveDirectoryGroups}
#GetActiveDirectoryGroups
Import-Module ActiveDirectory
#Get-ADGroup -Filter {samaccountname -eq "Citrix_Test_Users" -ResultSetSize 1 -Properties * | SELECT SamAccountName,Name,Description,DistinguishedName,CanonicalName,GroupCategory,GroupScope,whenCreated
#Get-ADGroupMember -Identity "Citrix_Test_Users" | SELECT -property SamAccountName
#Get-ADGroupMember -Filter {samaccountname -eq "Citrix_Test_Users" -and objectClass -eq "user"} | SELECT -property SamAccountName
#Get-ADGroupMember -Filter {samaccountname -eq "Citrix_Test_Users"} | SELECT -property SamAccountName
#GetActiveDirectoryUsers
###################################################################################################
# PowerShell to grab all active directory groups incrementally
#inserting or updating them into a SQL table.
###################################################################################################
function Get-String ($obj){
if ([string]::IsNullOrEmpty($obj))
{
return ""
}
else
{
return $obj.ToString();
}
}
###################################################################################################
#SQL Connection Setup
###################################################################################################
$SQLServer = "localhost";
$SQLDBName = "DBA_Utilities";
$SqlQuery = @"
UPDATE [DBA_Utilities].[dbo].[GetActiveDirectoryGroups]
SET
[DisplayName] = @DisplayName,
[Description] = @Description,
[DistinguishedName] = @DistinguishedName,
[CanonicalName] = @CanonicalName,
[GroupCategory] = @GroupCategory,
[GroupScope] = @GroupScope,
[CreatedDate] = @CreatedDate,
[DWUpdatedDate] = getdate()
WHERE sAMAccountName = @sAMAccountName
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [DBA_Utilities].[dbo].[GetActiveDirectoryGroups]
([sAMAccountName],[DisplayName],[Description],[DistinguishedName],[CanonicalName],[GroupCategory],[GroupScope],[CreatedDate])
SELECT @sAMAccountName ,@DisplayName ,@Description ,@DistinguishedName ,@CanonicalName ,@GroupCategory ,@GroupScope ,@CreatedDate
END
"@
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True";
$SqlConnection.Open();
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.Connection = $SqlConnection;
$SqlCmd.CommandType = [System.Data.CommandType]::Text ;
##$SqlCmd.CommandText = "SELECT COUNT(*) FROM [DBA_Utilities].[dbo].[ActiveDirectoryGroups]";
##$RecordCount = $SqlCmd.ExecuteScalar();
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@sAMAccountName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DisplayName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Description", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@DistinguishedName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CanonicalName", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@GroupCategory", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@GroupScope", [Data.SQLDBType]::VarChar, 128))) | Out-Null
$SqlCmd.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@CreatedDate", [Data.SQLDBType]::VarChar, 128))) | Out-Null
###################################################################################################
#Group Members Setup
###################################################################################################
[string]$MembersQuery = @"
DECLARE @GCanonicalName varchar(128),
@GSamAccountName varchar(128),
@SamAccountName varchar(128),
@ObjectClass varchar(128);
SELECT @GCanonicalName = 'param1',
@GSamAccountName = 'param2',
@SamAccountName = 'param3',
@ObjectClass = 'param4';
UPDATE [DBA_Utilities].[dbo].[GetActiveDirectoryGroupMembers]
SET DWUpdatedDate = getdate()
WHERE [GroupCanonicalName] = @GCanonicalName
AND [GroupSamAccountName] = @GSamAccountName
AND [SamAccountName] = @SamAccountName
AND [ObjectClass] = @ObjectClass
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [DBA_Utilities].[dbo].[GetActiveDirectoryGroupMembers]
([GroupCanonicalName],[GroupSamAccountName],[SamAccountName],[ObjectClass])
SELECT @GCanonicalName,@GSamAccountName ,@SamAccountName,@ObjectClass
WHERE NOT EXISTS(SELECT *
FROM [DBA_Utilities].[dbo].[GetActiveDirectoryGroupMembers]
WHERE [GroupSamAccountName] = @GSamAccountName
AND [SamAccountName] = @SamAccountName
AND [ObjectClass] = @ObjectClass)
END --IF
"@
$MembersCmd = New-Object System.Data.SqlClient.SqlCommand;
$MembersCmd.Connection = $SqlConnection;
$MembersCmd.CommandType = [System.Data.CommandType]::Text ;
###################################################################################################
#Get the Results From AD
###################################################################################################
#$Results = Get-ADGroup -Filter * -Server "BigBang.com" -ResultSetSize 10 -Properties * | SELECT SamAccountName,Name,Description,DistinguishedName,CanonicalName,GroupCategory,GroupScope,whenCreated
$Results = Get-ADGroup -Filter * -Properties * | select -property SamAccountName,Name,Description,DistinguishedName,CanonicalName,GroupCategory,GroupScope,whenCreated
foreach ($item in $Results) {
$SqlCmd.Parameters[0].Value = Get-String($item.SamAccountName)
$SqlCmd.Parameters[1].Value = Get-String($item.Name)
$SqlCmd.Parameters[2].Value = Get-String($item.Description)
$SqlCmd.Parameters[3].Value = Get-String($item.DistinguishedName)
$SqlCmd.Parameters[4].Value = Get-String($item.CanonicalName)
$SqlCmd.Parameters[5].Value = Get-String($item.GroupCategory)
$SqlCmd.Parameters[6].Value = Get-String($item.GroupScope)
$SqlCmd.Parameters[7].Value = Get-String($item.whenCreated)
$SqlCmd.ExecuteNonQuery() | Out-Null;;
#now the details! members info!
#$MemberResults = Get-ADGroupMember -Filter {Samaccountname -eq $item.SamAccountName -and objectClass -eq "user"} | SELECT -property SamAccountName
[string]$can = $item.CanonicalName.ToString()
[string]$grp = $item.SamAccountName.ToString()
#$MemberResults = Get-ADGroupMember -Server "BigBang.com" -Identity $grp | SELECT -property SamAccountName,objectClass
$MemberResults = Get-ADGroupMember -Identity $grp | SELECT -property SamAccountName,objectClass
#write-host $item.SamAccountName
foreach ($MemberItem in $MemberResults) {
[string]$s = Get-String($MemberItem.SamAccountName)
[string]$o = Get-String($MemberItem.objectClass);
#write-host $grp '|' $s '|' $o
[string] $sq = $MembersQuery;
$sq = $sq -Replace "param1",$can.Replace("'","''")
$sq = $sq -Replace "param2",$grp.Replace("'","''")
$sq = $sq -Replace "param3",$s.Replace("'","''")
$sq = $sq -Replace "param4",$o.Replace("'","''")
#write-host $sq
$MembersCmd.CommandText = $sq;
$MembersCmd.ExecuteNonQuery() | Out-Null;
}
}
if ($SqlConnection.State -eq "Open") {$SqlConnection.Close()}Run This Regularly or not?
I’ve included a scripted Job as part of the article as well. However, running things that Get AD info requires a domain user, so you need all that that would entail: a Credential, an Operator, and operator permissions.
I’ve created a scheduled job that scans for users M-W-F, but I end up refreshing Groups and Group members rarely, if ever. I’ve disabled the job step in my case, since I rarely need to compare group information. I might run it manually once a month or so, but my own needs tend towards reviewing end users.
So What Can I do with it?
So you’ve got some nifty tables full of this information, but if you don’t actually use it, it has no real value. Here’s just some of the things I do with it.
- Remove SQL users that are disabled. I create a script that does an IF EXISTS() and drops users and logins. Pasting that into a Central Management Server multi server query, and I cleanup tons of servers at a time.
- Generate and verify the list of users who should have access to various resources based on the groups they belong to.
- Adding AD users quickly to our application via insert…from syntax.
- Finding users who exist in recursive groups.
- Inventorying Servers and workstations.
- Providing spreadsheets to be used for Updating Active Directory; we have had several initiatives to update phone number and addresses and custom string values; I provide a spreadsheet, and then generate Set-ADUser commands to update AD, which in turn gets pulled back into my tables.
I hope you find this suite of tools for your toolbox helpful; It’s one of those Secret Sauce implementations a lot of folks might have developed independently, but are afraid to share, since they put a lot of time into it. I’ve been in countless meetings where someone says “how many people in active directory? How many active in the last 30 days? how many are just active period?”; being able to pull that info up in 30 secodns makes you look extremely competent.
Please join the discussion and give some feedback! Everything we add makes the SQL community a little better.
TLDR:
- Run the ActiveDirectoryTablesAndViews.sql in a database named DBA_Utilities(or the db of your choice) to create the Tables And Views.
- Modify the two scripts GetActiveDirectoryUsers.ps1 And GetActiveDirectoryGroups.ps1 to point to the right server and database, if it’s not localhost and DBA_Utilities.
- Run each Of the two PowerShell Scripts manually from either a PowerShell prompt, a PowerShellIDE, or a commandline call .
- Optional SQL Job is going to require a Credential and Operator, that is an AD User:
- Find the CredentialHelper.sql script, so that you can modify it to create the Credential, Operator, and permissions to the PowerShell and CmdExec subsystem.
- Copy both PowerShell files to a location on the server, ie L:\PowerShell\
- Modify the Script SQLJob_ETL_GetActiveDirectoryData.sql in two places to have the filepath you decided on in Step 3. Modify the Operator if you didn’t stick with “TaskRunner”, and run it to create the Job.
- Run the Job manually, and review the contents of the new tables and views.
