One of my blog reader asked question about ‘There is report in your blog to find out packages that has no advertisements created,but is there similar report to find applications with no deployments created’.
I found this question is valid and is needed if you want to perform maintenance tasks like cleanup packages,applications,collections etc .Performing regular maintenance is important to ensure correct site operations.
There are several default site maintenance tasks available that maintain the health of your site database but when it comes to clean up unused collections,packages,applications etc,you have to find way to do it.
There are instances where someone create application without any deployment or deployment was created and deleted it later leaving the application in the console for longer time ,many other instances .
If you are Maintaining a maintenance log to document dates that maintenance was conducted, by whom, and any maintenance-related comments about the task conducted, I would add the following tasks to the maintenance document as they also required to cleanup every 6 months or yearly once.
1.Clean up unused collections
2.clean up unused packages
3.clean up unused applications etc.
This blog post covers task 3 to identify applications without any deployments and not used in any other task sequence.
How to create SQL query or SSRS report to identify applications with no deployments?
To create such report ,first you need to identify the SQL views that store the information about applications and its dependencies. For that, you can refer Configmgr SQL view documentation available here
There are 3 main sql views/functions that store the information about applications ,its deployment info,dependent application info and task sequence app references etc and they are listed below:
I will be using above views to create nice SSRS report.This report mainly output applications that has zero deployments and these applications are not referenced in any task sequence.
This report contains fields like application name,Created by,Datelastmodified,Application age since created (days),Isenabled,is deployed,number of DT(deployment types),no of dependencies,number of devices with app,number of devices with failure.
After you run the report, take a look at column dependentdeployments as this filed refers this application is used as dependent application in another application (supersedence ).
ex: Application A appear in this report with dependentdeployments >1 which means ,application A is used as supersedence application in other applications listed in the dependentdeployments .
How does the report look:
Download the RDL file from Technet Gallery Here ,upload to your SSRS reports ,change the datasource and run it.
You might receive an error as seen below:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
The SELECT permission was denied on the object 'vSMS_AppRelation_Flat', database 'CM_XXX', schema 'dbo'.
This can be resolved by adding the reporting user with the datareader permission or by giving the select permission to the views.