SCCM Report – All Packages, Programs and their command lines

Hi all,

I recently had a requirement at a client to make a list of all SCCM Packages. their Programs,  and then the command lines of each, along with the package source.  I wondered if this was possible, and fortunately the SQL Views were already present to do so.

This ended up being a very short query:

SELECT Program.PackageID,
Package.Name 'Package Name',
Program.ProgramName 'Program Name',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM [v_Program] as Program
LEFT JOIN v_Package as Package on Package.PackageID = Program.PackageID</p>
Order by Program.PackageID

And the output  Some of these command lines were VERY long, so it came out a bit wide.:

apps and command lines - 1 apps and command lines - 2

Advertisements

17 thoughts on “SCCM Report – All Packages, Programs and their command lines

  1. Chris August 18, 2014 / 7:58 pm

    Do you happen to know how to modify this report to include the package hash value as well? I was trying to resolve hash value mismatch issues on my DP’s and came across the SQL query at the bottom of this article: http://marcusoh.blogspot.com/2011/04/sccm-content-hash-fails-to-match.html, but it requires the package ID to be entered one at a time and uses the SQL management studio. I would love to have a report viewable from the SCCM console that includes the information in your report and also includes the hash value so I can match it against the values calculated by the hashdir tool.

    • FoxDeploy August 20, 2014 / 10:14 am

      Sure thing Chris. I have my SCCM config mothballed at home and can’t get to it at the moment.

      Can you give me a screen shot from SQL Management Studio of the table which has the column you want (hash value)? If you do, I can help you update the query I provided to give you the info you need.

  2. Mat October 15, 2014 / 12:10 pm

    Thanks for that Query! Really helped me out!

    • FoxDeploy October 15, 2014 / 12:25 pm

      Happy to help. Feel free to shoot me query requests in the future. I think it is really a fun challenge to pull out the perfect info with a query.

  3. ez12a October 28, 2014 / 3:37 am

    Great query! I thought it’d be a good idea to archive this information for future use. Would you be able to make one for Applications and their Deployment Types that include their command line? (I see this seems to only do Packages)

  4. ez12a October 28, 2014 / 3:38 am

    Thanks for the helpful script. Would you be able to do the same for Applications and their Deployment Types (and their ‘Command Line’)? I see this query only returns Packages. That would be awesome!

  5. David McKinnon January 15, 2016 / 12:47 pm

    Hi Steve – is it possible to get the deployment counts for each package followed be a grand total of deployments for all packages? Thanx, Dave

  6. TL February 7, 2017 / 7:03 pm

    Thank you very much for the script. Can you provide guidance with a sql script that shows which PackageID is linked to a CollectionID? Also, one that shows a Packages Programs and there related CollectionIDs?

    Example: We use sccm packages instead of applications. Each Package could have several programs (one for each business location) and each program is associated with its own collection. How can I query all packageid, associated programs and related collections in a single query?

    Thank you very much for your assistance!!

    • FoxDeploy April 12, 2017 / 9:28 am

      Not sure if you figured this out or not, but a Package is only linked to a collection when it’s deployed to a collection. What you’d need to do is add a subquery to gather all of the deployments targetted at a collection, and then join the ResourceID from the collection deployment to the Package. This will link Packages Deployment Collection. Finally, add a Group on Collection.ResourceID or at least an Order by to make sense of it.

  7. Robert Ferguson April 12, 2017 / 9:23 am

    Thanks Stephen! I want to add V_Package.SourceVersion, how would you do that? What would your query look like then?

    • FoxDeploy April 12, 2017 / 9:26 am

      Sure thing, Robert! Since we already are selcting the table `v.Package`, all you need to do is add the column you want to the select statement at the top, like this: `SELECT Program.PackageID,Package.SourceVersion` and the extra column will be added.

      • robert30062 April 12, 2017 / 9:49 am

        Wow I am an idiot. That was ridiculously easy after you gave me the heads up. LOL. I added the comma after Package.SourcePath then on the next line “Package.SourceVersion”. And for the record, we are referencing V_Package from your ‘Left Join’ correct?

        • FoxDeploy April 12, 2017 / 10:00 am

          Yep! The way these queries are laid out (Structured Query Language) queries, that is, is that first we Select the properties we want to see, FROM the tables or views that have these columns, WHERE they meet certain criteria. When we pick our tables and views, we can add an alias, and we selected V_Packages as Package, we define Package as the alias. Left Join v. Right join v. Inner joins are basically the Vinn diagrams of how to process the data.

        • FoxDeploy April 12, 2017 / 10:17 am

          And another thing, you’re not an idiot. You’re learning and actively seeking knowledge. That means you’re smart enough to be able to plug the gaps in your knowledge, which will only make you smarter! There’s no shame in learning

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s