I am in the process of setting up a CMDB.  I want to feed it all of the relevant WMI data I can remotely gather from my servers.  Ultimately this is what I would like to do via Powershell:

• Query AD for a list of servers to attempt to pull WMI data from
• Iterate through each server and poll them for WMI data from different objects (ComputerSystem, OperatingSystem, etc)
• Output a single line for each server to a CSV file using the names required for the CMDB

Once I have the file then I would have a scheduled job import the CSV file into the CMDB,  add any new records and merging any changes.

I have manually performed this process using the premium verison of LANSweeper with a custom report using joins between the tables on the ComputerName field,  converting the output to CSV and then importing it into the CMDB.

I have found the folliwng PS snippet (from: http://synjunkie.blogspot.com/2008/09/powershell-scripts-for-ad-management.html ):

gwmi -query "SELECT SystemName,Caption,VolumeName,Size,Freespace FROM win32_logicaldisk WHERE DriveType=3" -computername servanem | Select-Object SystemName,Caption,VolumeName,@{Name="Size(GB)"; Expression={"{0:N2}" -f ($_.Size/1GB)}},@{Name="Freespace(GB)"; Expression={"{0:N2}" -f ($_.Freespace/1GB)}}, @{n="% Free";e={"{0:P2}" -f ([long]$_.FreeSpace/[long]$_.Size)}} | sort "% Free" |export-csv -notype c:\Disk-GB.csv

Is it possible to do the SQL style joins via this Select statement so that I can output a single line at a time to the CSV,  or is there a better way to do this?

Thanks

-MattG

Matt, I didn't get a chance to finish, but have a look at this. It may or may not work. :)

$outputFile = "c:\path\to\file.csv"

function New-ObjectWmiProperty ( $property ) {
	Process {
		$computer = $_
		switch ( $property ) {
			"SerialNumber" {
				$output = ( Get-WmiObject Win32_OperatingSystem -comp $computer.Name ).SerialNumber
				$computer | Add-Member -name SerialNumber -memberType NoteProperty `
					-value $output -passThru # passThru sends obj down pipeline
				}
			# repeat for each item to extract, using above template
		}
	}
}

# $ADComputers = Get-QADComputer # read help file for how to filter results here
$ADComputers = "." # for testing... can also do array e.g. "pc1", "pc2"
$output = $() # empty array
foreach ( $server in $ADComputers ) {
	$server | New-ObjectWmiProperty SerialNumber
	$server | New-ObjectWmiProperty AnotherKeyword
	$output += $server
} 
$output | Export-Csv $outputFile

Hal,

Thanks for the reply.  I cannot figure out how to get the syntax of your suggestion to work?

Would something like this work:

#This retrieves the computer name of all computer accounts with NT or Server in their description where they are not disabled accounts:

 $ADComputers = Get-QADComputer -ComputerRole 'Member' -ldapfilter '(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))(|(operatingSystem=*Server*)(&(operatingSystem=*NT*))))'|select name

      foreach ( $server in $ADComputers ) {

$InstallDate = gwmi -query "SELECT InstallDate from win32_operatingsystem" -computername $server|select-object InstallDate
$Caption = gwmi -query "SELECT Caption from win32_operatingsystem" -computername $server|select-object Caption

#Need command here to concatenate the fields for output?

} 
$output | Export-Csv $outputFile 

Thanks,
-MattG

I am thinking it might be best to just do a csv per query and then perform the CMDB imports with the various CSVs.

Here is an example of an OS specific CSV that was created with all enabled Windows Servers listed in AD:

Get-QADComputer -ComputerRole 'Member' -ldapfilter '(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))(|(operatingSystem=*Server*)(&(operatingSystem=*NT*))))' | foreach {

$name = $_.name
$ping = gwmi win32_pingstatus -filter "address='$name'"

if($ping.statusCode -eq 0){
$srv = gwmi win32_operatingSystem -computer $name
$srv | select CSName,ServicePackMajorVersion,BuildNumber
}

} | export-csv c:\os.csv -noType


2 questions:
1. How do I change the column names for the exported csv data. My CMDB expects specific column headers.
2. When I run this command I get a handled of connect errors, how I log these error to a log. The errors come up in PS when I execute this command, but don't show the server that had an issue.

Thanks,
-MattG

Hal,

Thanks.  That works.

-MattG

I figured it out:

add-pssnapin Quest.ActiveRoles.ADManagement
 

Connect-QADService -Service DCSRV1
$AD = Get-QADComputer -ldapfilter '(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))(|(operatingSystem=*Server*)(&(operatingSystem=*NT*))))'
 

Connect-QADService -Service DCSRV2
$AD += Get-QADComputer -ldapfilter '(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))(|(operatingSystem=*Server*)(&(operatingSystem=*NT*))))'
 

$AD |foreach {
    
    $name = $_.name
    $ping = gwmi win32_pingstatus -filter "address='$name'"
    
    if($ping.statusCode -eq 0){
    $srv = gwmi win32_operatingSystem -computer $name
    $srv | select @{Name="Name";Expression={$_.Csname}},@{Name="os_serial";Expression={$_.SerialNumber}},@{Name="Os_Swproduct_Id";Expression={$_.Caption}},@{Name="ad_ou";Expression={$_.Caption}}
      }
  } | export-csv c:\newos.csv -noType

Thanks,

-MattG

So here is my current code. I was able to assign each Select-Object filter to a variable which make my code easier to understand when I need to make changes. This code works.

Now I want to make it easier to include/exclude WMI fields from the query/output. Ideally the code would be setup so that the max fields are incldued in the code, and the user can comment out one line and the field would be excluded. I have tried to make the $field an array, but it complains that the "Name" field is already in use. I have tried to make the $fields strings, but that doesn't work when I try to use the variable down in the select statement. If I can figure out the mechanics of addind the appropriate variables to the Select statement, then I can figure out the IF THEN logic. Any suggestions? Thanks, -MattG

add-pssnapin add-pssnapin Quest.ActiveRoles.ADManagement
    

Connect-QADService -Service DCSRV1
    $AD = Get-QADComputer -ldapfilter '(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))(|(operatingSystem=*Server*)(&(operatingSystem=*NT*))))'
    Connect-QADService -Service DCSRV2
    $AD += Get-QADComputer -ldapfilter '(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))(|(operatingSystem=*Server*)(&(operatingSystem=*NT*))))'


$field1 = @{Name="name";Expression={$_.Csname}}
$field2 = @{Name="os_serial";Expression={$_.SerialNumber}}
$field3 = @{Name="Os_Swproduct_Id";Expression={$_.Caption}}
$field4 = @{Name="ad_ou";Expression={$_.Caption}}
$field5 = @{Name="os_patch_level";Expression= {$_.csdversion}}
$field6 = @{Name="installdate";Expression= {$_.ConvertToDateTime($_.installdate)}}
$field7 = @{Name="lastbootuptime";Expression= {$_.ConvertToDateTime($_.lastbootuptime)}}
$field8 = @{Name="othertypedescription";Expression= {$_.othertypedescription}}
$field9 = @{Name="os_organization";Expression= {$_.organization}}
$field10 = @{Name="os_registereduser";Expression= {$_.registereduser}}


$AD |foreach {
    
    $name = $_.name
    $ping = gwmi win32_pingstatus -filter "address='$name'"
    
    if($ping.statusCode -eq 0){
    $srv = gwmi win32_operatingSystem -computer $name
    
    $srv | select $field1,$field2,$field3,$field4,$field5,$field6,$field7,$field8,$field9,$field10
      }

    
  } | export-csv e:\easycmdb\import\csv\newos.csv -noType

Hi Matt

I have two suggestions, one is good and the other is even better IMO.

You can include all select's calculated properties as a collection and pass that collection to select object. $field1 = @{Name="name";Expression={$_.Csname}}
$field2 = @{Name="os_serial";Expression={$_.SerialNumber}}
(...)
$fields = $field1,$field2,$field3,$field4,$field5,$field6,$field7,$field8,$field9,$field10

... | select $fields

That way your users need to remove just the field(s) they don't want in the output, including the comma (see bolded text).

The second, and my favorite way... Teach your script users how to use the format-* cmdlets. You're already asking them to edit the script, so I'm sure that using the format-* cmdlets would be easier and much fun for them. You will need to remove the export-csv call so they can run the script and pipe (function) and pipe the results to format-table:
 

PS > .\script.ps1 | format-table $field1,$field6,$field7,$field10 

Here is my version of your script, keep in mind that the ldap query part (operatingSystem=*NT*) will capture the following values also, be sure to fine tune it:

Windows NT
Windows VistaT Enterprise

 
Also add -sizeLimit 0 to Get-QADUser to bypass the default 1000 objects limitation. 

$filter = '(&(!(userAccountControl:1.2.840.113556.1.4.803:=2))(|(operatingSystem=*Server*)(&(operatingSystem=*NT*))))'
$field1 = @{Name="name";Expression={$_.Csname}}
$field2 = @{Name="os_serial";Expression={$_.SerialNumber}}
$field3 = @{Name="Os_Swproduct_Id";Expression={$_.Caption}}
$field4 = @{Name="ad_ou";Expression={$_.Caption}}
$field5 = @{Name="os_patch_level";Expression= {$_.csdversion}}
$field6 = @{Name="installdate";Expression= {$_.ConvertToDateTime($_.installdate)}}
$field7 = @{Name="lastbootuptime";Expression= {$_.ConvertToDateTime($_.lastbootuptime)}}
$field8 = @{Name="othertypedescription";Expression= {$_.othertypedescription}}
$field9 = @{Name="os_organization";Expression= {$_.organization}}
$field10 = @{Name="os_registereduser";Expression= {$_.registereduser}}
$fields = $field1,$field2,$field3,$field4,$field5,$field6,$field7,$field8,$field9,$field10

"DCSRV1","DCSRV2" | foreach {
    $null = Connect-QADService -Service $_
    Get-QADComputer -ldap $filter -sizeLimit 0 | foreach {$_.name}  | where { (gwmi win32_pingstatus -filter "address='$_'").statusCode -eq 0 } | foreach {
    gwmi win32_operatingSystem -computer $_ | select $fields
} | export-csv e:\easycmdb\import\csv\newos.csv -noType