Inventory AD Groups

This PowerShell script grabs every group listed in Active Directory, then parses through the members and uploads it into SQL. The purpose was to get each user entry as a separate row to be used later in COUNT() functions and the like. This is then run each night to refresh the database from the days changes.
 

Add-PSSnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 0
Import-Module SQLPS -DisableNameChecking

#region Debug
$Debug = $true
$SQLServer = "SERVERNAME"
If ($Debug) {$SQLDatabase = "Inventory_Dev"}
Else {$SQLDatabase = "Inventory"}
#endregion

# Clear the current table
Invoke-Sqlcmd -Query "DELETE FROM [$SQLDatabase].[dbo].[ADUserGroups]" -ServerInstance $SQLServer -Database $SQLDatabase

$ADGroups = Get-QADGroup
ForEach ($ADGroup in $ADGroups) {
    $name = $ADGroup.name
    $desc = $ADGroup.description
    # if there is a ' used in the description, an extra ' needs to be appended for sql to read it correctly.
    If($desc){If($desc -match "'"){$desc = $desc -replace "'","''"}}
    $Members = Get-QADGroupMember $name
        ForEach ($Member in $Members) {
            $username = $Member.sAMAccountName
            $SQLQuery = "INSERT INTO [$SQLDatabase].[dbo].[ADUserGroups] VALUES ('$username','$name','$desc')"
            Write-Output $SQLQuery
            Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
        }
    }

# makes clear which groups have no members
$SQLQuery = "UPDATE [ADUserGroups] SET UserID = 'NONE' WHERE UserID = ''"
Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
Advertisements
This entry was posted in Programming and tagged , , , , . Bookmark the permalink.

One Response to Inventory AD Groups

  1. Rich Hopkins says:

    Note to self. Remember to remove the server’s name from scripts before posting them. 😉

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