Powershell: Export Installed Software to Excel


image

I created the next script to inventory the installed software on my servers. The only thing you need to create is a text file with all your servers in it and save it to Servers.txt.

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel.SheetsInNewWorkbook = @(get-content "C:\Scripts\PS\Servers.txt").count

#Counter variable for rows
$i = 1

#Read thru the contents of the Servers.txt file
foreach ($server in get-content "C:\Scripts\PS\Servers.txt")
{
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item($i++)
    $Sheet.Name = $server

    $intRow = 1

    # Send a ping to verify if the Server is online or not.
    $ping = Get-WmiObject `
    -query "SELECT * FROM Win32_PingStatus WHERE Address = '$server'"
       if ($Ping.StatusCode -eq 0) {

         #Create column headers
         $Sheet.Cells.Item($intRow,1) = "NAME:"
         $Sheet.Cells.Item($intRow,2) = $server.ToUpper()
         $Sheet.Cells.Item($intRow,1).Font.Bold = $True
         $Sheet.Cells.Item($intRow,2).Font.Bold = $True

         $intRow++

         $Sheet.Cells.Item($intRow,1) = "APPLICATION"
         $Sheet.Cells.Item($intRow,2) = "VERSION"

             #Format the column headers
             for ($col = 1; $col –le 2; $col++)
             {
                  $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
                  $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
                  $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
             }

             $intRow++

             $software = Get-WmiObject `
             -ComputerName $server -Class Win32_Product | Sort-Object Name 

             #Formatting using Excel

             foreach ($objItem in $software){
                $Sheet.Cells.Item($intRow, 1) = $objItem.Name
                $Sheet.Cells.Item($intRow, 2) = $objItem.Version

                   $intRow ++
             }

        $Sheet.UsedRange.EntireColumn.AutoFit()
    }
}

Clear

The following Excel file will be created. For each server in Servers.txt, there will be added a worksheet with the name of the server.

image

18 thoughts on “Powershell: Export Installed Software to Excel

  1. Pingback: Powershell: Export Installed Software to Excel « ICT-Freak.nl

  2. Pingback: Powershell: Export Installed Software to Excel « ICT-Freak.nl | Information Technology

  3. Thanks for this script! It works very well with XP boxen.

    When I run it against a Windows 2003 Standard box, I get this error:

    Invalid class
    At :line:44 char:38
    + $software = Get-WmiObject <<<< `

  4. Pingback: Episode 82 – MVP Russ Kaufmann on PowerShell and Clustering « PowerScripting Podcast

  5. Thanks for the script, it gave me a good starting point.

    A few things I ran into and that might help somebody else:

    1. Take the “$Excel = $Excel.Workbooks.Add()” statement out of the foreach loop or you’ll end up with one workbook per computer.

    2. The Win32_Product class is not available on all versions of windows (slow on Vista, not installed on WS2003 according to http://www.microsoft.com/technet/scriptcenter/resources/qanda/apr07/hey0418.mspx). My solution was to test if the class was available:
    if ((get-wmiobject -ComputerName $_ -namespace “root/cimv2” -list) | ? {$_.name -match “Win32_Product”})
    If it is, I use it, if not, I get the list of installed applications by listing the registry keys under HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall.
    This thread helped me to do that: http://powershell.com/cs/forums/p/1822/2537.aspx

    3. If you looking for a powershell audit script that output in html format, I recommend: http://teckinfo.blogspot.com/2008/10/powershell-audit-script_15.html

  6. The Win32_Product class exists on Windows Server 2003, but it is not installed by default. To install it, you’ll need to do the following:

    * In Add or Remove Programs, click Add/Remove Windows Components.
    * In the Windows Components Wizard, select Management and Monitoring Tools and then click Details.
    * In the Management and Monitoring Tools dialog box, select WMI Windows Installer Provider and then click OK.
    * Click Next.

  7. hi,

    Thanks for this script.
    I need to export the installed Microsoft KB numbers alone to the Excel Sheet. Can you please help me how to do that?

  8. This works great but I was actually looking to find out which versions of IE are installed on each machine but I do not see IE listed in the application output Any ideas?

  9. Awesome piece of scripting that gave me a head start, thanks!
    Have modded to take out the error for checking workbook, and added in display version:

    #Create a new Excel object using COM
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $True
    $Excel.SheetsInNewWorkbook = @(get-content “D:\Servers.txt”).count
    $Excel = $Excel.Workbooks.Add()

    #Counter variable for rows
    $i = 1

    #Read thru the contents of the Servers.txt file
    foreach ($server in get-content “D:\Servers.txt”)
    {
    $Sheet = $Excel.Worksheets.Item($i++)
    $Sheet.Name = $server

    $intRow = 1

    # Send a ping to verify if the Server is online or not.
    $ping = Get-WmiObject `
    -query “SELECT * FROM Win32_PingStatus WHERE Address = ‘$server'”
    if ($Ping.StatusCode -eq 0) {

    #Create column headers
    $Sheet.Cells.Item($intRow,1) = “NAME:”
    $Sheet.Cells.Item($intRow,2) = $server.ToUpper()
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,2).Font.Bold = $True

    $intRow++

    $Sheet.Cells.Item($intRow,1) = “APPLICATION”
    $Sheet.Cells.Item($intRow,2) = “PUBLISHER”
    $Sheet.Cells.Item($intRow,3) = “VERSION”

    #Format the column headers
    for ($col = 1; $col –le 3; $col++)
    {
    $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
    $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
    $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
    }

    $intRow++

    # Branch of the Registry
    $Branch=’LocalMachine’
    # Main Sub Branch you need to open
    $SubBranch=”SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall”
    $registry=[microsoft.win32.registrykey]::OpenRemoteBaseKey(‘Localmachine’,$server)
    $registrykey=$registry.OpenSubKey($Subbranch)
    $SubKeys=$registrykey.GetSubKeyNames()
    # Drill through the list of SubKeys and examine the Display name

    Foreach ($key in $subkeys)
    {
    $exactkey=$key
    $NewSubKey=$SubBranch+”\\”+$exactkey
    $ReadUninstall=$registry.OpenSubKey($NewSubKey)
    $Value=$ReadUninstall.GetValue(“DisplayName”)
    $Publisher=$ReadUninstall.GetValue(“Publisher”)
    $Version=$ReadUninstall.GetValue(“DisplayVersion”)

    #Write-Host $Server, $Value
    $Sheet.Cells.Item($intRow, 1) = $Value
    $Sheet.Cells.Item($intRow, 2) = $Publisher
    $Sheet.Cells.Item($intRow, 3) = $Version

    $intRow ++
    }

    $Sheet.UsedRange.EntireColumn.AutoFit()
    }}

  10. Forgot to mention it uses the registry uninstall key, will have an updated version for you that searches x64 as well, plus progress bar, and removal of blank cells.

    Cheers,

    H

  11. anyone else getting this error? any suggestions to fix? im running windows server 2008 R2
    S D:\> .\soft_Inv.ps1
    The string starting:
    At D:\soft_Inv.ps1:37 char:36
    + for ($col = 1; $col â? <<<< "le 2; $col++)
    is missing the terminator: ".
    At D:\soft_Inv.ps1:62 char:6
    + Clear <<< .\soft_Inv.ps1
    The string starting:
    At D:\soft_Inv.ps1:37 char:36
    + for ($col = 1; $col â? <<<< "le 2; $col++)
    is missing the terminator: ".
    At D:\soft_Inv.ps1:62 char:6
    + Clear <<<<
    + CategoryInfo : ParserError: (le 2; $col++)
    … }
    }
    Clear:String) [], ParseException
    + FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

  12. The script is giving me a lot of a blank lines/rows in my excel file. Is there an easy way to get rid of these or to not write these blank lines/rows?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.