Creating excel documents with powershell

Recently I began my adventure with excel and COM objects via powershell. This adventure as every adventure began as a really bumpy ride. First I had to find out how to create an excel document the powershell way. The background for this is provided to me by PowerCLI, I have to create all kinds of reports to the Managers in a readable old way fashion like excel… I don’t really understand this approach because a nice HTML with unique CSS template can be very-very delighting. So in a nutshell I have some virtual machines which are running on  ESXi hosts and I want to add their current location to an XLSX file.

First I create my COM object and initialize it.

$excel = New-Object -ComObject excel.application
$excel.visible = $false
$workbook = $excel.Workbooks.Add()
$workbook.Worksheets.Add()
$machines= $workbook.Worksheets.Item(1)
$machines.Name = ‘VirtualMachines’

Then all I have to do is add the actual data from the machines. All I do is cycle through the virtual machines with a foreach loop like this then add the data.

foreach($VM in $VirtualMachines)
{
$machines.Cells.Item($ROW,$Column) = $VM.Name
$machines.Cells.Item($ROW,$Column+1) = $PowerState
$machines.Cells.Item($ROW,$Column+2) = $VM.NumCpu
$machines.Cells.Item($ROW,$Column+3) = $(($VM.MemoryMB -as [string]) + ” MB”)
$machines.Cells.Item($ROW,$Column+4) = $($VM.Host.Name)
$ROW++
$i++
}

Actually this is quite what I want, if I change the $excel.visible = $true I can see as the excel being filled up right before my eyes. But I was hoping for something faster, and more dynamic. When I have 100 machines to add to the excel and some formatting it becomes very-very slow. To be honest it’s quite faster than manually creating the excel, but you know how it goes. Here is a way to add some formating and eyecandy to the report.

$machines.Cells.Item($ROW,$Column+1).ColumnWidth = 13
$machines.Cells.Item($ROW,$Column+1).HorizontalAlignment = -4108
$machines.Cells.Item($ROW,$Column+1).Font.Bold=$False
$machines.Cells.Item($ROW,$Column+1).Font.ColorIndex= 1
$machines.Cells.Item($ROW,$Column+1).Interior.ColorIndex = 15
$machines.Cells.Item($ROW,$Column+1).Borders.LineStyle = 1

This will align the width, font, color of the cell, to the disadvantage of the speed. It’s bugged me so much I began looking for another ways to add data to the excel. Here is what I found… I don’t know if it is happening as I belive it is happening but It’s how I percive it.

$Colnames = New-Object ‘object[,]’1,5
$Colnames[0,0] = $VM.Name
$Colnames[0,1] = $PowerState
$Colnames[0,2] = $VM.NumCpu
$Colnames[0,3] = $(($VM.MemoryMB -as [string]) + ” MB”)
$Colnames[0,4] = $($VM.Host.Name)
$range = $machines.range(“A$($row):E$($row)”)
$range.Value2 = $Colnames

The just decribed way of adding values to the excel shows more impressive speed then the one before. My conclusion is that adding a value to a cell counts as 1 operation. The same as adding a 2-D array’s values to a range of cells. So this is where the speed difference my reside. I don’t think it is always more efficient than the other but even if the 2D array consists of 1 row and 1 column we can never go below the speed of the older way.

This post is dedicated to Don Jones, Ed Wilson, Dr. Tobias Weltner they are amazing people, they are the reason I started my journey with powershell, they made me love it. 🙂

Cheers,

Daniel

 

Using WMIC to discover OS information

WMIC has made the life of many-many sysadmins easier since the Windows 2000. This tool allows anyone with proper rights to access the WMI namespace, and repository of a local or remote computer. Which means we can easily access the information we need for reporting purposes or even configuration. Let’s say we want the BuildNumber and the Caption of the operating systems we are currently working on or a remote system.

Why bother logging in, and creating screenshots and sending them to the persons requesting it when we can inquire the data we want from a single command line tool.

wmic os get caption,version

Thre return value of the command on my local machine is as follows.

Caption                                Version
Microsoft Windows 8.1 Pro  6.3.9600

In case we have connection and access to a remote machine we can go viral with this tool.

wmic /node:Reaper os get Caption,Version

We only have to specify the Name or the IP address after the /node: switch and we shall see the values returned. Naturally nowadays on a modern OS like Windows 7 or Windows Server 2008 and above we would use the following command to retrive the information.

Get-WmiObject -Class Win32_OperatingSystem  | Select-Object -Property Caption,Version

Caption                                                                 Version
——-                                                                     ——-
Microsoft Windows 8.1 Pro                                   6.3.9600

If we are of those few lucky ones with Windows 8 and Windows Server 2012 or above we can leverage the CIM objects aswell.

Get-CimInstance -ClassName Win32_OperatingSystem | Select-Object -Property Caption,Version

Caption                                                                Version
——-                                                                     ——-
Microsoft Windows 8.1 Pro                                   6.3.9600

With the “-ComputerName <Name of the server>” both WMI and CIM can go viral, and due to the fact that everything returned is an Object nothing is there to stop us from being productive.

Cheers, Daniel

 

Working with dates coming from different culture in Powershell

This post is about [datetime] object. I recently faced a problem, when I had to use PowerCLI to retrive the current Uptime of the reachable virtual machines. The problem came when I tried to calculate the value by extracting the retrived date from the actual date, by which I could refer to the Days, Hours, Minutes properties of the newborn object. I had different values for the LastBootUpTime and I didn’t understand why the [datetime]::Parse() function fails with FormatInvalid exception. Then I had an idea. I have stored all the available cuture information from my powershell to a variable.

$AllCultures =  [globalization.cultureinfo]::GetCultures(‘AllCultures’)

By this command I had a good chance to find a culture pattern which could match my retrived date.

$FinalCulture = [Globalization.cultureinfo]::GetCultureInfo(“hu-HU”)

I have stored the destination culture in another variable so finally I could transform the date to the necessary pattern.

foreach($Culture in $AllCultures)
     {
     $newdate = $null
     $canstop = $false
     try{ $newdate = [datetime]::Parse($DateToConvert,$Culture)
     if($newdate -ne $null){$canstop = $true}}
     catch{}
     if($canstop){Write-Host $newdate;Write-Host $Culture;break}
     }

This funtion starts matching the $DateToConvert variable’s contents agains the $Culture information which comes from the actual culture of the $AllCultures variable. The try{}catch{} block is to catch the errros which came from the invalid format, and this is how the funciton works. The $newdate get’s the value from the parsing, the $canstop variable is a logical variable which shows us when the pattern was successfully matched against one of the cultures without any errors. We don’t care which culture was successfully matched, we only want to convert the $DateToConvert string to a [datetime] object. After this is done, we can convert the object to our preferred culture, which is this time “hu-HU” then return this value for further usage.

return ([datetime]::Parse($newdate,$FinalCulture))

After this is done we can calculate the timespan, and get the Days, Hours, Minutes spent since that freaking last reboot.
That’s all for now.

Cheers,Daniel

 

Finding loaded assemblies in a powershell console

This post is about prechecking. Let’s say you have an application and it relies on specific assemblies in the powershell console. How do you know? How can you check if you have to load them or if they are already loaded?

The answer is: [AppDomain]::CurrentDomain.GetAssemblies()

This small command will list all the currently loaded assemblies which concern your shell!

GAC Version Location

— ——- ——–
True v4.0.30319 C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.dll
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.PowerShell.ConsoleHost\v4.0_3.0.0.
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\S
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Management.Automation\v4.0_3.0.0.0__3
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Numerics\v4.0_4.0.0.0__b77a5c561934e0
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\Sy
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.DirectoryServices\v4.0_4.0.0.0__b03f5
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\System.Management\v4.0_4.0.0.0__b03f5f7f11d5
True v4.0.30319 C:\windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.Management.Infrastructure\v4.0_1.0

Cheers, Daniel