It is tricky to know all the software installed in a company. There are ample software companies that specialize in Software Inventory and categorization. I just wanted to share a quick SQL script I wrote that exactly matches what is in the Program and Features on a computer. This script queries the Configuration Manager database and uses Recast’s reporting tool. Garth Jones, @GarthMJ, at Recast, wrote an executable with his reporting software that gathers information on the computer and user accounts that Configuration Manager does not generally capture, such as user-installed software. Using his SQL views, I was able to create the script.
You should purchase the Recast reporting tool; however, for companies that do not have it, I wrote an old-style hardware inventory script that creates a new WMI class for Configuration Manager. The WMI class needs to be added to Hardware Inventory. I will post the script first. The script only works for the currently logged-in user. Once you deploy the script, I recommend creating a scheduled task to run it.
$ProfileList=Get-WmiObject -Class win32_UserProfile|Where-Object {$_.Special -eq $False}
$Sids = $ProfileList.SID
$InstalledSoftware = New-Object System.Collections.ArrayList
$PCName = (Get-WmiObject Win32_ComputerSystem).Name
#Create WMI Class
$ClassName = "CG_Add_Remove_Programs_User"
function New-WmiClass()
{
$newClass = New-Object System.Management.ManagementClass("root\cimv2", [String]::Empty, $null);
$newClass["__CLASS"] = $ClassName ;
$newClass.Qualifiers.Add("Static", $true)
$newClass.Properties.Add("DeviceName", [System.Management.CimType]::String, $false)
$newClass.Properties["DeviceName"].Qualifiers.Add("key", $true)
$newClass.Properties["DeviceName"].Qualifiers.Add("read", $true)
$newClass.Properties.Add("UserID", [System.Management.CimType]::String, $false)
$newClass.Properties["UserID"].Qualifiers.Add("key", $true)
$newClass.Properties["UserID"].Qualifiers.Add("read", $true)
$newClass.Properties.Add("DisplayName", [System.Management.CimType]::String, $false)
$newClass.Properties["DisplayName"].Qualifiers.Add("key", $true)
$newClass.Properties["DisplayName"].Qualifiers.Add("read", $true)
$newClass.Properties.Add("DisplayVersion", [System.Management.CimType]::String, $false)
$newClass.Properties["DisplayVersion"].Qualifiers.Add("read", $true)
$newClass.Properties.Add("Publisher", [System.Management.CimType]::String, $false)
$newClass.Properties["Publisher"].Qualifiers.Add("read", $true)
$newClass.Properties.Add("InstallDate", [System.Management.CimType]::String, $false)
$newClass.Properties["InstallDate"].Qualifiers.Add("read", $true)
$newClass.Put()
}
# Check whether we already created our custom WMI class on this PC, if not, create it
[void](Get-WmiObject $ClassName -ErrorAction SilentlyContinue -ErrorVariable wmiclasserror)
if ($wmiclasserror)
{
try {
New-WmiClass
write-host "Class Created"
}
catch
{
write-host "Could not create WMI class"
write-host $wmiclasserror
Exit 1
}
}
#remove all instances so only current application titles are listed. Any files that were removed will be no longer
#be listed in the class or HWInv
Get-WmiObject -Class $ClassName |Remove-WmiObject
#User Installs
foreach($sid in $Sids)
{
$ValidPath = $False
$ValidPath = test-path "registry::HKEY_USERS\$sid\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall"
If($ValidPath -eq $True)
{
#Find User Name
$UserPath = Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\$Sid" -Name ProfileImagePath
$UserID = ($UserPath.Split("\"))[2]
$Programs = Get-ItemProperty registry::HKEY_USERS\$sid\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\* | Select-Object DisplayName, DisplayVersion, Publisher, InstallDate
ForEach($Program in $Programs)
{
$Arguments=@{DeviceName=$PCName; `
UserID = $UserID; `
displayName=$Program.DisplayName;`
displayVersion=$Program.DisplayVersion;`
Publisher=$Program.Publisher; `
InstallDate=$Program.InstallDate}
$Path = "\\.\ROOT\cimv2:CG_Add_Remove_Programs_User"
Set-WmiInstance -Namespace root\cimv2 -Class $ClassName -Arguments $Arguments
}
}
}
Now I am no Garth or Benjamin Reynolds, @SqlBenjamin, so excuse my query if there is a better way to write it.
USE CM_LAB
select sysv.Netbios_Name0, sw64.DisplayName0 'Name', sw64.Publisher0 'Publisher', sw64.Version0 'Version'
from v_GS_ADD_REMOVE_PROGRAMS_64 sw64
JOIN v_R_System_Valid sysv on sysv.resourceid = sw64.ResourceID
UNION
select sysv.Netbios_Name0, sw.DisplayName0 'Name', sw.Publisher0 'Publisher', sw.Version0 'Version'
from v_GS_ADD_REMOVE_PROGRAMS sw
JOIN v_R_System_Valid sysv on sysv.resourceid = sw.ResourceID
UNION
Select sysv.Netbios_Name0, uarp.DisplayName0 'Name' ,uarp.Publisher0 'Publisher', uarp.DisplayVersion0 'Version'
from v_GS_CG_ADD_REMOVE_PROGRAMS_USER uarp
JOIN v_R_System_Valid sysv on sysv.ResourceID = uarp.ResourceID
UNION
SELECT sysv.Netbios_Name0, APP.ApplicationName0 'Name', APP.Publisher0 'Publisher', APP.Version0 'Version'
FROM v_GS_WINDOWS8_APPLICATION APP
JOIN v_R_System_Valid sysv on sysv.ResourceID = app.ResourceID
Order by sysv.Netbios_Name0, Name0
Here is a sample of the results. It shows programs in Add Remove Programs, everything from the software store, and everything install under the currently logged-in user.
Config Girl 
Leave a comment