Retrieve All SCCM/MECM Applications That Have No Active Deployments || ConfigMgr

 

Finding Unused SCCM Applications with SQL: A Quick Reporting Query

In every SCCM (Microsoft Endpoint Configuration Manager) environment, unused or unreferenced applications gradually accumulate over time. These unused applications clutter the console, consume storage, and complicate administrative tasks such as audits, cleanup, and migration.

To help identify such applications, here is a SQL query that lists SCCM applications that exist in the environment but are not deployed, not referenced in task sequences, and have no dependent relationships. This report is very useful for cleanup and housekeeping activities.

---

SQL Query: Identify Unused/Orphaned SCCM Applications

select apps.DisplayName as 'ApplicationName',apps.Description, 
 apps.Softwareversion as 'Version',apps.Manufacturer,apps.createdby, apps.Datelastmodifiedby,
 pkg.PackageID,
 CASE pkg.PackageType
 WHEN 0 THEN 'Package'
 WHEN 3 THEN 'Driver'
 WHEN 4 THEN 'TaskSequence'
 WHEN 5 THEN 'SoftwareUpdate'
 WHEN 6 THEN 'DeviceSettings'
 WHEN 7 THEN 'Virtual'
 WHEN 8 THEN 'Application'
 WHEN 257 THEN 'Image'
 WHEN 258 THEN 'BootImage'
 WHEN 259 THEN 'OSInstall'
 END AS 'PackageType',
 apps.NumberOfDeploymentTypes as 'NoofDT',
 apps.NumberOfDeployments,
 apps.NumberOfDependentTs
 from fn_ListLatestApplicationCIs(1033) apps
 left join v_TaskSequencePackageReferences tspr on tspr.ObjectID = apps.ModelName
 left join vSMS_ApplicationAssignment ass on ass.AssignedCI_UniqueID = apps.CI_UniqueID
 left join v_Package pkg on pkg.SecurityKey = apps.ModelName
 where
 PackageType = 8
 and apps.IsLatest=1
 and ass.AssignmentName IS NULL
 and tspr.PackageID IS NULL
 and apps.NumberOfDeployments = 0
 and apps.NumberOfDependentTs = 0
 and apps.IsLatest=1
 order by apps.DisplayName

---

What This Query Does

This SQL query helps you quickly identify SCCM applications that are:

✔ Not deployed to any collection

apps.NumberOfDeployments = 0

✔ Not referenced in any task sequence

tspr.PackageID IS NULL

✔ Not depended on by other task sequences

apps.NumberOfDependentTs = 0

✔ Not assigned to any users or devices

ass.AssignmentName IS NULL

✔ Latest version of the application only

apps.IsLatest = 1

✔ Applications only (not packages, drivers, OS images, etc.)

PackageType = 8

This gives you a clean, filtered list of orphaned or unused applications that can be reviewed for retirement or cleanup.

---

Columns Returned by the Report

The result includes helpful administrative fields:

• ApplicationName – Display name of the application

• Description – App description (if provided)

• Version – Software version

• Manufacturer – Vendor or publisher

• CreatedBy / DateLastModifiedBy – Who created or modified the app

• PackageID – Package ID associated with the application

• PackageType – Interpreted using the CASE statement

• NoofDT – Number of deployment types

• NumberOfDeployments – Total deployments

• NumberOfDependentTs – Task sequences that depend on it

These fields help administrators understand the lifecycle and usage of each application.

---

Why This Report Is Useful

1. Cleanup and Housekeeping

This report helps identify applications that can safely be deleted or reviewed because they are not actively used.

2. Migration Projects

Before migrating to:

• A new SCCM site

• Intune

• A new infrastructure
  This report helps detect unused apps to avoid migrating unnecessary content.

3. Compliance and Inventory

Auditors or admins can verify which apps are actively managed vs. inactive.

4. Environment Optimization

Removing unused apps improves:

• Console performance

• Content library organization

• Storage utilization

---

How to Run the Query

1. Open SQL Server Management Studio (SSMS)

2. Connect to the SCCM site database (e.g., CM_ABC)

3. Open a new query window

4. Paste the SQL query

5. Execute and review the output

How to Pull SCCM Application Content Information Using SQL

 How to pull SCCM application content data using SQL, how to report on SCCM application content through SQL queries: -

Introduction: Why Application Content Reporting Matters

• In SCCM/MECM environments, applications often contain multiple deployment types and associated content files.

• Understanding application metadata, deployment technologies, and content size/location is essential for:

  • Application audits

  • Content management and cleanup

  • Migration assessment (SCCM → Intune)

  • Troubleshooting deployment issues

  • Optimizing disk usage on Distribution Points

• SCCM does not include a built-in report that clearly correlates all this information, so SQL queries become highly useful.

---

2. Purpose of the SQL Query

Explain what the query is designed to do:

• Pull a list of all latest SCCM applications.

• Display their deployment types and priority.

• Show technology type (MSI, Script, PowerShell, AppX, etc.).

• Fetch content source (network path or source folder).

• Retrieve content size from the content library.

• Provide a single consolidated view of all applications and their content.

This query is popular for application inventory, planning, cleanup, and documentation.

---

3. SQL Query (Formatted for the Blog)

Select Distinct VApp.Manufacturer, VApp.Displayname, Dts.Displayname As Deploymenttypename, Dts.Priorityinlatestapp, Dts.Technology, V_Contentinfo.Contentsource, V_Contentinfo.Sourcesize From Dbo.Fn_Listdeploymenttypecis(1033) As Dts 
Inner Join Dbo.Fn_Listlatestapplicationcis(1033) As Vapp On Dts.Appmodelname = App.Modelname 
Left Outer Join V_Contentinfo On Dt.Contentid = V_Contentinfo.Content_Uniqueid
Where (Dts.Islatest = 1)

---

4. Explanation of Each SCCM SQL Function/Table

▶ fn_ListLatestApplicationCIs (1033)

• Lists all latest application versions.

• Key fields:

  • Application Name

  • Manufacturer

  • Model Name (used for joining to deployment types)

▶ fn_ListDeploymentTypeCIs (1033)

• Returns deployment type CI information.

• Displays:

  • Deployment Type Name

  • Technology (MSI, Script, etc.)

  • Priority

  • Content ID

▶ V_ContentInfo

• Correlates deployment types to their content.

• Shows:

  • Content Source Path

  • Content Size

  • Unique Content ID

---

5. What the Report Shows

The query returns:

Field	Description
Manufacturer	Vendor name (Adobe, Microsoft, etc.)
DisplayName	Application name
DeploymentTypeName	Install script, MSI installer, AppX package, etc.
PriorityInLatestApp	DT run order
Technology	MSI, Script, PowerShell, IntuneWin, etc.
ContentSource	Original file location used to create the app
SourceSize	Total size of the application content

This gives admins a complete view of application structure + content details.

---

6. Use Cases for SCCM Admins

✔ Content Library Cleanup

Identify:

• Large applications

• Duplicate content

• Missing source paths

✔ Application Audit

Get clarity on how applications are built.

✔ Migration Planning

Helpful when moving to:

• Intune

• New SCCM infrastructure

• Application modernization projects

✔ Deployment Troubleshooting

Useful when apps fail because of:

• Wrong content source

• Missing files

• Incorrect deployment type priority

✔ Compliance & Documentation

Generate documentation for audit teams.

---

7. How to Run the Query in SCCM

Provide steps:

1. Open SCCM Console

2. Navigate to Monitoring → Database → SQL Server Views

3. Open SQL Server Management Studio (SSMS)

4. Connect to your SCCM site database

5. Paste the SQL query

6. Run the query to generate your report

(Optional: include screenshots in your blog.)

---

8. Enhancing the Report (Optional)

Admins can modify the query to include:

• App creation date

• Last updated date

• User experience details

• Detection method type

• Content hash info

• Distribution status

Mention that these additions depend on needs.

---

9. Conclusion

Summarize:

• SCCM does not provide a single built-in report for application + content data.

• This SQL query provides a clear, detailed overview of:

  • Application metadata

  • Deployment types

  • Technologies

  • Content source

  • Content size

• Essential for auditing, troubleshooting, and managing application lifecycle.

SCCM/MECM Package cleanup PowerShell automation scripts

 

Title Options

1. Automating SCCM Package Cleanup with PowerShell: Step-by-Step Script Explained

2. How to Remove Packages from MECM Automatically Using PowerShell

3. PowerShell Script to Clean Up SCCM Packages and DP Groups: A Complete Guide

4. Streamline Your SCCM Environment with This Package Removal Script

---

SEO Keywords

• SCCM package cleanup

• MECM PowerShell scripts

• Remove-CMContentDistribution

• SCCM automation

• MECM package removal

• Cleanup unused packages in SCCM

• PowerShell SCCM examples

---

Meta Description

Learn how to automate SCCM/MECM package cleanup using a PowerShell script. This guide walks you through removing Distribution Point groups, clearing content, and deleting packages from the MECM database.

---

Automating SCCM/MECM Package Cleanup Using PowerShell

Managing and maintaining a clean Microsoft Endpoint Configuration Manager (MECM/SCCM) environment is essential for performance, disk optimization, and operational efficiency. Over time, unused or outdated packages accumulate in the SCCM database and Distribution Points (DPs), leading to unnecessary clutter.

To help streamline this process, here’s a PowerShell script that automates the cleanup of SCCM packages—removing DP group associations, clearing content distribution, and deleting the package from the MECM database.

---

Why Automate SCCM Package Cleanup?

Manual cleanup takes time and is prone to errors. Automation helps you:

• Remove outdated packages quickly

• Maintain a clean MECM database

• Eliminate orphaned content on DPs

• Reduce overhead on administrators

• Improve overall SCCM environment performance

If you're dealing with a long list of packages, this script can save hours of manual work.

---

PowerShell Script for SCCM Package Cleanup

Below is the script used for the cleanup operation:

$PKGID=get-content "C:\Cleanup\Package-List.txt"

Write-Host "Package removal initiated, Please wait**" -BackgroundColor -DarkMagenta
Remove-CMContentDistribution -Packageld $Packageid DistributionPointGroupName "Distribution Point Group1" -Force

foreach ($Packageid in $PKGID)
{

    # Removing associated DP's group from the Packages
    Write-Host "Removing associated DP's group from the Packages:-" -BackgroundColor Gray

    Remove-CMContentDistribution -Packageld $Packageid -DistributionPointGroupName "Enter the Dp group Name" -Force 
    Remove-CMContentDistribution -PackageId $Packageid -DistributionPointName "Enter the DP Name" -Force

    Write-Host "$Packageid, Associated DP's group removed" -BackgroundColor Green

    Write-Host "Please wait... " -BackgroundColor Gray

    # Removing the Packages from SCCM DB
    Write-Host "Removing Package from MECM DB......" -BackgroundColor Gray

    Remove-CMPackage -Id $Packageid -Force

    Write-Host "Successfully removed PackageID $Packageid from MECM..."  -BackgroundColor Green
}

---

How the Script Works

1. Importing the Package List

The script begins by reading a text file that contains a list of Package IDs to remove.

$PKGID=get-content "C:\Cleanup\Package-List.txt"

This allows bulk operations instead of manually entering each package.

---

2. Initial Cleanup Message

A simple status message notifies the admin that the removal process has started.

Write-Host "Package removal initiated, Please wait**"

---

3. Removing Content Distribution

Before a package can be removed, you must clear it from Distribution Point Groups or individual DPs.

Remove-CMContentDistribution -PackageId $Packageid -DistributionPointGroupName "Distribution Point Group1" -Force

---

4. Looping Through Each Package

The script iterates through every Package ID in the input file:

foreach ($Packageid in $PKGID)

Inside this loop, it performs:

a. Removing DP Group associations

Remove-CMContentDistribution -PackageId $Packageid -DistributionPointGroupName "Enter the Dp group Name" -Force

b. Removing DP-specific content

Remove-CMContentDistribution -PackageId $Packageid -DistributionPointName "Enter the DP Name" -Force

---

5. Removing Packages from MECM Database

Once DP cleanup is complete, the script deletes the package entirely from MECM:

Remove-CMPackage -Id $Packageid -Force

This ensures a clean database without orphaned references.

---

Benefits of This Script

• Time-saving: Removes hundreds of packages in minutes

• Prevents human error: Avoids accidental deletion of wrong packages

• Keeps DPs clean: Ensures no leftover content remains

• Improves SCCM performance: Reduced clutter leads to faster operations

• Fully automated: Run it once, and it handles everything

---

Important Notes Before Running

✔ Ensure you have appropriate SCCM permissions
✔ Test in a LAB environment
✔ Backup package info before bulk deletion
✔ Verify Distribution Point names and DP group names
✔ Ensure the path to the package list file is correct

---

Conclusion

A clean and optimized SCCM/MECM environment ensures smoother deployments and fewer system issues. This PowerShell script greatly simplifies the package cleanup process by automating removal from Distribution Points and the MECM database.

If you manage large-scale SCCM infrastructures, automating repetitive cleanup tasks like this can significantly reduce administrative overhead and improve overall environment health.

Bulk Remove SCCM Applications and Their Distribution Points Using PowerShell

Managing applications in Microsoft Endpoint Configuration Manager (MECM/SCCM) can become time-consuming—especially when you need to remove multiple applications along with their associated Distribution Points (DPs) or DP Groups.

To simplify this process, here’s a PowerShell script that:

✔ Reads application names from a text file
✔ Removes associated Distribution Point Groups
✔ Removes individual Distribution Points
✔ Deletes the application from the MECM database
✔ Displays progress and success messages in the console

This script is very helpful during environment cleanups, migrations, or when retiring large numbers of outdated applications.

PowerShell Script: Bulk Application & DP Cleanup in SCCM

$application = Get-Content "C:\applicationlist.txt"

Write-Host "Application removal initiated, Please wait..." -BackgroundColor DarkMagenta

foreach ($applicationname in $application)
{
    # Removing associated DP group from application
    Write-Host "Application Name: $applicationname" -BackgroundColor Blue

    Write-Host "Removing associated DP group and DP from the application..." -BackgroundColor Gray

    Remove-CMContentDistribution -ApplicationName $applicationname -DistributionPointGroupName "Enter the distribution group Name" -Force
    Remove-CMContentDistribution -ApplicationName $applicationname -DistributionPointName "Enter the DP Name" -Force

    Write-Host "$applicationname - Associated DP group and DP removed successfully." -BackgroundColor Green

    Write-Host "Please wait..." -BackgroundColor Gray

    # Removing application from SCCM DB
    Write-Host "Removing application from MECM database..." -BackgroundColor Gray

    Get-CMApplication -Name "$applicationname" | Remove-CMApplication -Force

    Write-Host "Successfully removed application: $applicationname" -BackgroundColor Green
}

---

📂 Preparing the Application List

Create a simple text file at:

C:\applicationlist.txt

Add each application name on a new line—names must match exactly as shown in MECM:

Google Chrome
Mozilla Firefox
Adobe Reader
WinRAR

---

▶️ How the Script Works

1. Load application names

The script reads each application from the text file using Get-Content.

2. Remove DP Group & DP associations

It removes:

• The assigned Distribution Point Group

• The assigned Distribution Point

This ensures MECM doesn’t block deletion due to content distribution.

3. Remove the application from MECM

After cleanup, the script removes each application using:

Remove-CMApplication -Force

4. Shows clear status messages

Colored console messages indicate progress and completion.

---

⚠️ Important Notes Before Running

• Run this script in the Configuration Manager PowerShell environment.

• Replace the placeholders:

  • "Enter the distribution group Name"

  • "Enter the DP Name"

• Ensure you have Application Administrator or higher permissions.

• Test the script in a non-production environment first.

• This script permanently deletes applications, so proceed cautiously.

---

✔️ Benefits of Using This Script

• Saves hours of manual cleanup

• Automates DP and DP group removal

• Avoids MECM dependency errors

• Ensures consistent and repeatable cleanup

• Ideal for large-scale environment maintenance

How to Retire the SCCM/MECM application by using PowerShell command line

Retire Multiple Applications in SCCM Using PowerShell

Managing applications in SCCM (ConfigMgr) can become time-consuming when you need to retire multiple apps one by one. To simplify this process, you can use a PowerShell script that reads application names from a text file and retires them automatically.

This script uses the Suspend-CMApplication cmdlet to retire each application listed in applist.txt. It’s a fast and efficient way to bulk-retire applications—perfect for cleanup tasks, environment restructuring, or decommissioning outdated software.

PowerShell Script: Retire Applications

Suspend-CMApplication -Name $applicationname

PowerShell Script: Retire Applications in Bulk

$appname = Get-Content "C:\applist.txt"

foreach ($appname1 in $appname) 
{
    Suspend-CMApplication -Name $appname1
    Write-Host "$appname1 has been retired successfully" -BackgroundColor Green
}

How It Works
Input file:
 Create a text file named applist.txt at C:\ containing the list of application names—one per line.

Load the list:
 The script reads each line of the file using Get-Content.

Retire the applications:
 For each app name, the script runs Suspend-CMApplication, which retires the application in SCCM.
Status message:
 After each retirement, PowerShell displays a green success message.

SCCM/MECM Basic SQL Queries: -

 ******************Devices with OS details********************************

Select vr.Netbios_Name0,VGS.caption0 from V_GS_OPERTING_SYSTEM VGS

inner join v_r_system vr on vr.resourceid =vgs.resourceid

******* Device with LastHW, Last SW, Lastpolicyrequest and ClientActive Status***

select distinct vr. Netbios_Name0, vch. LastHw, vch.LastSW, vch.lastpolicyrequest,c=vch.clientactivestatus from v_ch_clienthealth vch

Inner join v_R_System vr on vr.resourceid=vch.machineid

**************ARP report********************************************

SELECT  

    VR.Name0 AS 'Computer Name',

    ar.DisplayName0 AS 'Application Name',

    ar.Version0 AS 'Version',

    ar.Publisher0 AS 'Publisher',

    ar.InstallDate0 AS 'Install Date'

FROM 

    v_R_System AS VR

INNER JOIN  v_Add_Remove_Programs AS ar  ON SYS.ResourceID = ar.ResourceID

*******************ARP report with Install source file details. ********

select VR.netbios_Name0, VR.Operating_System_Name_ando, VGS.ARPDisplayName0, vgs.ProductVersion0, vgs. InstallSource0, vgs.LocalPackage0 from v_R_System VR

Inner join v_GS_INSTALLED_SOFTWARE VGS on VGS.ResourceID=VR.ResourceID

Where vgs.ARPDisplayName0 like '%Enter the AppName%

**Application group Apps details**

select vapp.DisplayName, vapp.AdminComments, vapp.Manufacturer, vapp.SoftwareVersion, vapp.ObjectTypeID from v_applications vapp where vapp.ObjectTypeID='224'

*****Application group apps details***

select from v_AppGroupDisplayProperties -- It will show application group apps details

**Application group reference Apps details**

select fn.DisplayName, fn.Manufacturer, fn.SoftwareVersion, fn.NumberOfApplicationGroups, fn.NumberOfDeployments, fn. NumberOfDependedDTs, fn.NumberOfDevicesWithApp, fn.DateCreated, fn.DateLastModified, fn.LastModifiedBy, fn.CreatedBy from fn_ListLatestApplicationCIs(1033) fn 

where fn.NumberOfApplicationGroups >=1 

How to delete older user profile PowerShell script

#PowerShell Script to Remove Old User Profiles, #Deleting Outdated User Profiles with PowerShell, #How to Clean Up User Profiles Older Than One Year Using PowerShell, #PowerShell Method for Deleting Aged User Profiles, #Scripted Cleanup: Remove Older User Profiles with PowerShell

This PowerShell script scans Windows user profiles and removes any that are older than one year. It helps reclaim disk space, enhance system performance, and streamline workstation maintenance by clearing out stale or unused profiles. The script also generates a detailed report in C:\Temp\SystemName_Date_UserProfile.csv for review. Be sure to test it in your own environment before deployment—although it has been successfully tested and is working in ours.

# Define the date 1 year ago from today

$oneYearAgo = (Get-Date).AddMonths(-12)

#Create a log for user profile deletions

$FileSave="C:\temp\$(gc env:computername)_" + (get-date -format "MM-d-yy-HH-mm")+ "_userprofiledeletion.csv"

If(!(test-path -path $FileSave))

{

New-Item -ItemType file -Path $FileSave -Force

}

# Profiles to exclude

$profilesToExclude = @("KKG","specificProfileName")

# Get user profile directories under C:\Users

$UserFolders = Get-ChildItem -Path "C:\Users" -Force -Directory

# Initialize an array to store profile details

$profilesToExport = @()

# Iterate through each user profile directory

foreach ($Folder in $UserFolders) {

    # Skip profiles that are in the exclusion list

    if ($profilesToExclude -contains $Folder.Name) {

        continue

    }

    # Path to IconCache.db

    $iconCachePath = [System.IO.Path]::Combine($Folder.FullName, "AppData\Local\IconCache.db")

    Write-Host $iconCachePath

    # Initialize status

    #$status = "not eligible for deletion"

    # Get the LastWriteTime of the profile directory itself

    $profileLastWriteTime = $Folder.LastWriteTime

    # Calculate the size of the profile directory in GB

    [UInt64] $FolderSize = (Get-ChildItem -Path $Folder.FullName -Force -Recurse -ErrorAction SilentlyContinue | Measure-Object -Property Length -Sum).Sum

    $profileSizeGB = [math]::Round($FolderSize / 1GB, 4) # Rounds to 4 decimal places

   

    # Check if IconCache.db exists and get its last write time

    if (Test-Path -Path $iconCachePath) {

        $file = Get-Item -Path $iconCachePath -Force

        $iconCacheLastWriteTime = $file.LastWriteTime

        Write-Host $iconCacheLastWriteTime

        # Determine if IconCache.db is older than one year

        if ($iconCacheLastWriteTime -lt $oneYearAgo) {

            # Delete the profile directory

            try {

                Remove-Item -Path $Folder.FullName -Recurse -Force

                $status = "Profile deleted"

            } catch {

                $status = "failed to delete"

            } }

             else {

            $status = "Not eligible for deletion"

        }

    } else {

        $iconCacheLastWriteTime = "NA"

        

# Determine if User profile is older than two years

if ($profileLastWriteTime -lt $oneYearAgo) 

{

#Delete the user profile directory

try {

Remove-Item -Path $Folder.FullName -Recurse -Force

$status = "Deleting profile considering user profile timestamp >1 Years."

} catch {

$status= "Failed to delete profile." 

}

 }

 else {

$status = "Iconcache.db not found and user profile time < 1 Years. not eligible for deletion"

 }

  }

    

    # Create a custom object with profile details

    $profileDetails = [PSCustomObject]@{

        "User Profile" = $Folder.FullName

        "Last Write Time of User profile" = $profilelastwritetime

        "Last Write Time of IconCache file" = $iconCacheLastWriteTime

        "Profile Size (GB)" = $profileSizeGB

        "Status" = $status

    }

   

    # Add profile details to the array

    $profilesToExport += $profileDetails

}

# Export the profile details to a CSV file

#$csvFilePath = "C:\temp\profiles.csv"

$profilesToExport | Export-Csv -Path $Filesave -NoTypeInformation

Write-Output "Exported profile details with size and status to: $Filesave"