Get a SQL Inventory of Active Directory with PowerShell

Here is the newly improved version of my Powershell script to inventory Active Directory script. Rather than use SQLPS, I instead create .NET objects within the Data.SqlClient namespace. I also prettied it up a bit with progress bar using Write-Progress.

#========================================================================
# Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.1.24
# Created on:   12/2/2013 8:06 AM
# Created by:   Rich Hopkins
# Filename:     InventoryAD.ps1
#========================================================================

#region Script Initializers
#Import Quest Powertools and the SQLPS module
Add-PSSnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 0

#Enable/Disable debugging by commenting the next line
#$debug = $true
$SQLServer = "SERVERNAME"
If ($debug) {$SQLDatabase = "Inventory_Dev"}
Else {$SQLDatabase = "Inventory"}

#Create persistant connection to SQL to keep Temp Tables alive
$conn = New-Object Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$SQLServer;Database=$SQLDatabase;Trusted_Connection=Yes;Integrated Security=SSPI;"
$conn.Open()
$SQLQuery = ""
$SQLCmd = New-Object System.Data.SqlClient.SqlCommand $SQLQuery, $conn
#endregion

#region Create Temporary Tables on SQL Server
$SQLQuery = "CREATE TABLE [$SQLDatabase].[dbo].[#ADUserAccounts](
	[UserID] [nvarchar](255) NOT NULL,
	[SID] [nvarchar](255) NOT NULL,
	[Account_Status] [nvarchar](255) NULL,
	[First_Name] [nvarchar](255) NULL,
	[Last_Name] [nvarchar](255) NULL,
	[Name] [nvarchar](255) NULL,
	[Common_Name] [nvarchar](255) NULL,
	[Display_Name] [nvarchar](255) NULL,
	[Initials] [nvarchar](255) NULL,
	[Address] [nvarchar](255) NULL,
	[PO_Box] [nvarchar](255) NULL,
	[City] [nvarchar](255) NULL,
	[State] [nvarchar](255) NULL,
	[Zip_Code] [nvarchar](255) NULL,
	[Telephone] [nvarchar](255) NULL,
	[Mobile Phone] [nvarchar](255) NULL,
	[Department_Number] [nvarchar](255) NULL,
	[Department] [nvarchar](255) NULL,
	[Office] [nvarchar](255) NULL,
	[Division] [nvarchar](255) NULL,
	[Employee_ID] [float] NULL,
	[Employee_Number] [nvarchar](255) NULL,
	[Employee_Type] [nvarchar](255) NULL,
	[Manager] [nvarchar](255) NULL,
	[Company] [nvarchar](255) NULL,
	[Title] [nvarchar](255) NULL,
	[Distinguished_Name] [nvarchar](255) NULL,
	[Home_Directory] [nvarchar](255) NULL,
	[Home_Drive] [nvarchar](255) NULL,
	[Mail] [nvarchar](255) NULL,
	[Internet_Access] [nvarchar](255) NULL,
	[Remote_Access_Permission] [nvarchar](255) NULL,
	[Last_Logon] [datetime] NULL,
	[Last_Logon_Domain_Controller] [nvarchar](255) NULL,
	[Account_Last_Modified] [datetime] NULL
)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null

$SQLQuery = "CREATE TABLE [$SQLDatabase].[dbo].[#ADUserGroups](
	[UserID] [nvarchar](255) NULL,
	[ADGroup] [nvarchar](255) NULL,
	[Description] [nvarchar](max) NULL
)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null

$SQLQuery = "CREATE TABLE [$SQLDatabase].[dbo].[#ADComputers](
	[Name] [nvarchar](255) NULL,
	[Status] [nvarchar](255) NULL,
	[AssetTag] [float] NULL,
	[CommonName] [nvarchar](255) NULL,
	[DisplayName] [nvarchar](255) NULL,
	[ManagedBy] [nvarchar](255) NULL,
	[Description] [nvarchar](255) NULL,
	[Department] [nvarchar](255) NULL,
	[Dept#] [float] NULL,
	[Info] [nvarchar](255) NULL,
	[SerialNumber] [nvarchar](255) NULL,
	[DNSHostName] [nvarchar](255) NULL,
	[SAMAccountName] [nvarchar](255) NULL,
	[CanonicalName] [nvarchar](255) NULL,
	[DistinguishedName] [nvarchar](255) NULL,
	[CreateTimeStamp] [datetime] NULL,
	[ModifyTimeStamp] [datetime] NULL,
	[PasswordLastSet] [datetime] NULL,
	[Location] [nvarchar](255) NULL,
	[Operating System] [nvarchar](255) NULL,
	[Operating System Service Pack] [nvarchar](255) NULL,
	[Operating System Version] [nvarchar](255) NULL
)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
#endregion

#region Inventory Enabled Users
#To save myself the headache of having to parse userAccountControl flags
#to see if a user is enabled to disabled, I just use to -enable modifier
#to first collect all enabled users, then next do another collection with
#-disabled.  Is this a cheap copout?  Yeah.  Does it work?  Yeah.
$userlist = Get-QADUser -Enabled -IncludedProperties userAccountControl,`
				departmentNumber,employeeID,employeeNumber,employeeType,`
				distinguishedName,lastLogon,whenChanged,cn,SID
#Setup for the progress bar
$totalTicks = 0
ForEach ($user in $userlist) {$script:totalTicks++}
$eachTick = 100/$totalTicks
$currentTick = 0
ForEach ($user in $userlist) {
	Write-Progress -Activity "Inventory Enabled Users" -Status "Processing $($user.displayName)" -PercentComplete $currentTick
	$status = "Enabled"
	#The manager property is pulled as a Distinguished Name, so I
	#use this regex to pull out just the managers name
	[string]$manager = ""
	If ($user.manager) {
		$manager = $user.manager
		$manager = $manager -replace "(CN=)(.*?),.*",'$2'
	}
	#Uses regex to find the users group that starts with "GS-WS-Equator"
	#These groups are used in our environment for internet access, so
	#I store it seperately in the database for easy access
	$groups = $user.memberOf
	ForEach ($group in $groups) {
		$group = $group.ToString()
		$group = $group -replace "(CN=)(.*?),.*",'$2'
		If ($group.StartsWith("GS-WS-Equator")) {[string]$iGroup = $group}
	}
	#determine the type of $RemoteAccess for the user
	$blnRemoteAccess = $user.msNPAllowDialing
	If (!$blnRemoteAccess) {$RemoteAccess = "Control access through Remote Access Policy"}
	ElseIf ($blnRemoteAccess -eq $true) {$RemoteAccess = "Access Granted"}
	ElseIf ($blnRemoteAccess -eq $false) {$RemoteAccess = "Access Denied"}
	#Force casting these properties as strings, so they can be used in the SQL Query
	[string]$departmentNumber = $user.departmentNumber
	[string]$employeeID = $user.employeeID
	[string]$employeeType = $user.employeeType
	#call Get-ADUserDCStats to get DC specific stats for the user
	$lastLogon = $user.lastLogon
	$lastLogonDC = ""
	$whenChangedTime = $user.whenChanged
	$SQLQuery = "INSERT INTO [$SQLDatabase].[dbo].[#ADUserAccounts] VALUES ('$($user.sAMAccountName)" + `
		"','$($user.SID)','$status','$($user.givenName)','$($user.sn)','$($user.name)','$($user.cn)" + `
		"','$($user.displayName)','$($user.initials)','$($user.streetAddress)','$($user.postOfficeBox)" + `
		"','$($user.l)','$($user.st)','$($user.postalCode)','$($user.telephoneNumber)','$($user.mobile)" + `
		"','$departmentNumber','$($user.department)','$($user.physicalDeliveryOfficeName)','$($user.division)" + `
		"','$employeeID','$employeeNumber','$employeeType','$manager','$($user.company)','$($user.title)" + `
		"','$($user.distinguishedName)','$($user.homeDirectory)','$($user.homeDrive)','$($user.mail)" + `
		"','$iGroup','$RemoteAccess','$lastLogon','$lastLogonDC','$whenChangedTime')"
	#Write-Output $SQLQuery
	$SQLCmd.CommandText = $SQLQuery
	$SQLCmd.ExecuteNonQuery() | Out-Null
	$currentTick += $eachTick
}
#endregion

#region Inventory Disabled Users
$userlist = Get-QADUser -Disabled -IncludedProperties userAccountControl,`
				departmentNumber,employeeID,employeeNumber,employeeType,`
				distinguishedName,lastLogon,whenChanged,cn,SID
#Setup for the progress bar
$totalTicks = 0
ForEach ($user in $userlist) {$script:totalTicks++}
$eachTick = 100/$totalTicks
$currentTick = 0
ForEach ($user in $userlist) {
	Write-Progress -Activity "Inventory Disabled Users" -Status "Processing $($user.displayName)" -PercentComplete $currentTick
	$status = "Disabled"
	#The manager property is pulled as a Distinguished Name, so I
	#use this regex to pull out just the managers name
	[string]$manager = ""
	If ($user.manager) {
		$manager = $user.manager
		$manager = $manager -replace "(CN=)(.*?),.*",'$2'
	}
	#Uses regex to find the users group that starts with "GS-WS-Equator"
	#These groups are used in our environment for internet access, so
	#I store it seperately in the database for easy access
	$groups = $user.memberOf
	ForEach ($group in $groups) {
		$group = $group.ToString()
		$group = $group -replace "(CN=)(.*?),.*",'$2'
		If ($group.StartsWith("GS-WS-Equator")) {[string]$iGroup = $group}
	}
	#determine the type of $RemoteAccess for the user
	$blnRemoteAccess = $user.msNPAllowDialing
	If (!$blnRemoteAccess) {$RemoteAccess = "Control access through Remote Access Policy"}
	ElseIf ($blnRemoteAccess -eq $true) {$RemoteAccess = "Access Granted"}
	ElseIf ($blnRemoteAccess -eq $false) {$RemoteAccess = "Access Denied"}
	#Force casting these properties as strings, so they can be used in the SQL Query
	[string]$departmentNumber = $user.departmentNumber
	[string]$employeeID = $user.employeeID
	[string]$employeeType = $user.employeeType
	#call Get-ADUserDCStats to get DC specific stats for the user
	#$lastLogon,$lastLogonDC,$whenChangedTime = Get-ADUserDCStats -userName $user.sAMAccountName
	$lastLogon = $user.lastLogon
	$lastLogonDC = ""
	$whenChangedTime = $user.whenChanged
	$SQLQuery = "INSERT INTO [$SQLDatabase].[dbo].[#ADUserAccounts] VALUES ('$($user.sAMAccountName)" + `
		"','$($user.SID)','$status','$($user.givenName)','$($user.sn)','$($user.name)','$($user.cn)" + `
		"','$($user.displayName)','$($user.initials)','$($user.streetAddress)','$($user.postOfficeBox)" + `
		"','$($user.l)','$($user.st)','$($user.postalCode)','$($user.telephoneNumber)','$($user.mobile)" + `
		"','$departmentNumber','$($user.department)','$($user.physicalDeliveryOfficeName)','$($user.division)" + `
		"','$employeeID','$employeeNumber','$employeeType','$manager','$($user.company)','$($user.title)" + `
		"','$($user.distinguishedName)','$($user.homeDirectory)','$($user.homeDrive)','$($user.mail)" + `
		"','$iGroup','$RemoteAccess','$lastLogon','$lastLogonDC','$whenChangedTime')"
	#Write-Output $SQLQuery
	$SQLCmd.CommandText = $SQLQuery
	$SQLCmd.ExecuteNonQuery() | Out-Null
	$currentTick += $eachTick
}
#endregion

#region Inventory Groups
#Create object of all AD Groups
$ADGroups = Get-QADGroup
#Setup for the progress bar
$totalTicks = 0
ForEach ($ADGroup in $ADGroups) {$script:totalTicks++}
$eachTick = 100/$totalTicks
$currentTick = 0
ForEach ($ADGroup in $ADGroups) {
	Write-Progress -Activity "Inventory Groups" -Status "Processing $($ADGroup.name)" -PercentComplete $currentTick
	$name = $ADGroup.name
	$desc = $ADGroup.description
	#If there is a ' character in the description it must be
	#replaced with '' for SQL to take it
	If($desc){If($desc -match "'"){$desc = $desc -replace "'","''"}}
	#Connect to the individual group for membership
	$Members = Get-QADGroupMember $name
	ForEach ($Member in $Members) {
		If ($Member) {$username = $Member.name}
		Else {$username = "NONE"}
		$SQLQuery = "INSERT INTO [$SQLDatabase].[dbo].[#ADUserGroups] VALUES ('$username','$name','$desc')"
		#Write-Output $SQLQuery
		$SQLCmd.CommandText = $SQLQuery
		$SQLCmd.ExecuteNonQuery() | Out-Null
	}
	$currentTick += $eachTick
}
#endregion

#region Inventory Computers
#Being that I am on the desktop team, my manager is only interested in systems running a desktop OS
$LdapFilter = '(|(operatingSystem=*Windows XP*)(operatingSystem=*Windows Vista*)(operatingSystem=*Windows 7*)(operatingSystem=*Windows 8*))'
$computers = Get-QADComputer -LdapFilter $LdapFilter -IncludedProperties `
				name,cn,userAccountControl,distinguishedName,createTimeStamp,departmentNumber,managedBy,info,`
				lastLogoff,lastLogon,modifyTimeStamp,pwdLastSet,whenChanged,whenCreated,department,serialNumber,`
				operatingSystem,operatingSystemHotfix,operatingSystemServicePack,operatingSystemVersion
#Setup for the progress bar
$totalTicks = 0
ForEach ($comp in $computers) {$script:totalTicks++}
$eachTick = 100/$totalTicks
$currentTick = 0
ForEach ($comp in $computers) {
	Write-Progress -Activity "Inventory Computers" -Status "Processing $($comp.name)" -PercentComplete $currentTick
	$AssetTag = 0
	#As part of our computer naming convention we put the
	#4 digit asset tag at the end of the computer name
	If ($comp.name -match "\d{4}") {$AssetTag = $($matches[0])}
	If ($comp.accountisdisabled) {$status = "Disabled"}
	Else {$status = "Enabled"}
	$description = $comp.description
	#If there is a ' character in the description it must be
	#replaced with '' for SQL to take it
	If ($description -match "'"){$description = $description -replace "'", "''"}
	#Force DateTime type casting
	If ($comp.createTimeStamp) {[DateTime]$createTimeStamp = $comp.createTimeStamp}
	[string]$departmentNumber = $comp.departmentNumber
	[string]$ManagedBy = $comp.managedBy -replace "(CN=)(.*?),.*",'$2'
	$SQLQuery = "INSERT INTO [$SQLDatabase].[dbo].[#ADComputers] VALUES ('$($comp.name)','$status','" + `
		"$AssetTag','$($comp.cn)','$($comp.displayName)','$ManagedBy','$description','" + `
		"$($comp.department)','$departmentNumber','$($comp.info)','$($comp.serialNumber)','" + `
		"$($comp.dNSHostName)','$($comp.sAMAccountName)','$($comp.canonicalName)','" + `
		"$($comp.distinguishedName)','$createTimeStamp','$($comp.modifyTimeStamp)','$($comp.pwdLastSet)" + `
		"','$($comp.location)','$($comp.operatingSystem)','$($comp.operatingSystemServicePack)','" + `
		"$($comp.operatingSystemVersion)')"
	#Write-Output $SQLQuery
	$SQLCmd.CommandText = $SQLQuery
	$SQLCmd.ExecuteNonQuery() | Out-Null
	$currentTick += $eachTick
}
#endregion

#region Drop current tables and replace with temp data
$SQLQuery = "DROP TABLE ADUserAccounts"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO ADUserAccounts FROM #ADUserAccounts"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "DROP TABLE ADUserGroups"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO ADUserGroups FROM #ADUserGroups"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "DROP TABLE ADComputers"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO ADComputers FROM #ADComputers"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
#endregion

#region Script Cleanup
$SQLCmd.Dispose()
$conn.Close()
$conn.Dispose()
#endregion
Advertisements
This entry was posted in Programming and tagged , , , , , , , . Bookmark the permalink.

2 Responses to Get a SQL Inventory of Active Directory with PowerShell

  1. Pingback: PoSH: Connecting to an Remote Domain | Rich Hopkins

  2. Pingback: Updating Active Directory using PowerShell and SQL | Rich Hopkins

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s