Sunday, August 7, 2016

SCCM Report - Application Content Maintenance

I had a client recently that wanted to perform some maintenance tasks on their SCCM infrastructure before upgrading to the new SCCM Current Branch.  Part of this maintenance is cleaning up stagnant Application Content from Distribution Points.

Now over the many years they amassed a great deal of Applications and needed a clear idea of how many stagnant Applications existed.  We determined that if an Application has not been "deployed" then we would consider it stagnant as it currently serves no purpose.  The below query is what was used to show us a report of all Applications with the property "IsDeployed" being "0".


All SMS_ApplicationLatest.DisplayName,

FROM fn_ListLatestApplicationCIs_List(1033) AS SMS_ApplicationLatest
WHERE IsDeployed = 0
Order By DisplayName


And the result of running such a query is below.

Now this was of course just pre-selected headers I wanted, you can modify the SQL code for what might fit your purpose.  The screenshot below will show you all the possible headers that you can make visible by adding them to your SQL Query.

When all is said and done you can then bring this code into SSRS and create a SCCM report for a cleaner look or simply just copy from SQL into Excel as it might not be a report you run frequently.  


  1. Reports in SCCM are not very good. That's why I'm using Total software deployment. This is software with user friendly interface an the same functions.

    1. Hello Elizabeth. You are right, there is a certain level of comfort with SQL you'd need to create reports in SCCM. I do wish, and there probably is, a more user-friendly graphical interface with a sort of drag and drop of what you want queried. But I guess the assumption is at the scale of clients needing SCCM there probably also is a SQL team that could help with the reports.