Formating Several out-file reports

by rambog at 2013-01-31 12:20:21

I am collecting a plethora of information from servers using multiple Win32 object classes. I am able to get the information I need but need to be able to format it in a manner of a CSV to make it easier to import into a database. I believe much of my problem stems from the fact that the Export-CSV cmdlet does not offer an -Append parameter. I have four enhancements I need to my code below:

1. Need to convert the InstallDate and LastBootUpTime from Win32_Operating System into a format humans can understand.
2. For the OSReport, I need to appropriately size the columns and simply have only one header row. The intent is to import into SQL.
3. The AdapterReport does not have all the columns. I have tried several iterations to get all the info to fit. If I use -auto, it does not show. Also, I only want one header row.
4. For some reason, the services report does not show MachineName. If I run the command manually on the console, it displays the MachineName.

Here is my code:
$ping=New-Object System.Net.NetworkInformation.Ping

Get-Content D:\scripts\VMList.txt |Foreach-object {
$Adapterdata=@()
$OSdata=@()
$Servicedata=@()
$LogicalDiskdata=@()
$reply=$ping.send($)
if ($reply.status -eq "Success") {
$OSRecord=gwmi Win32_OperatingSystem -comp $
|select CSName,Name,Version,ServicePackMajorVersion,InstallDate,LastBootUpTime
$OSdata+=$OSRecord
$AdapterRecord=gwmi Win32_NetworkAdapterConfiguration -filter IPEnabled=TRUE -comp $|select DNSHostName,Description,InterfaceIndex,IPAddress,IPSubnet,MACAddress,DefaultGateway,DHCPServer,DNSServerSearchOrder,DNSDomain,DNSDomainSuffixSearchOrder,DomainDNSRegistrationEnabled
$Adapterdata+=$AdapterRecord
$LogicalDiskRecord=gwmi Win32_LogicalDisk -comp $
|where {$.DeviceID -eq "C:" -or $.DeviceID -eq "D:" -or $.DeviceID -eq "E:"}
$LogicalDiskdata+=$LogicalDiskRecord
$ServiceRecord=get-service | where-object {$
.status -eq "Running"}
$Servicedata+=$ServiceRecord
}
else {

Write-Host "The server $_ is not reachable"
}

$Adapterdata|ft @{Label="Host";Expression={$.DNSHostName};width=25},@{Label="Desc";Expression={$.Description};Width=25},@{Label="IP";Expression={$.IPAddress};Width=30},@{Label="Subnet";Expression={$.IPSubnet};Width=25},@{Label="MAC";Expression={$.MACAddress};Width=25},@{Label="Gateway";Expression={$.DefaultGateway};Width=25},@{Label="DHCP Server";Expression={$.DHCPServer};Width=25},@{Label="Search Order";Expression={$.DNSServerSearchOrder};Width=25},@{Label="Domain";Expression={$.DNSDomain};Width=25}|Out-file d:\scripts\AdapterReport.csv -Append
$OSdata|ft |Out-file d:\scripts\OSReport.csv -Append
$LogicalDiskdata|ft SystemName,DeviceID,VolumeName,@{Label="Size(Gb)";Expression={($
.Size / 1gb) -as [int]}},@{Label="FreeSpace(Gb)";Expression={($.FreeSpace / 1gb) -as [int]}}|Out-File d:\scripts\LogicalDisk.csv -Append

}
$Servicedata|select MachineName,DisplayName,ServiceName|Export-CSV d:\scripts\Services.csv -NoTypeInformation


The Adatper report is:

WARNING: 6 columns do not fit into the display and were removed.

Host Desc IP
---- ---- –
Server203 Intel(R) PRO/1000 MT N… {10.10.221.139, fe80::38…



WARNING: 6 columns do not fit into the display and were removed.

Host Desc IP
---- ---- –
Server208 Intel(R) PRO/1000 MT N… {10.10.221.132, fe80::71…

The OS report looks like:

CSName Name Version ServicePackM InstallDate LastBootUpTi
ajorVersion me
------ ---- ------- ------------ ----------- ------------
Server203 Microsoft … 6.1.7600 0 201101271… 201301102…



CSName Name Version ServicePackM InstallDate LastBootUpTi
ajorVersion me
------ ---- ------- ------------ ----------- ------------
Server208 Microsoft … 6.1.7600 0 201101271… 201301102…

And the services report:

"MachineName","DisplayName","ServiceName"
".","Application Management","AppMgmt"
".","Base Filtering Engine","BFE"
".","Background Intelligent Transfer Service","BITS"
".","Symantec Event Manager","ccEvtMgr"
".","SMS Agent Host","CcmExec"
".","Symantec Settings Manager","ccSetMgr"
".","Certificate Propagation","CertPropSvc"
".","COM+ System Application","COMSysApp"
".","Cryptographic Services","CryptSvc"
".","CM Remote Client","CSIRemoteC"

Any help on any of the above four items would be helpful. Thanks.
by kittH at 2013-01-31 12:37:47
Would it be feasible to sidestep the issue and just write directly to SQL?
by rambog at 2013-01-31 13:45:53
I was able to correct the adapter report (by using Export-CSV) and moving the line,
$Adapterdata|Export-CSV d:\scripts\AdapterReport.csv -NoTypeInformation completely outside the loop for each machine and initializing the array befor I start looping through the contents of VMList. The problem I am seeing is that IPaddress (since it is mult-valued to include IPv4 and IPv6) shows up as "System.String[]". How is this corrected?

I still would like to see how to convert the dates as mentioned in my original post.
by rambog at 2013-01-31 13:47:21
It may be possible to go right to SQL but I will not know that for a couple of weeks. The requisition has been placed.
by DonJ at 2013-01-31 13:53:36
So, you’ve got a couple of things.

First, a CSV file isn’t meant to be used quite that way. Each row should have exactly the same data, which you’re not achieving by just appending different classes.

Do achieve what you’ve asked, you’d use ConvertTo-HTML with the -NoType and -NoHeader switches, and pipe to Out-File with its -Append switch. But that’s not a proper CSV file.

To do the right thing, you’d query all of your classes and create a custom object that contained all of the information. THAT could be exported in a single Export-CSV operation.

Going directly to SQL would be even better. PowerShell can do it quite easily (I’ve a chapter on it in Learn PowerShell Toolmaking in a Month of Lunches; you can download my Database module in the code samples for the book at www.morelunches.com if you like).

The problem you’re seeing with the IPAddress is yet another reason CSV files suck. What you’re looking at is a collection, and what you do depends on what you want. Want a comma-separated sub-list? Use the -split operator ($IPAddress -split ",").

Converting dates is easy. If $os contains your Win32_OperatingSystem class, $os.ConvertToDateTime($os.LastBootTime) does it. All WMI classes have the ConvertToDateTime() method - pipe the object to Get-Member and you’ll see it.

(As a side note - please consider using the CODE button in the toolbar… your script is a bit hard to follow because the formatting gets lost.)
by kittH at 2013-01-31 13:59:47
I use this in one of my scripts:
([WMI]'').ConvertToDateTime((Get-WmiObject win32_operatingsystem -computername $name).InstallDate).ToString("yyyy-MM-dd HH]

Which converts it to SQL compatible datetime format.
by DonJ at 2013-01-31 14:06:53
With the note, KittH, that you’re executing an entire WMI query against a remote machine to retrieve only one property and convert it. If you plan to use any of the other properties, it’s worth querying it, storing the result in a variable, and converting that - to avoid the extra round-trip query.

The ConvertToDateTime() method will return a DateTime object, not a String, which is what your approach is doing. SQL Server will accept a DateTime (the object’s default ToString() method returns a format that SQL can parse).

Your approach is pretty concise, though! Although that makes it a bit tough for a newcomer to interpret, I bet <grin>.

Both approaches are valid depending on your circumstances. Just be a bit careful about a needless extra WMI query just to convert the date.
by DonJ at 2013-01-31 14:24:23
BTW, rambog… aside from the technical questions here, could you give me a bit of information about what you’re trying to achieve? It seems as if you’re trying to build a little inventory system that stores data in SQL Server - yes/no? I’m asking because I’m working on a new book, and this might be one of the specific scenarios I address, and if so I’d like to quiz you a bit to make sure I"m hitting the right points.

I’m also wondering if you’ve considered the HTML reports book (free) at www.PowerShellBooks.com. It’s actually designed to produce these multi-section inventory reports like you’re after, although it focuses on just the report and not on storing data in SQL Server for later reporting. I’m curious what you think if it, and whether you find it helpful.
by rambog at 2013-01-31 14:45:35
DonJ,
Yes, I am building an inventory. We are converting Virtual Machines to a separate VM Cluster and IP addresses and I am wish to have a solid record of the condition of the VMs prior to the conversion. We also adjusting the size of the logical disks, installing some item, etc.

I will definately look at your HTML reports book. My next investigation is into the powershell cmdlets put out by VMWare to see how they could also assist me. Thank you for your excellent instruction.
by kittH at 2013-01-31 15:28:07
Here’s a scrubbed version of the script that we’re currently using in production for doing a system inventory and writing to AD and SQL… I’m sure a lot of things could be done differently/better but it may give you some ideas:
("badname" field assumes you want computername to equal users fullname, it also normally has a few big switch blocks for cleaning up processor/model names)

#PCInfo - Get Computer Info and Populate Database
#kittH - created 7/5/12
Add-PSSnapin Quest.ActiveRoles.ADManagement
Import-Module SQLPS
C:

#Get list of all computers in Headquarters
$computers = get-QADComputer -SearchRoot 'domain.com/headquarters' -SizeLimit 0

#Specify number of threads that should be spawned concurrently
$Threads = 8

#Begin Loop on each computer
foreach($computerjob in $computers)
{
$computersid = $computerjob.sid.value
While ($(Get-Job -state running).count -gt $Threads){Start-Sleep -Milliseconds 500}
start-job -argumentlist $computersid -scriptblock {

#Add Snappins
Import-Module SQLPS
Add-PSSnapin Quest.ActiveRoles.ADManagement
C:

#Define Database Info
$DBServer = "DB"
$Database = "PCInfo"
$Table = "dbo.PCInfo"
$SID = $args[0]
$Computer = Get-QADComputer $SID

#Ping the computer once, if no response, skip.
if (-not (Test-Connection $computer.dnsname -count 1 -quiet)) {exit}
$Name = $Computer.Name
write-host "$name ping passed" -foregroundcolor Green

#Get WMI Information
$WMIComputerSystem = Get-WmiObject win32_computersystem -computername $name
#If no response to the first WMI query, skip.
if (-not($WMIComputerSystem)){exit}
$WMIBIOS = Get-WmiObject win32_bios -computername $name
$WMIProcessor = Get-WmiObject win32_processor -computername $name
$WMILogicalDisk = Get-WMIObject win32_LogicalDisk -computername $name
$WMIOS = Get-WmiObject win32_operatingsystem -computername $name
$WMIGroup = Get-WMIObject win32_groupuser -computername $name
$WMIProfile = Get-WMIObject win32_userprofile -computername $name
$WMIEnclosure = Get-WMIObject win32_SystemEnclosure -computername $name
$WMISQL = Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 -computername $name -class SQLService
$WMINetwork = Get-WMIObject win32_networkadapter -computername $name

#Extract Data from WMI Info
$model = $WMIComputerSystem.Model
$memory = $WMIComputerSystem.TotalPhysicalMemory
$serial = $WMIBIOS.SerialNumber
$BIOS = $WMIBIOS.Version
$cpu = $WMIProcessor.Name
$disk = $WMILogicalDisk | ? {$
.DeviceID -eq "C:"}
$OS = $WMIOS.Caption
$SPLevel = $WMIOS.ServicePackMajorVersion
$OSDate = ([WMI]'').ConvertToDateTime(($WMIOS).InstallDate).ToString("yyyy-MM-dd HH:mm:ss")
$ChassisType = $WMIEnclosure.ChassisTypes
$NetworkAdapters = $WMINetwork | ? {$.MacAddress} | ? {(-not($.Description -match "Miniport|RAS|Bluetooth|Sonicwall"))}

#Get Local Admin users - This section modified from script by Paperclips on MS Technet http://gallery.technet.microsoft.com/sc … ns#content
$Admins = $WMIGroup | ? {$.groupcomponent -like '*"Administrators"'} | % {
$
.partcomponent –match “.+Domain=(.+),Name=(.+)$” > $nul
$matches[1].trim('"') + “\” + $matches[2].trim('"')} | ? {($_ -notlike "Domain\Domain Admins") -and ($_ -notlike "$name\Administrator")}

#Check if Computer Name matches Users Name
if ($WMIComputerSystem.Username){
$username = $WMIComputerSystem.username | Split-Path -Leaf
$adname = (Get-QADUser $username)
$adfullname = ($adname).FirstName + ($adname).LastName
$adfullname = $adfullname.Substring(0, [Math]::Min(15, $adfullname.length))
if ($adfullname -eq $name) {$BadName = 0}
if ($adfullname -ne $name) {$BadName = 1}}

#Get Dates
$Date = Get-Date -format "yyyy-MM-dd HH:mm:ss"
$DateCreated = $Computer.CreationDate.ToString("yyyy-MM-dd HH:mm:ss")

#Get SQLInstance
if ($WMISQL){$SQLInstance = ($WMISQL | ? {($.__RELPATH -like "*SQLServiceType=1") -and ($.State -eq 4)}).ServiceName}

#Get ProfilePath
$ProfilePath = ($WMIProfile | ? {($.Special -eq $false) -and ($.LocalPath -like "$username")}).LocalPath

#Get OU
$OU = $Computer.parentcontainer | split-path -leaf

#Get MAC Addresses
if($NetworkAdapters){$MAC = $NetworkAdapters | % {$.MacAddress}}
if($NetworkAdapters){$NICMAC = $NetworkAdapters | % {$
.Name + " - " + $.MacAddress}}

#Check if the computer has a bitlocker recovery code, and determine if bitlocker is enabled.
$BitKey = Get-QADObject -Type "msFVE-RecoveryInformation" -SizeLimit 0 -Includedproperties msFVE-RecoveryPassword -SearchRoot $computer.parentcontainer | ? {$
.ParentContainerDN -eq $computer.DN} | Select-Object -ExpandProperty 'msFVE-RecoveryPassword'
if ($BitKey.Count -gt 1){$Bitkey = $Bitkey[-1]}
if ($BitKey){$Bitlocker = $true}
if (-not($BitKey)){$Bitlocker = $false}

#Cleanup WMI Info and switch Model, CPU, and ChassisType to friendly names
$cpu = $cpu.Trim()
$model = $model.Trim()
$serial = $serial.Trim()
$serial = $serial.Replace('VMware-', '')
$HDFree = $Disk.FreeSpace / 1GB
$HDFree = [math]::round($HDFree, 0)
$HDSize = ($disk.size / 1GB)
$HDSize = [math]::round($HDSize, 0)
$HDPercent = ($disk.freespace / $disk.size) * 100
$HDPercent = [math]::round($HDPercent, 0)
$ChassisType = switch ($ChassisType)
{
"1" {"Other"}
"2" {"Unknown"}
"3" {"Desktop"}
"4" {"Low Profile Desktop"}
"5" {"Pizza Box"}
"6" {"Mini Tower"}
"7" {"Tower"}
"8" {"Portable"}
"9" {"Laptop"}
"10" {"Notebook"}
"11" {"Hand Held"}
"12" {"Docking Station"}
"13" {"All in One"}
"14" {"Sub Notebook"}
"15" {"Space-Saving"}
"16" {"Lunch Box"}
"17" {"Main System Chassis"}
"18" {"Expansion Chassis"}
"19" {"Sub Chassis"}
"20" {"Bus Expansion Chassis"}
"21" {"Peripheral Chassis"}
"22" {"Storage Chassis"}
"23" {"Rack Mount Chassis"}
"24" {"Sealed-Case PC"}
default {$ChassisType}
}
$memory = $memory/1GB
$memory = [math]::round($memory, 0)

#Comma Delineate Arrays into single line
$OFS = ', '
$Admins = "$Admins"
$ProfilePath = "$ProfilePath"
$MAC = "$MAC"
$NICMAC = "$NICMAC"

#Write data to AD Description
Set-QADComputer $computer -Description $model" | "$cpu" | "$memory"GB | "$HDPercent"% of "$HDSize"GB | "$serial

#Check if Computer exists in database already, update if it does.
$RowExists = (Invoke-Sqlcmd -Query "Select ID from $table with (nolock) where Name='$Name' and Serial='$Serial' and SID='$SID'" -ServerInstance $DBServer -Database $Database).id
if ($RowExists) {
if ($ADName){
Invoke-Sqlcmd -Query "Update $Table with (ROWLOCK) set Name='$Name', OS='$OS', Processor='$cpu', MemoryGB='$memory', HDSizeGB='$HDSize', HDFreeGB='$HDFree', Serial='$Serial', BadName='$BadName', Bitlocker='$Bitlocker', DateCreated='$DateCreated', ImageDate='$OSDate', LastUpdated='$Date', SID='$SID', LoggedOnUser='$username', OU='$OU', LocalAdmins='$Admins', ServicePack='$SPLevel', BIOS='$BIOS', ProfilePath='$ProfilePath', Model = '$Model', IsActive=1, ChassisType='$ChassisType', SQLInstance='$SQLInstance', BitLockerKey='$BitKey', MACAddress='$MAC', NIC='$NICMAC' where ID='$RowExists'" -ServerInstance "$DBServer" -Database "$Database"}
else{
Invoke-Sqlcmd -Query "Update $Table with (ROWLOCK) set Name='$Name', OS='$OS', Processor='$cpu', MemoryGB='$memory', HDSizeGB='$HDSize', HDFreeGB='$HDFree', Serial='$Serial', Bitlocker='$Bitlocker', DateCreated='$DateCreated', ImageDate='$OSDate', LastUpdated='$Date', SID='$SID', OU='$OU', LocalAdmins='$Admins', ServicePack='$SPLevel', BIOS='$BIOS', Model = '$Model', IsActive=1, ChassisType='$ChassisType', SQLInstance='$SQLInstance', BitLockerKey='$BitKey', MACAddress='$MAC', NIC='$NICMAC' where ID='$RowExists'" -ServerInstance "$DBServer" -Database "$Database"}}
#if Computer does not exist, create an entry for it.
else{
Invoke-Sqlcmd -Query "Insert into $table with (ROWLOCK) (Name, OS, Processor, MemoryGB, HDSizeGB, HDFreeGB, Serial, Bitlocker, DateCreated, ImageDate, LastUpdated, SID, LoggedOnUser, OU, LocalAdmins, ServicePack, BIOS, ProfilePath, Model, IsActive, ChassisType, SQLInstance, BitLockerKey, ActiveDate, MACAddress, NIC) values ('$Name', '$OS', '$cpu', '$memory', '$HDSize', '$HDFree', '$Serial', '$Bitlocker', '$DateCreated', '$OSDate', '$Date', '$SID', '$Username', '$OU', '$Admins', '$SPLevel', '$BIOS', '$ProfilePath', '$Model', 1, '$ChassisType', '$SQLInstance', '$BitKey', '$Date', '$MAC', '$NICMAC')" -ServerInstance "$DBServer" -Database "$Database"}}}

#Cleanup PCInfo Database, get last accessed date, set inactive computers.
$DBServer = "DBServer"
$Database = "PCInfo"
$Table = "dbo.PCInfo"
$Date = Get-Date -format "yyyy-MM-dd HH:mm:ss"

#Select Active Computers
$Computers = Invoke-Sqlcmd -Query "Select Name, ID, SID, Serial from $Table where IsActive='1'" -ServerInstance $DBServer -Database $Database

#Database Cleanup - Mark computers Inactive if they do not match a computer in AD by Name, Serial, and SID.
Foreach ($Computer in $Computers)
{
$Name = $Computer.Name
$ID = $Computer.ID
$SID = $Computer.SID
$Serial = $Computer.Serial
#Get AD Computer Account
$ADComputer = Get-QADComputer $SID
#If there is no computer account, set IsActive to False
If (-Not($ADComputer)){Invoke-Sqlcmd -Query "Update $Table Set IsActive='0', InActiveDate='$Date' where ID='$ID'" -ServerInstance $DBServer -Database $Database}
Else {
#Write modification date to determine unused computers
$DateAccessed = $ADComputer.ModificationDate.ToString("yyyy-MM-dd HH:mm:ss")
Invoke-Sqlcmd -query "Update $Table set DateAccessed='$DateAccessed' where ID='$ID'" -ServerInstance $DBServer -Database $Database
#Check if Serial number matches
$ADSerial = $ADComputer | ? {$_.Description -like "$Serial"}
#If there is a computer account, verify SID and Serial are the same, if not set IsActive to false.
If((-Not($ADSerial)) -or ($ADComputer.name -ne $Computer.name)){Invoke-Sqlcmd -Query "Update $Table Set IsActive='0', InActiveDate='$Date' where ID='$ID'" -ServerInstance $DBServer -Database $Database}}}