System Center Configuration Manager (SCCM) is a robust solution for managing endpoints, software deployments, and compliance across enterprises. As an SCCM administrator, there are times when you need detailed insights into application deployments, including install counts, deployment status, and metadata such as creation dates and ownership.
In this blog, we’ll walk through an SQL query using the fn_ListLatestApplicationCIs(1033) function to pull a rich dataset of all applications in SCCM, including:
Application name and description
Install count across workstations
Deployment and dependency information
Metadata like creation/modification date and author
---
🎯 Objective
To generate a report that includes:
Application Name
Application ID / Description
Install Count (Number of Devices)
Date Created & Last Modified
Last Modified By
Deployment Status
Dependencies
Expiration Status
---
🧾 SQL Query
Here’s the full query that accomplishes this:
SELECT
DisplayName AS AppName,
Description AS AppID,
NumberOfDevicesWithApp AS WorkstationCount,
DateCreated,
DateLastModified,
LastModifiedBy,
IsDeployed,
NumberOfDependedDTs,
NumberOfDependentTS,
App.IsExpired
FROM
dbo.fn_ListLatestApplicationCIs(1033) AS app
WHERE
app.IsLatest = '1'
ORDER BY
AppName;
---
📈 Example Output
| AppName | AppID | WorkstationCount | DateCreated | IsDeployed | IsExpired |
| ------------ | -------- | ---------------- | ------------------- | ---------- | --------- |
| Chrome | CHRM2021 | 1587 | 2021-06-15 08:30:00 | 1 | 0 |
| Adobe Reader | ADR2022 | 1342 | 2022-03-10 14:12:00 | 1 | 0 |
| LegacyApp | LEG2018 | 50 | 2018-09-20 10:00:00 | 0 | 1 |
---
🔧 Customization Ideas
You can enhance this report further by:
Filtering by IsDeployed = 1 to see only active deployments
Joining with deployment collections to view targeting scope
Adding filters by creation or modification date
---
✅ Conclusion
This SCCM SQL query offers a powerful way to audit application installations, monitor deployment reach, and track application lifecycle metadata. Whether you're cleaning up old packages, preparing for an audit, or just trying to understand your application estate better, this query is a solid addition to your reporting toolkit.
