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.:
Advertisements
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.
LikeLike
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.
LikeLike
Thanks for that Query! Really helped me out!
LikeLike
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.
LikeLike
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)
LikeLike
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!
LikeLike
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
LikeLike
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!!
LikeLike
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.
LikeLike
Thanks Stephen! I want to add V_Package.SourceVersion, how would you do that? What would your query look like then?
LikeLike
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.
LikeLike
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?
LikeLike
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.
LikeLike
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
LikeLike