Inventory AD Computers

Again, to use Active Directory data as for cross reference with other systems, I grab all the computer accounts and put them into SQL. Granted, the INSERT is based on my own tables schema, but I think you can get the point of what I’m doing here. One thing to note when inventorying AD into SQL… SQL does not accept NULLs for dates, so for any date you try to pull from AD, you need to do it with the If to verify it exists first.
 

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].[ADComputers]" -ServerInstance $SQLServer -Database $SQLDatabase

$LdapFilter = '(|(operatingSystem=*Windows XP*)(operatingSystem=*Windows Vista*)(operatingSystem=*Windows 7*)(operatingSystem=*Windows 8*))'

# Create array of AD Computers that match the LDAP filter
$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

ForEach ($comp in $computers) {
    $AssetTag = "" #declared so if a match isn't found, it inserts a blank value into sql
    #if 4 digits are found at the end of the computer name, use the match for $AssetTag
    If ($comp.name -match "d{4}") {$AssetTag = $($matches[0])} 
    #a very handy property that I wish was included with user accounts
    If ($comp.accountisdisabled) {$status = "Disabled"} 
    Else {$status = "Enabled"}
    $description = $comp.description
    #if there is a ' used in the computer description, an extra ' needs to be appended for sql to read it correctly.
    If ($description -match "'"){$description = $description -replace "'", "''"}
    If ($comp.createTimeStamp) {[DateTime]$createTimeStamp = $comp.createTimeStamp}
    [string]$departmentNumber = $comp.departmentNumber
    #uses a RegEx replace to grab the manager name that is originally shown in Distinguished Name formatting
    [string]$ManagedBy = $comp.managedBy -replace "(CN=)(.*?),.*",'$2'
    If ($comp.serialNumber) {[string]$serialNumber = $comp.serialNumber[0].ToString()}
    $SQLQuery = "INSERT INTO [$SQLDatabase].[dbo].[ADComputers] VALUES ('" + $comp.name + "','$status','" + $AssetTag + "','" `
      + $comp.cn + "','" + $comp.displayName + "','$ManagedBy','$description','" + $comp.department + "','$departmentNumber','" `
      + $comp.info + "','$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
    Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
}

 

Advertisements
This entry was posted in Programming and tagged , , . Bookmark the permalink.

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