Programs without MSI Product ID

Ryan Coding/Scripting, General, Reporting Services, SCCM SMS, SQL, Sys Admin, Windows

Recently I had an issue where ActiveSetup wasn’t installing some applications when deployed. It turned out that it was due to some of the packaged applications not having a program with the Windows installer information populated. As it was believed that this may not an isolated issues I was tasked with producing a report of all Packages where the information was missing. This is not actually possible via the default reports as none of the views exposed to SCCM actually contain this information. The only way to get this information is to query the SCCM database table “PkgPrograms”, and to do that and have a permenant report SSRS is needed as it can run with the credentials of the user generating it or with an execution account which may have more access.

The report uses the following SQL statement:

SELECT
tpr.PkgID,
vpa.Manufacturer,
vpa.Name,
vpa.Version
FROM
PkgPrograms tpr
INNER JOIN v_Package vpa ON tpr.PkgID = vpa.PackageID
WHERE
vpa.PackageType = '0' -- Application Packages
AND
tpr.Command like '%msi%'
AND
tpr.PkgID NOT IN (
 SELECT PkgID
 FROM PkgPrograms
 WHERE
 MSIProductID <> ''
)
GROUP BY tpr.PkgID,vpa.Manufacturer,vpa.Name,vpa.Version
ORDER BY Manufacturer, Name

PackageTypes

  • 0  – Application Packages
  • 3 – Drivers
  • 4 – Task Sequences
  • 5 – Software Updates
  • 257 – Operating System Images
  • 258 – WinPE Boot Images
  • 259 – Operating System Install Packages
Command like ‘%msi%’ is required as the command string might be “msiexec.exe /i …” or “msiexec /i …” or “blah.exe msiexec /i” So we want to ensure that we catch any commandline that has anything to do with MSIs. It is likely we will get a few false positives but better to have that than miss out on packages that will need updating.

Report:

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA

*

This blog is kept spam free by WP-SpamFree.