Recently I was asked to deliver a report on the count of machines in AD and their location. In our environment we use a computer name identifier to designate location. But, you could use OU information or eve AD Site information. So, I wrote a PowerShell script that grabs all three and puts the information into SQL. You can then merge the tables together and create a view. You may be asking, “doesn’t SCCM System Discovery provide this information?” Well, yes and no. My PowerShell script returns more computers than SCCM is discovering. Plus, the PowerShell script also returns information from AD Sites and Services as well as OU Description that can’t be obtained using SCCM discovery.
Get-ADComptuerInformation
Import-Module[DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
# Get all of the domain names
# Connect to RootDSE
$rootDSE = [ADSI]"LDAP://RootDSE"
# Connect to the Configuration Naming Context
$configSearchRoot = [ADSI]("LDAP://" + `
$rootDSE.Get("configurationNamingContext"))
# Configure the filter
$filter = "(NETBIOSName=*)"
# Search for all partitions where the NetBIOSName is set
$configSearch = New-Object DirectoryServices.DirectorySearcher($configSearchRoot, $filter)
# Configure search to return dnsroot and ncname attributes
$retVal = $configSearch.PropertiesToLoad.Add("dnsroot")
$retVal = $configSearch.PropertiesToLoad.Add("ncname")
#$configSearch.FindAll() | Select-Object @{n="dnsroot";e={$_.Properties.dnsroot}}, @{n="ncname";e={$_.Properties.ncname}}
#$DomainNames = foreach ($i in $configSearch.FindAll()) {$i.Properties.ncname}
$DomainNames = foreach ($i in $configSearch.FindAll()) {@{[string]$i.Properties.ncname = [string]$i.Properties.dnsroot}}
# Get computer information from AD into SQL
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SQL SERVER NAME]; Initial Catalog=[SCCM DATABASE NAME]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADComputerInformation"
$cmd.ExecuteNonQuery()
foreach ($domain in $DomainNames) {
#$LDAPDomainName = $domain.Keys
#$LDAPDomainName = "`'$LDAPDomainName'"
#[string]$DNSDomainName = $domain.Values
$ADService = Connect-QADService -Service $domain.Values
$ComputersInDomain = Get-QADComputer -SearchRoot $ADService.DefaultNamingContextDN -service $ADService.Domain.Name -SearchScope Subtree -Inactive:$false |
Select OperatingSystem, ParentContainerDN, Name, domain
# Write to SQL
foreach ($i in $ComputersInDomain) {
$cmd = $conn.CreateCommand()
$OperatingSystem = $i.OperatingSystem.ToString()
$ParentContainerDN = $i.ParentContainerDN.ToString()
$Name = $i.Name.ToString()
$domain = $i.domain.ToString()
$cmd.CommandText ="INSERT ADComputerInformation VALUES ('$OperatingSystem','$ParentContainerDN', '$Name', '$domain')"
$cmd.ExecuteNonQuery()
}
Clear-Variable computersindomain
}
$conn.Close()
# Old working area
#$var = Get-QADComputer -SearchRoot['LDAP DOMAIN] -SearchScope Subtree -SizeLimit 1 -Inactive:$false |
# group operatingsystem | sort count -Descending
#
#$var = $var.Group
# SQL
#-- USE [SCCM DATABASE]
#GO
#
#/****** Object: Table [dbo].[ADComputerInformation] Script Date: 07/29/2011 09:09:15 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADComputerInformation](
# [OperatingSystem] [nvarchar](256) NULL,
# [ParentContainerDN] [nvarchar](max) NULL,
# [Name] [nvarchar](256) NULL,
# [Domain] [nvarchar](256) NULL
#) ON [PRIMARY]
#
#GO
Get-ADOUInfo
Import-Module [DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
$var = Get-QADObject -Type 'organizationalUnit' | select DN, Description, ParentContainerDN
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SQL Servers]; Initial Catalog=[SCCM Database]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADOUInformation"
$cmd.ExecuteNonQuery()
foreach ($i in $var) {
if ($i.Description -eq $Null) {$description = $Null}
else {$description = $i.Description.ToString()}
if ($i.DN -eq $null) {$DN = $null}
else {$DN = $i.DN.ToString()}
if ($i.ParentcontainerDN -eq $null) {$ParentContainerDN = $Null}
else {$ParentContainerDN = $i.ParentContainerDN.tostring()}
$cmd.CommandText ="INSERT ADOUInformation VALUES ('$DN','$description', '$ParentContainerDN' )"
$cmd.ExecuteNonQuery()
}
$conn.Close()
#SQL
#
#-- USE [SCCM DATABASE]
#GO
#
#/****** Object: Table [dbo].[ADOUInformation] Script Date: 07/29/2011 10:44:46 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADOUInformation](
# [DN] [nvarchar](max) NULL,
# [Description] [nvarchar](256) NULL,
# [ParentContainerDN] [nvarchar] (max) NULL
#) ON [PRIMARY]
#
#GO
Get-ADSiteInfo
Import-Module [DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
$var = Get-QADObject -Searchroot "CN=Subnets,CN=Sites,CN=Configuration,DC=[FOREST ROOT],DC=[FOREST ROOT]" -includedproperties "Description", "Location" | select description, location
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SCCM SERVER]; Initial Catalog=[SCCM Database]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADSiteInformation"
$cmd.ExecuteNonQuery()
foreach ($i in $var) {
if ($i.Description -eq $null) {$description = $null}
else {$description = $i.Description.ToString()}
if ($i.location -eq $null) {$location = $null}
else {$location = $i.location.ToString()}
$cmd = $conn.CreateCommand()
$cmd.CommandText ="INSERT ADSiteInformation VALUES ('$location','$description' )"
$cmd.ExecuteNonQuery()
}
$conn.Close()
#SQL Code
#
#-- USE [SCCM DATABASE]
#GO
#
#/****** Object: Table [dbo].[ADSiteInformation] Script Date: 07/28/2011 13:18:14 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADSiteInformation](
# [LocationCode] [nchar](10) NULL,
# [LocationDescription] [nvarchar](max) NULL
#) ON [PRIMARY]
#
#GO