Quickly List SCCM Packages using SQL Query | PowerShell

List SCCM Packages using SQL Query

In SCCM, you can find all the packages and its details using a SQL query and PowerShell. Let’s see how to quickly list SCCM Packages using SQL Query and PowerShell.

Packages in Configuration Manager were introduced in version 2007. The packages are intended for device deployments while applications are intended for user deployments. Although, you can still use packages for user deployments and applications for device deployments.

Configuration Manager current branch still continues to support packages and programs that were used in Configuration Manager 2007.

Install and Update Third Party Applications with Patch My PC
Install and Update Third Party Applications with Patch My PC

Packages can use some new features of Configuration Manager, including distribution point groups and monitoring. You can’t deploy Microsoft Application Virtualization (App-V) applications with packages and programs in Configuration Manager.

Packages are still a part of ConfigMgr, and they are used widely. One of the reasons why applications are preferred over packages is there is no support for detection methods in packages.

In your SCCM environment, you may have several packages created. If you’re asked to find all the packages present in SCCM, you can either use a SQL query or PowerShell command.

With a simple SQL query, you can find all the packages and associated details. Other ways to list all the packages is use the packages section of the SCCM console or use the PowerShell command.

Default Packages in SCCM

When you install SCCM, there are 3 default packages created. All the default packages are located in Software Library\Overview\Application Management\Packages folder of ConfigMgr console.

  • Configuration Manager Client Package
  • Configuration Manager Client Piloting Package
  • User State Migration Tool for Windows
Default Packages in SCCM
Default Packages in SCCM

Note: The Configuration Manager Client Upgrade package is used to keep clients up-to-date on the latest version. This package is automatically created and distributed so that the client can upgrade itself from the closest distribution point.

Except the SCCM client package, you can delete the other packages. Do not delete them unless you absolutely don’t need it.

The SCCM SQL queries are very useful in getting information from the ConfigMgr database. Queries can return most types of ConfigMgr objects, which include sites, collections, applications, and inventory data. For example, you can perform the following tasks with SQL queries.

List SCCM Packages using SQL Query

Perform the following steps to list SCCM Packages using SQL Query:

  • Launch the SQL Server Management studio (SSMS) and connect to database engine.
  • Expand Databases and right click SCCM database and select New Query.
  • In the query window, paste the below SQL query to list all the packages and click Execute.
SELECT
Program.PackageID,
Package.Name 'Package',
Program.ProgramName 'Type',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM
[v_Program] as Program
LEFT JOIN
v_Package as Package on Package.PackageID = Program.PackageID
WHERE Program.ProgramName != '*'
--WHERE Program.ProgramName = 'Install'
ORDER BY
Package.Name
List SCCM Packages using SQL Query
List SCCM Packages using SQL Query

The above SQL query will list all the packages present in SCCM. In addition, the following Package details are included in the results.

  • Package ID
  • Package Name
  • Type of the Package
  • Command Line
  • Comment
  • Package Description
  • Package Source Path

Read: How to use Package Conversion Manager in SCCM

Get SCCM Packages using PowerShell Command Get-CMPackage

To get all the packages in SCCM, you can use the PowerShell command Get-CMPackage. Running this command will result in lengthier output. Hence, you should either use the Get-CMPackage command with PackageID or PackageName.

  • Get-CMPackage -Id “PackageID”
  • Get-CMPackage -Id “PackageName”

Every package that you create in SCCM has a unique PackageID and PackageName. Use these parameters with Get-CMPackage command to get SCCM packages using PowerShell.

Note: You must run Configuration Manager cmdlets from the Configuration Manager site drive.

Let me show you some examples to list SCCM packages using PowerShell. First, let’s use the below PowerShell command to get a SCCM package by using a name. The below command will list the Configuration Manager Client Package details.

Get-CMPackage -Name "Configuration Manager Client Package"
Get SCCM Packages using Get-CMPackage
Get SCCM Packages using Get-CMPackage

You can also list SCCM package details using the PackageID. The below PowerShell command lists the package details by using the PackageID.

Get-CMPackage -Id "MEM00007"
Get SCCM Packages using Get-CMPackage
Get SCCM Packages using Get-CMPackage

Conclusion

I hope you have learned how to list SCCM packages using SQL query and PowerShell. You can use any of these methods to get the SCCM packages in your setup. If you have any suggestions or questions, let me know them in the comments section.

Need more help?

If you need further assistance on the above article or want to discuss other technical issues, check out some of these options.