In a recent conversation with a customer managing endpoints via SCCM ConfigMgr, we discussed the need to monitor the installation of critical security applications. Specifically, the customer wants to ensure that devices have essential applications—such as antivirus and device monitoring tools—installed. If any device is missing a required application, it should be flagged in a report.
Use Case
For instance, consider three essential security agents: Qualys agent, Netskope, Cisco VPN. It’s crucial that these applications are installed on every device. If a device is missing any of these agents, we need a mechanism to identify it in our reporting.
To meet this requirement, we will create an SQL report for monitoring purposes.
For any SQL reporting in SCCM, I will always have the SQL views datasheet ready. This sheet will help me to identify the right SQL views for the information that I am looking for.
Required Tables for this use case
To generate this report, we will utilize the following SQL tables:
- v_Add_Remove_Programs: Contains data on installed applications.
- v_R_System: Contains information about the devices.
Reporting Goals
By the end of this post, we will achieve:
1.Count of Devices: Identify how many devices have each application installed, how many are missing, and provide a detailed report (excluding server OS).
2. List of Devices: Generate a list of devices missing any of the specified applications (excluding server OS).
SQL Query for Counting Installed and Missing Applications
You can customize the list of applications as needed, including using wildcards if the exact application name isn't known. Here’s the SQL query to get the count of installed and missing applications:
WITH RequiredApplications AS (
SELECT '7-Zip %' AS DisplayNamePattern
UNION ALL SELECT 'Google Chrome'
UNION ALL SELECT 'MDOP MBAM'
UNION ALL SELECT 'Microsoft Power BI Desktop'
),
DeviceApplications AS (
SELECT
ra.DisplayNamePattern,
COUNT(*) AS TotalDevices, COUNT(arp.DisplayName0) AS InstalledDevices, COUNT(*)-COUNT(arp.DisplayName0) AS MissingDevices
FROM
v_R_System sys
CROSS JOIN
RequiredApplications ra
LEFT JOIN
v_Add_Remove_Programs arp
ON
sys.ResourceID = arp.ResourceID AND arp.DisplayName0 LIKE ra.DisplayNamePattern
WHERE
sys.Operating_System_Name_and0 NOT LIKE '%Server%'
GROUP BY
ra.DisplayNamePattern
)
SELECT
DisplayNamePattern AS ApplicationName,
TotalDevices,
InstalledDevices,
MissingDevices
FROM
DeviceApplications
ORDER BY
MissingDevices DESC;
SQL Query for Listing Devices with Missing Applications
To list devices missing any of the specified applications, use the following SQL query:
WITH RequiredApplications AS (
SELECT '7-Zip %' AS DisplayNamePattern
UNION ALL SELECT 'Google Chrome%'
UNION ALL SELECT 'MDOP MBAM'
UNION ALL SELECT 'Microsoft Power BI Desktop'
),
DeviceApplications AS (
SELECT
sys.ResourceID,
sys.Name0 AS DeviceName,
STRING_AGG(ra.DisplayNamePattern, ', ') AS MissingApplications
FROM
v_R_System sys
CROSS JOIN
RequiredApplications ra
LEFT JOIN
v_Add_Remove_Programs arp
ON
sys.ResourceID = arp.ResourceID AND arp.DisplayName0 LIKE ra.DisplayNamePattern
WHERE
sys.Operating_System_Name_and0 NOT LIKE '%Server%'
GROUP BY
sys.ResourceID, sys.Name0
HAVING
COUNT(arp.DisplayName0) < (SELECT COUNT(*) FROM RequiredApplications)
)
SELECT
DeviceName,
MissingApplications
FROM
DeviceApplications;
Hope you find this post useful!
if you have any custom reporting requirements, feel free to comment below.