SCCM SQL查询需求：按最后使用日期筛选软件仅显示最新记录

Alright, let's get this sorted out. Your original query has the right idea with using a subquery to find the max last used date, but the table joins were still pulling in all individual usage records instead of just the latest one. Here's the revised version that will give you exactly what you need—one record per computer-software combination, showing only the most recent usage date, sorted from newest to oldest:

SELECT 
    SYS.Name0 AS 'Computer',
    max_usage.CompanyName0 AS 'Publisher',
    max_usage.ProductName0 AS 'Software Name',
    max_usage.ProductVersion0 AS 'Version',
    Console.TopConsoleUser0 AS 'User',
    max_usage.Max_LastUsed AS 'Last Used Date',
    ABS(DATEDIFF(day, GETDATE(), max_usage.Max_LastUsed)) AS 'Days Since Used'
FROM 
    (
        -- Subquery to get the latest usage date per device-software pair
        SELECT 
            ResourceID,
            CompanyName0,
            ProductName0,
            ProductVersion0,
            MAX(LastUsedTime0) AS 'Max_LastUsed'
        FROM 
            v_GS_CCM_RECENTLY_USED_APPS
        WHERE 
            ProductName0 LIKE '%indesign%' 
            OR ProductName0 LIKE '%Incopy%' 
            OR ProductName0 LIKE '%Photoshop%'
        GROUP BY 
            ResourceID, CompanyName0, ProductName0, ProductVersion0
    ) AS max_usage
LEFT OUTER JOIN 
    dbo.v_R_System AS SYS ON max_usage.ResourceID = SYS.ResourceID
LEFT OUTER JOIN 
    v_GS_System_Console_Usage AS Console ON max_usage.ResourceID = Console.ResourceID
WHERE 
    SYS.Name0 LIKE '%' -- Adjust this to filter specific computers if needed
ORDER BY 
    max_usage.Max_LastUsed DESC; -- Sort by most recent usage first

Key Changes & Explanations: #

• Overhauled query structure: We start with a subquery that aggregates the latest usage date for each device-software combination first. This ensures we only ever get one record per pair, eliminating duplicate entries with older dates.
• Streamlined joins: Instead of joining to the full usage table in the main query, we join the pre-aggregated subquery to the system and console user tables. This avoids pulling in unnecessary extra records.
• Explicit sorting: Added ORDER BY max_usage.Max_LastUsed DESC to sort results from the most recently used software to the oldest—exactly what you requested.
• Removed redundant filters: The product name filter is only applied in the subquery now, so we don't repeat logic unnecessarily.

Quick Tweaks You Might Want: #

• If you don't need the Days Since Used column, just delete that line from the main SELECT statement.
• Narrow down the computer filter by modifying SYS.Name0 LIKE '%' (e.g., SYS.Name0 LIKE 'LAPTOP-%' to only show laptop devices).

内容的提问来源于stack exchange，提问作者David Newman