Note!   This article won Technet GURU silver medal. You are welcome to update and give your valuable feedback

Abstract

How to list the software installed on the remote server and export the data into various formats such as CSV, EXCEL, GridView or console. Using SCCM, we do have a way to pull the software inventory by querying SCCM objects but think of an environment where they don't have SCCM. Do we have any other way to do this? How to audit installed software's using PowerShell. How do we get only the selected list of installed software? Do we have an exclude list to take out an unwanted software component out of the list?


Solution

Yes, the power of PowerShell stems a robust way to pull the data and export the data into a CSV file, Grid output, console which ease out systems administration. There are other ways to pull the required information using Win32_product.  Many experts say the use of win32_product is not an efficient way to pull the required details. We can also try Win32Reg_AddRemovePrograms however this class is only available after installing System Center 2012. By keeping all this in mind, thought of using a registry HIVE. 

Details

The task of determining applications (32/64 bit) is installed on a system using PowerShell made a lot easier by querying the Registry HIVE.
This Powershell script list all the installed applications (32/64), particularly useful when we try to audit the list of installed software also helpful in license validation.  The code also contains an exclusion array where you can exclude list of program that you don't want to list in the output. The function gets an input parameter -ComputerName to connect to any system and Application architecture detection (32-bit or 64-bit) is done using Win32_processor WMI class.
The output is a PowerShell console, GridView, CSV files, consolidated CSV files into EXCEL.

Examples

Example 1: OutputType CSV and Output path E:\Output

PS P:\> Get-InstalledApplication -Computername hqdbsp18 -OutputType CSV -outpath E:\OutPut

Example 2: OutputType GridView

PS P:\>Get-InstalledApplication -Computername hqdbsp18 -OutputType GridView

Example 3: OutputType Console

PS P:\>Get-InstalledApplication -Computername hqdbsp18 -OutputType Console

Example 4: Invalid OutputType

PS P:\> Get-InstalledApplication -Computername hqdbsp18 -OutputType Grid

Example 5: Loop through each system to pull the software list

foreach($srv in Get-Content E:\Server.txt)
{
Get-InstalledApplication -Computername $srv -OutputType CSV -outpath E:\OutPut
}

Convert CSV/s to Excel 

Get-ChildItem e:\Output\hqdbsp*.csv | ConvertCSV-ToExcel -output 'SoftwareList.xlsx'

Code

Function Get-InstalledApplication
{
Param(
[Parameter(Mandatory=$true)]
[string[]]$Computername,
[String[]]$OutputType,
[string[]]$outpath
)
 
#Registry Hives
 
$Object =@()
 
$excludeArray = ("Security Update for Windows",
"Update for Windows",
"Update for Microsoft .NET",
"Security Update for Microsoft",
"Hotfix for Windows",
"Hotfix for Microsoft .NET Framework",
"Hotfix for Microsoft Visual Studio 2007 Tools",
"Microsoft Visual C++ 2010",
"cwbin64a",
"Hotfix")
 
[long]$HIVE_HKROOT = 2147483648
[long]$HIVE_HKCU = 2147483649
[long]$HIVE_HKLM = 2147483650
[long]$HIVE_HKU = 2147483651
[long]$HIVE_HKCC = 2147483653
[long]$HIVE_HKDD = 2147483654
 
Foreach($EachServer in $Computername){
$Query = Get-WmiObject -ComputerName $Computername -query "Select AddressWidth, DataWidth,Architecture from Win32_Processor" 
foreach ($i in $Query)
{
 If($i.AddressWidth -eq 64){            
 $OSArch='64-bit'
 }            
Else{            
$OSArch='32-bit'            
}
}
 
Switch ($OSArch)
{
 
 "64-bit"{
$RegProv = GWMI -Namespace "root\Default" -list -computername $EachServer| where{$_.Name -eq "StdRegProv"}
$Hive = $HIVE_HKLM
$RegKey_64BitApps_64BitOS = "Software\Microsoft\Windows\CurrentVersion\Uninstall"
$RegKey_32BitApps_64BitOS = "Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall"
$RegKey_32BitApps_32BitOS = "Software\Microsoft\Windows\CurrentVersion\Uninstall"
 
#############################################################################
 
# Get SubKey names
 
$SubKeys = $RegProv.EnumKey($HIVE, $RegKey_64BitApps_64BitOS)
 
# Make Sure No Error when Reading Registry
 
if ($SubKeys.ReturnValue -eq 0)
{  # Loop through all returned subkeys
ForEach ($Name in $SubKeys.sNames)
 {
$SubKey = "$RegKey_64BitApps_64BitOS\$Name"
$ValueName = "DisplayName"
$ValuesReturned = $RegProv.GetStringValue($Hive, $SubKey, $ValueName)
$AppName = $ValuesReturned.sValue
$Version = ($RegProv.GetStringValue($Hive, $SubKey, "DisplayVersion")).sValue 
$Publisher = ($RegProv.GetStringValue($Hive, $SubKey, "Publisher")).sValue 
$donotwrite = $false
 
if($AppName.length -gt "0"){
 
 Foreach($exclude in $excludeArray) 
                        {
                        if($AppName.StartsWith($exclude) -eq $TRUE)
                            {
                            $donotwrite = $true
                            break
                            }
                        }
            if ($donotwrite -eq $false) 
                        {                        
            $Object += New-Object PSObject -Property @{
            Application = $AppName;
            Architecture  = "64-BIT";
            ServerName = $EachServer;
            Version = $Version;
            Publisher= $Publisher;
           }
                        }
 
}
 
  }}
 
#############################################################################
 
$SubKeys = $RegProv.EnumKey($HIVE, $RegKey_32BitApps_64BitOS)
 
# Make Sure No Error when Reading Registry
 
if ($SubKeys.ReturnValue -eq 0)
 
{
 
  # Loop Through All Returned SubKEys
 
  ForEach ($Name in $SubKeys.sNames)
 
  {
 
    $SubKey = "$RegKey_32BitApps_64BitOS\$Name"
 
$ValueName = "DisplayName"
$ValuesReturned = $RegProv.GetStringValue($Hive, $SubKey, $ValueName)
$AppName = $ValuesReturned.sValue
$Version = ($RegProv.GetStringValue($Hive, $SubKey, "DisplayVersion")).sValue 
$Publisher = ($RegProv.GetStringValue($Hive, $SubKey, "Publisher")).sValue 
 $donotwrite = $false
 
if($AppName.length -gt "0"){
 Foreach($exclude in $excludeArray) 
                        {
                        if($AppName.StartsWith($exclude) -eq $TRUE)
                            {
                            $donotwrite = $true
                            break
                            }
                        }
            if ($donotwrite -eq $false) 
                        {                        
            $Object += New-Object PSObject -Property @{
            Application = $AppName;
            Architecture  = "32-BIT";
            ServerName = $EachServer;
            Version = $Version;
            Publisher= $Publisher;
           }
                        }
           }
 
    }
 
}
 
} #End of 64 Bit
 
######################################################################################
 
###########################################################################################
 
"32-bit"{
 
$RegProv = GWMI -Namespace "root\Default" -list -computername $EachServer| where{$_.Name -eq "StdRegProv"}
 
$Hive = $HIVE_HKLM
 
$RegKey_32BitApps_32BitOS = "Software\Microsoft\Windows\CurrentVersion\Uninstall"
 
#############################################################################
 
# Get SubKey names
 
$SubKeys = $RegProv.EnumKey($HIVE, $RegKey_32BitApps_32BitOS)
 
# Make Sure No Error when Reading Registry
 
if ($SubKeys.ReturnValue -eq 0)
 
{  # Loop Through All Returned SubKEys
 
  ForEach ($Name in $SubKeys.sNames)
 
  {
$SubKey = "$RegKey_32BitApps_32BitOS\$Name"
$ValueName = "DisplayName"
$ValuesReturned = $RegProv.GetStringValue($Hive, $SubKey, $ValueName)
$AppName = $ValuesReturned.sValue
$Version = ($RegProv.GetStringValue($Hive, $SubKey, "DisplayVersion")).sValue 
$Publisher = ($RegProv.GetStringValue($Hive, $SubKey, "Publisher")).sValue 
 
if($AppName.length -gt "0"){
 
$Object += New-Object PSObject -Property @{
            Application = $AppName;
            Architecture  = "32-BIT";
            ServerName = $EachServer;
            Version = $Version;
            Publisher= $Publisher;
           }
           }
 
  }}
 
}#End of 32 bit
 
} # End of Switch
 
}
 
#$AppsReport
 
$column1 = @{expression="ServerName"; width=15; label="Name"; alignment="left"}
$column2 = @{expression="Architecture"; width=10; label="32/64 Bit"; alignment="left"}
$column3 = @{expression="Application"; width=80; label="Application"; alignment="left"}
$column4 = @{expression="Version"; width=15; label="Version"; alignment="left"}
$column5 = @{expression="Publisher"; width=30; label="Publisher"; alignment="left"}
 
if ($outputType -eq "Console")
{
"#"*80
"Installed Software Application Report"
"Number of Installed Application count : $($object.count)"
"Generated $(get-date)"
"Generated from $(gc env:computername)"
"#"*80
$object |Format-Table $column1, $column2, $column3 ,$column4, $column5
}
 
elseif ($OutputType -eq "GridView")
{
$object|Out-GridView 
}
elseif ($OutputType -eq "CSV")
{
[string]$FileDS = Get-Date -Format "yyyyMMdd"
[string]$outFile = $outpath+'\'+$computername+'_'+$FileDS+'.csv'
New-Item -ItemType file $outfile -Force
$object | export-csv -path $outfile
}
else
{
write-host " Invalid Output Type $OutputType"
}
 
 
}

Conclusion

  • The code can be used to exclude the application list. You can list only the needed installed components
  • The code pulls the installed software into various output forms
  • Tested for both 32 and 64-bit servers
  • The CSV/s file consolidation to EXCEL is done via ConvertCSV-ToExcel Script

References

  • ConvertCSV-ToExcel - https://gallery.technet.microsoft.com/scriptcenter/7c56c444-2476-4625-b1d9-821f30280e44/
  • https://sqlpowershell.wordpress.com/2014/01/20/powershell-program-list-3264-bit-localremote-machies/