Introduction:
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:
dbo.fn_ListApplicationCIs(1033)
vSMS_AppRelation_Flat
v_TaskSequenceAppReferencesInfo
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.
10 Comments
Hey,
Could you share the updated link to the source file "RDL file from Technet Gallery"
(https://gallery.technet.microsoft.com/SCCM-Configmgr-how-to-find-21f470eb) is broken.
Hi Bob,
Here is how you can download the samples from Technet Gallery http://eskonr.com/2021/02/did-you-miss-to-download-your-samples-from-technet-gallery-here-is-how-you-can-do-it/
Thanks,
Eswar
Should this work for SCCM 2007 too?
in SCCM 2007 ,there is no applications concept ,it is only packages for which you can refer my other post.
Regards,
Eswar
Not working for me. Dataset1?
Hi,
did you change the datasource after you upload the RDL file ? what error you get exactly .
Regards,
Eswar
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'.
Hi,
This can be resolved by adding the reporting user with the datareader permission on the database or by giving the select permission to the views.
Regards,
Eswar
Please make a monkey-see-monkey-do guide 🙂
Microsoft.Reporting.WinForms.ReportServerException
An error has occurred during report processing. (rsProcessingAborted)
Stack Trace:
at Microsoft.Reporting.WinForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension)
at Microsoft.Reporting.WinForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension)
at Microsoft.Reporting.WinForms.AsyncMainStreamRenderingOperation.RenderServerReport(ServerReport report)
at Microsoft.Reporting.WinForms.AsyncRenderingOperation.PerformOperation()
at Microsoft.Reporting.WinForms.ReportViewer.AsyncReportOperationWrapper.PerformOperation()
at Microsoft.Reporting.WinForms.ProcessingThread.ProcessThreadMain(Object arg)
Hi,
Have no idea what does that error means but is that what you get when running the report ? You can use the following SQL query to run in SQL management studio to troubleshoot the problem.
SQL Code :
select parentapp.CI_ID, ParentApp.displayname [Application Name],
parentapp.CreatedBy [Created By],parentapp.DateLastModified,datediff(dd,parentapp.DateLastModified,GETDATE()) as 'Age of App (days)',
case when parentapp.IsEnabled='1' then 'Yes' else 'No' end as IsEnabled,
case when parentapp.IsDeployed='1' then 'Yes' else 'No' end as IsDeployed,
parentapp.NumberOfDeploymentTypes [No of DT],
count(distinct ChildApp.DisplayName) AS [No of Dependencies],
ISNULL((
select count(distinct apps.CI_UniqueID) From dbo.fn_ListApplicationCIs(1033) apps
inner Join fn_ListDeploymentTypeCIs(1033) appDT ON appDT.AppModelName = apps.ModelName and appdt.IsLatest = 1
inner Join vSMS_AppRelation_Flat appRel on appRel.FromApplicationCIID = apps.CI_ID
inner Join fn_ListApplicationCIs_List(1033) ChildApp on ChildApp.CI_ID = appRel.ToApplicationCIID And ChildApp.IsLatest = 1 and apps.IsDeployed = 1
where apps.IsLatest=1 and childapp.CI_ID = parentapp.CI_ID
group by childapp.CI_ID, childapp.DisplayName
--having count(distinct apps.CI_UniqueID)=0
), 0) AS DependantDeployments,
parentapp.NumberOfDevicesWithApp,
parentapp.NumberOfDevicesWithFailure
From dbo.fn_ListApplicationCIs(1033) ParentApp
Left Join fn_ListDeploymentTypeCIs(1033) ParentApp_DT ON ParentApp_DT.AppModelName = ParentApp.ModelName
Left Join vSMS_AppRelation_Flat R on R.FromApplicationCIID = ParentApp.CI_ID
Left Join fn_ListApplicationCIs_List(1033) ChildApp on ChildApp.CI_ID = R.ToApplicationCIID And ChildApp.IsLatest = 1
where parentapp.IsLatest=1 and parentapp.NumberOfDeployments=0
and parentapp.CI_ID not in(select RefAppCI_ID from v_TaskSequenceAppReferencesInfo)
group by parentapp.DisplayName,parentapp.CreatedBy,parentapp.DateLastModified,parentapp.NumberOfDeploymentTypes,parentapp.NumberOfDeployments,
parentapp.NumberOfDevicesWithApp,
parentapp.NumberOfDevicesWithFailure,
parentapp.IsEnabled,
parentapp.IsDeployed,
parentapp.CI_ID
Regards,
Eswar