How to write SQL query in MECM for devices last patched

In Microsoft Endpoint Configuration Manager (MECM), you can query devices’ last patching date using SQL queries run against the MECM database. Here’s a sample SQL query:

SELECT
sys.Name0 AS [Device Name],
sys.User_Name0 AS [Last Logged On User],
up.LastScanDate AS [Last Scan Date],
ups.LastComplianceTime AS [Last Patch Date],
ups.LastStatusChangeTime AS [Last Status Change],
coll.Name AS [Collection Name]
FROM v_R_System AS sys
JOIN v_UpdateScanStatus AS up ON sys.ResourceID = up.ResourceID
JOIN v_UpdateComplianceStatus AS ups ON sys.ResourceID = ups.ResourceID
JOIN v_FullCollectionMembership AS coll ON sys.ResourceID = coll.ResourceID
WHERE ups.LastComplianceState = 1 — 1 means Compliant
ORDER BY ups.LastComplianceTime DESC;

Explanation:

• v_R_System: Contains device information.
• v_UpdateScanStatus: Shows last scan dates.
• v_UpdateComplianceStatus: Contains compliance details for updates.
• v_FullCollectionMembership: Lists devices in collections.

Customization Options:

• Adjust the WHERE clause to filter by specific collections, compliance status, or patch categories.
• Use LastComplianceState = 0 to find non-compliant devices.