Populate Excel Spreadsheet with WMI data

  7 Responses to “Populate Excel Spreadsheet with WMI data”

1. 

   Robert Fergus says:

   January 5, 2010 at 7:13 am

   I really liked this script, however I needed an easy way to populate the computer list. There are seveal ways to do this, but I chose to use the “Net View” command. I have fitted this into this into “Populate Excel Spreadsheet with WMI data”.

   NOTE: For usability the REGEX for matching computer names is very broad “[A-Z].*” it is in line 37. This expression should be more specific to eliminate false entries. The “Net View” portion of the script works well, however it could be improved.

   # Begin Script #
   #
   #Wmi-to-Excel.ps1 – 12/02/2009 by Kahuna at #http://PoshTips.com
   #Updated – 01/05/2010 by Robert Fergus
   #

   $erroractionpreference = “SilentlyContinue”
   $a = New-Object -comobject Excel.Application
   $a.visible = $True

   $b = $a.Workbooks.Add()
   $c = $b.Worksheets.Item(1)

   #define column headers for the first row of the spreadsheet
   $ColHeaders = (“Organization”,”Server Name”,”Operating System”,”Service Packs”,
   “System Type”,”Install Date”,”Manufacturer”,”Model”,”Service Tag”,
   “Serial Number”,”Number of Processors”,”Total Physical Memory (GB)”,
   “Last Reboot Time”,”Report TimeStamp”)
   $idx=0
   #write the column headings to the spreadsheet
   foreach ($title in $ColHeaders) {
   $idx+=1
   $c.Cells.Item(1,$idx) = $title
   }

   $d = $c.UsedRange
   $d.Interior.ColorIndex = 19
   $d.Font.ColorIndex = 11
   $d.Font.Bold = $True

   $intRow = 2

   ### Empty array for the computer name list ###
   $ComputerList = @()
   ### Run Net View for Netbios computer list ###
   $Net_view = & net view
   ### Clean up the data for the computer list ###
   Foreach ($Index in $Net_View)
   {$Net_View_Data = [regex]::Match(“$Index”,”[A-Z].*”) ## Change regex to match computer name patterns ###
   IF ($Net_View_Data -match ‘^$’)
   {
   ### Nothing to do here for this script ###
   }
   ELSE {
   $Clean_ComputerNameData = [regex]::Replace(“$Net_View_Data”,”\s.*$”,”")
   $ComputerList += (,($Clean_ComputerNameData))
   }
   }
   Foreach ($strComputer in $ComputerList)
   {
   ### This will trap cmdlet issues and can be used for error handling ###
   Trap {
   # Log error to a file if needed #
   Continue
   }

   write-host “Processing $strComputer…”
   $OS = gwmi -computername $strComputer Win32_OperatingSystem
   $Computer = gwmi -computername $strComputer Win32_computerSystem
   $Bios = gwmi -computername $strComputer win32_bios

   #populate eash row of the spreadsheet with data collected from WMI
   $c.Cells.Item($intRow,1) = $OS.Organization
   $c.Cells.Item($intRow,2) = $strComputer.Toupper()
   $c.Cells.Item($intRow,3) = $OS.Caption
   $c.Cells.Item($intRow,4) = $OS.CSDVersion
   $c.Cells.Item($intRow,5) = $Computer.SystemType
   $c.Cells.Item($intRow,6) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.InstallDate)
   $c.Cells.Item($intRow,7) = $Computer.Manufacturer
   $c.Cells.Item($intRow,8) = $Computer.Model
   $c.Cells.Item($intRow,9) = $Bios.serialnumber
   $c.Cells.Item($intRow,10) = $OS.SerialNumber
   $c.Cells.Item($intRow,11) = $Computer.NumberOfProcessors
   $c.Cells.Item($intRow,12) = “{0:N0}” -f ($computer.TotalPhysicalMemory/1GB)
   $c.Cells.Item($intRow,13) = [System.Management.ManagementDateTimeconverter]::ToDateTime($OS.LastBootUpTime)
   $c.Cells.Item($intRow,14) = Get-date

   $intRow += 1
   }
2. 

   Kahuna says:

   January 8, 2010 at 7:49 am

   Thanks for your contribution Robert.

   Using “net view” to generate a host list is a good option and multi-domain environments (like mine) could use net view’s “/DOMAIN:domain-name” switch if necessary.
3. 

   Jean says:

   July 28, 2010 at 3:02 am

   Hello Crew,

   i created a script to read the harddisks and the freespace on this vollumes.
   The values are written in a excel sheet.
   However, if a computer has more than 1 harddisk, the excelsheet is populated in the colom.
   I would like to have the output per host in one line and the next host in an other line.

   Here is the Script.

   $a = New-Object -comobject Excel.Application

   $a.Visible = $True

   $b = $a.Workbooks.Add()
   $c = $b.Worksheets.Item(1)

   $c.Cells.Item(1,1).value2 = “Hostname”
   $c.Cells.Item(1,2).value2 = “Partition”
   $c.Cells.Item(1,3).value2 = “Volume”
   $c.Cells.Item(1,4).value2 = “Grösse (GB)”
   $c.Cells.Item(1,5).value2 = “Frei (GB)”
   $c.Cells.Item(1,6).value2 = “Partition”
   $c.Cells.Item(1,7).value2 = “Volume”
   $c.Cells.Item(1,8).value2 = “Grösse (GB)”
   $c.Cells.Item(1,9).value2 = “Frei (GB)”
   $c.Cells.Item(1,10).value2 = “Partition”
   $c.Cells.Item(1,11).value2 = “Volume”
   $c.Cells.Item(1,12).value2 = “Grösse (GB)”
   $c.Cells.Item(1,13).value2 = “Frei (GB)”
   $b.SaveAs(“C:\temp\Test.xls”)

   $i = 2

   Get-WMIObject Win32_LogicalDisk -filter “DriveType=3″ -computer localhost | Select SystemName,DeviceID,VolumeName,@{Name=”size(GB)”;Expression={“{0:N1}” -f($_.size/1gb)}},@{Name=”freespace(GB)”;Expression={“{0:N1}” -f($_.freespace/1gb)}} | foreach {
   $c.Cells.Item($i,1).value2=”{0}” -f $_.SystemName
   $c.Cells.Item($i,2).value2=”{0}” -f $_.DeviceID
   $c.Cells.Item($i,3).value2=”{0}” -f $_.VolumeName
   $c.Cells.Item($i,4).value2=”{0}” -f $_.”size(GB)”
   $c.Cells.Item($i,5).value2=”{0}” -f $_.”freespace(GB)”

   $i++

   }

   #resize the columns to fit the data
   $d.EntireColumn.AutoFit() |out-null

   Thanks Jean
4. 

   Kahuna says:

   July 28, 2010 at 9:36 am

   Hi Jean, looks like you just need to create two separate looping mechanisms.

   An outer loop to process a list of computers
   An inner loop to process the drives found on each computer

   Here’s a quick mock-up where I have used a hard-coded list of computer names in an array called $computerList and also use a new variable $colOffset to place each drive in the appropriate Excel column (all on a single line). Note that $i gets incremented for each new $computer now (instead of for drives) and $colOffset gets incremented for each new drive.

   $a = New-Object -comobject Excel.Application

   $a.Visible = $True

   $b = $a.Workbooks.Add()
   $c = $b.Worksheets.Item(1)

   $c.Cells.Item(1,1).value2 = “Hostname”
   $c.Cells.Item(1,2).value2 = “Partition”
   $c.Cells.Item(1,3).value2 = “Volume”
   $c.Cells.Item(1,4).value2 = “Grösse (GB)”
   $c.Cells.Item(1,5).value2 = “Frei (GB)”
   $c.Cells.Item(1,6).value2 = “Partition”
   $c.Cells.Item(1,7).value2 = “Volume”
   $c.Cells.Item(1,8).value2 = “Grösse (GB)”
   $c.Cells.Item(1,9).value2 = “Frei (GB)”
   $c.Cells.Item(1,10).value2 = “Partition”
   $c.Cells.Item(1,11).value2 = “Volume”
   $c.Cells.Item(1,12).value2 = “Grösse (GB)”
   $c.Cells.Item(1,13).value2 = “Frei (GB)”
   $b.SaveAs(“C:\temp\Test.xls”)

   $i = 2
   $computerList = “host1″,”host2″,”host3″

   ForEach ($computer in $computerList)
   {
   $colOffset = 0
   $c.Cells.Item($i,1).value2 = $computer
   Get-WMIObject Win32_LogicalDisk -filter “DriveType=3″ -computer $computer |
   Select `
   DeviceID, `
   VolumeName, `
   @{Name=”size(GB)”; Expression={“{0:N1}” -f($_.size/1GB)}}, `
   @{Name=”freespace(GB)”;Expression={“{0:N1}” -f($_.freespace/1GB)}} | `
   foreach {
   $c.Cells.Item($i,2+$colOffset).value2=”{0}” -f $_.DeviceID
   $c.Cells.Item($i,3+$colOffset).value2=”{0}” -f $_.VolumeName
   $c.Cells.Item($i,4+$colOffset).value2=”{0}” -f $_.”size(GB)”
   $c.Cells.Item($i,5+$colOffset).value2=”{0}” -f $_.”freespace(GB)”

   $colOffset += 4
   }

   $i++
   }

   #resize the columns to fit the data
   $d.EntireColumn.AutoFit() |out-null
5. 

   Fred says:

   January 6, 2011 at 1:06 am

   Is there a way to get the mac address of the network adapters and the serial numbers from the monitors?

   Thanks.
6. 

   xb90 says:

   January 6, 2011 at 10:43 am

   MAC Address is a property of win32_NetworkAdapter so it can be captured with :

   gwmi win32_NetworkAdapter

   I do not get a serial number for any of my monitors but I think the class to look at would be win32_DesktopMontor

   gwmi win32_DesktopMonitor

   Again, I don’t show a SN on my hosts, but you might give it a try anyway
7. 

   Mark says:

   July 18, 2011 at 1:24 am

   Hey guys,

   im trying to create a programme that will find all the computers on the network, i used (net view) and tried reading in from a text file, but it only does the computer I am on, heres the code.

   If somebody could help it’d be greatly appreciated.

   $File = “C:\Scripts\ComDomain.txt”

   Get-Content $File | foreach-object
   {

   $objExcel = New-Object -ComObject Excel.Application
   $objExcel.Visible = $True
   $wb = $objExcel.workbooks.add()

   $Computer = Get-WmiObject -Class Win32_ComputerSystem

   $item = $wb.worksheets.Item(1)

   $item.Cells.Item(1,1) = “ComputerName”
   $item.Cells.Item(1,2) = “FileName”
   $item.Cells.Item(1,3) = “Location”
   $item.Cells.Item(1,4) = “Size”
   $item.cells.Item(1,5) = “OtherComputer”

   $items = Get-ChildItem -path C:\ -recurse * -include “*.bmp” ,”*.xls”, “.mp3″ ;

   $i = 2

   foreach($p in $items)
   {
   $item.cells.Item(2,1) = $Computer.Name
   $item.cells.Item($i,2) = $p.Name
   $item.cells.Item($i,3) = $p.DirectoryName
   $item.cells.Item($i,4) = $p.size / 0.5kb
   $i++
   }

   $wb.SaveAs(“c:\scripts\week2test.xlsx”)

   $objExcel.Quit()

   [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
   [System.Runtime.Interopservices.Marshal]::ReleaseComObject($item) | Out-Null
   [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel) | Out-Null
   [System.GC]::Collect()
   [System.GC]::WaitForPendingFinalizers()

   Remove-Variable objExcel
   Remove-Variable wb
   Remove-Variable item

   }