Version CB 1802 introduced the Management Insights in the Management tab which has been extended in the recent Technical Previews.
Most helpful are to my mind the options to identify empty collections (to increase the performance of the system by eliminating unnecessary updates) and applications without deployment (to reduce disk space consumption).
Unfortunately, the results are displayed in a small, unsizeable window and cannot be copied. Although the Technical Previews meanwhile contain the Take Action button to display the result in a collection, you may want to see some details before you start a cleanup.
Below are two queries which are based on the view vSMS_ManagementInsightResultsList on which all entries in Management Insights are based upon.
For empty collections:
select Collections.CollectionName, Collections.CollectionID, Collections.CollectionComment, Collections.LimitToCollectionName, Collections.LastChangeTime from vSMS_ManagementInsightResultsList
left join Collections on Collections.SiteID = vSMS_ManagementInsightResultsList.ObjectID
where vSMS_ManagementInsightResultsList.Name = ‘MI_EmptyCollections’
For applications without deployment:
select distinct LLACI.DisplayName, LLACI.Manufacturer, LLACI.DateCreated, LLACI.DateLastModified, LDTCI.Technology, LDTCI.CreatedBy, LDTCI.LastModifiedBy, psrs.SourceSize, ‘Source Size (MB)’ = psrs.SourceSize/1024
from vSMS_ManagementInsightResultsList MIRL
left join fn_ListLatestApplicationCIs(1033) LLACI on LLACI.CI_ID=MIRL.ObjectID left join fn_ListDeploymentTypeCIs(1033) LDTCI on LDTCI.AppModelName =LLACI.ModelName
LEFT JOIN v_PackageStatusRootSummarizer psrs on psrs.Name=LLACI.DisplayName
where MIRL.Name like ‘MI_ApplicationsNotDeployed’
Finally, mind that the query above is really limited to applications in the SCCM sense. To get the full picture including unused packages, task sequences, disk images etc. the query below may be helpful.
SELECT pkg.Name as [Package Name], pkg.Version, pkg.PackageID, ‘Source Size (MB)’ = psrs.SourceSize/1024,
CASE pkg.PackageType
WHEN 0 THEN ‘Software Distribution Package’
WHEN 3 THEN ‘Driver Package’
WHEN 4 THEN ‘Task Sequence Package’
WHEN 5 THEN ‘Software Update Package’
WHEN 6 THEN ‘Device Settings Package’
WHEN 7 THEN ‘Virtual Package’
WHEN 8 THEN ‘Application’
WHEN 257 THEN ‘Image Package’
WHEN 258 THEN ‘Boot Image Package’
WHEN 259 THEN ‘OS Install Package’
END AS [Package Type], adv.AdvertisementID, tsp.Name as [TS Name]
FROM v_Package pkg
LEFT JOIN v_Advertisement adv on pkg.PackageID=adv.PackageID
LEFT JOIN v_PackageStatusRootSummarizer psrs on pkg.PackageID=psrs.PackageID
LEFT JOIN v_TaskSequencePackage tsp on tsp.PackageID=pkg.PackageID
WHERE adv.AdvertisementID is null
AND tsp.Name is null
ORDER by psrs.SourceSize
Link:
https://docs.microsoft.com/en-us/sccm/core/servers/manage/management-insights