Creating an Excel File with Computer Information gained from PowerShell

Hello All,

This blog is going to talk about how to make a Windows PowerShell (PS) script that gathers information about a computer's hardware and software then pipes it into an Excel workbook. It seems complicated at first, but it is actually quite simple.


Some Prerequisites for Scripting with Windows PowerShell:

1. Comments are made by using a '#' before the line
2. Variables are assigned using a '$'
3. As in all programming, comments are important so others know what you're doing

With PS, scripts are very simple and only have two main parts: the Cmdlets, which get or change information and takes up nearly nothing of the script, and the formatting, which takes up most of the script just to make it look nice...

To start we need to tell PS that everything in this file goes together, otherwise it will run each thing separately and we'd have to pipe the last line of gathered information into the next. This would just create a major hassle. To do this we create a function:

                             Function WMILookup{}

Now, everything else goes in between the brackets. To make things easier we are going to stick to only doing this on a local machine. There are plenty of other resources for how to expand out over a network with PowerShell. So next, we want to apply variables to the commands PS will be running. This makes it shorter than having to type every command every time since there are twelve of them. 

$GenItems1 = gwmi Win32_ComputerSystem -Comp $StrComputer
$GenItems2 = gwmi Win32_OperatingSystem -Comp $StrComputer
$SysItems1 = gwmi Win32_BIOS -Comp $StrComputer
$SysItems2 = gwmi Win32_TimeZone -Comp $StrComputer
$SysItems3 = gwmi Win32_WmiSetting -Comp $StrComputer
$ProcItems1 = gwmi Win32_Processor -Comp $StrComputer
$MemItems1 = gwmi Win32_PhysicalMemory -Comp $StrComputer
$memItems2 = gwmi Win32_PhysicalMemoryArray -Comp $StrComputer
$DiskItems = gwmi Win32_LogicalDisk -Comp $StrComputer
$ProgItems = gwmi Win32_Product -Comp $StrComputer
$LogItems = Get-EventLog system -AsBaseObject -Newest 100
$NetItems = gwmi Win32_NetworkAdapterConfiguration -Comp $StrComputer | where{$_.IPEnabled -eq "True"}


Each one of these commands use the Alias for Get-WMIObject. Because PowerShell is an Object-Oriented script environment, this tells PS what type of object / class of objects the items we're looking for come from. The Win32_xxxx is that object and the -Comp stands for COMPuter. Since this is only for a local machine we actually don't need to tell the script which  computer it should use. 
         Take Note: The last two functions ($LogItems, $NetItems) have additional parameters added. This is to add different constraints so the script doesn't time out or take too long to finish. 

===========================Formatting===========================

I told you the commands were short... Now for formatting:

This next part looks the same for all the entries, just different words for the XXXX, because of this I will only show the entry for one part.  To find the properties, or attributes, of the commands you want run this:

gwmi Win32_XXXX | Format-Table * -AUTO

This will give you a list of information, from which you can choose what to include in the script or not.

           XXXX is filled in with the above items (ComputerSystem, OperatingSystem, ect.) Format-Table * tells PS to gather all the information about that command (i.e. it list the properties of the command) any of those things listed can be used in the next section. The -AUTO part just auto fits the table so it doesn't take up so much space. 

So from that list I choose these attributes to include in the Excel file for the Win32_ComputerSystem: 

DomainRole, Manufacturer, Model, SystemType, NumberofProcessors, TotalPhyscialMemory (This is divided by 1024 twice to put it into gigabyte format), Caption, csdversion. 

The 'foreach' part is just naming an array to tell the script to look at each record it comes across. This is helpful for the other pages that will be created, as in 'logs'.  $ObjItem is a variable created so PS has something to store the data in. The $Sheet1.Cells.Item($IntRow, x) is a function of the Excel program to tell what page and what cell to store the data. $intRow should change for each new class of items (Physical Memory, CPU, Networking, etc.). Repeat this type of process for each item class until you have all the information needed for the report. At the end of this section counters need to be put in place for the records to be recorded on the next line. That looks like this: 

Now it's time to move on to create the Excel file. This is, again, repetitive: 

#New Excel Application

$Excel = New-Object -Com Excel.Application

$Excel.visible = $True

# Create 8 worksheets

$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.Worksheets.Add()

$Sheet = $Excel.Worksheets.Add()

$Sheet = $Excel.Worksheets.Add()

$Sheet = $Excel.Worksheets.Add()

$Sheet = $Excel.Worksheets.Add()

# Assign each worksheet to a variable and

# name the worksheet.

$Sheet1 = $Excel.Worksheets.Item(1)

$Sheet2 = $Excel.WorkSheets.Item(2)

$Sheet3 = $Excel.WorkSheets.Item(3)

$Sheet4 = $Excel.WorkSheets.Item(4)

$Sheet5 = $Excel.WorkSheets.Item(5)

$Sheet6 = $Excel.WorkSheets.Item(6)

$Sheet7 = $Excel.WorkSheets.Item(7)

$Sheet8 = $Excel.WorkSheets.Item(8)

$Sheet1.Name = "General"

$Sheet2.Name = "System"

$Sheet3.Name = "Processor"

$Sheet4.Name = "Memory"

$Sheet5.Name = "Disk"

$Sheet6.Name = "Network"

$Sheet7.Name = "Programs"

$Sheet8.Name = "Log"

#Create Heading for General Sheet

$Sheet1.Cells.Item(1,1) = "Device_Name"

$Sheet1.Cells.Item(1,2) = "Role"

$Sheet1.Cells.Item(1,3) = "HW_Make"

$Sheet1.Cells.Item(1,4) = "HW_Model"

$Sheet1.Cells.Item(1,5) = "HW_Type"

$Sheet1.Cells.Item(1,6) = "CPU_Count"

$Sheet1.Cells.Item(1,7) = "Memory_MB"

$Sheet1.Cells.Item(1,8) = "Operating_System"

$Sheet1.Cells.Item(1,9) = "SP_Level"

Now to Explain: 

The first three lines are meant for opening the application. $Excel = New-Object -Com Excel.Application is used so not as much typing is needed. Otherwise, $Excel.Worksheets.Item(1) would be New-Object -Com Excel.Application.Worksheets.Item(1). The next section is used to create a Workbook and additional Worksheets. A Workbook has three pages already and a Worksheet adds one additional page. The next little bit assigns names to the Worksheet pages. Then it moves into assigning the titles for each column. This part is done for each page and the labels are dependent on what attributes were selected in the previous sections of the script.  

To Finish Up:

$colSheets = ($Sheet1, $Sheet2, $Sheet3, $Sheet4, $Sheet5, $Sheet6, $Sheet7, $Sheet8)

foreach ($colorItem in $colSheets){

$intRow = 2

$intRowCPU = 2

$intRowMem = 2

$intRowDisk = 2

$intRowNet = 2

$intRowProg = 2

$intRowLog = 2

$WorkBook = $colorItem.UsedRange

$WorkBook.Interior.ColorIndex = 20

$WorkBook.Font.ColorIndex = 11

$WorkBook.Font.Bold = $True

}

#Auto Fit all sheets in the Workbook

foreach ($colorItem in $colSheets){

$WorkBook = $colorItem.UsedRange

$WorkBook.EntireColumn.AutoFit()

clear

}

Write-Host "*******************************" -ForegroundColor Green

Write-Host "The Report has been completed."  -ForeGroundColor Green

Write-Host "*******************************" -ForegroundColor Green

# =================================================================

# END of Script

# ===============================================================

The $colSheets section is used to point all of the pages to row 2 for adding the information into the sheets. This is important so the titles you just types aren't over written. The $.UsedRange, $.ColorIndex, and $.Font.Bold just makes sure the colors are black and applies bold to the cells. 

The If() statement can be ignored since this is only for a local computer. 

The AutoFit section is very helpful though. this takes all of the pages within the workbook, selects everything, and performs an AutoFit. Clear is an alias for Clear-Host which just erases everything shown in PS. Then the Write-Host is used as an indicator that it is finally complete. 

That's it! You have just created a script to write information about your computer to an Excel file. Please note however, this script can take up to fifteen minutes to complete. If this is too slow try exporting the information to a CSV file.