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".

----------------------------------------------------------------------------------------

Select
All SMS_ApplicationLatest.DisplayName,
SMS_ApplicationLatest.HasContent,
SMS_ApplicationLatest.IsEnabled,
SMS_ApplicationLatest.IsExpired,
SMS_ApplicationLatest.IsDeployed

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.