-- This query lists the most recent activity of agents for all applications using that agent, with specific versions that are not deleted, along with the application name and agent version.
-- If there are multiple applications using the same agent, each application will be listed with the agent's most recent activity, if an application does not show here, but is in the UI, 
-- that would indicate that there is no deployment activity from that agent for that application after the specified time.
-- You need the SQL server extension for VSCode to run this script directly from VSCode

WITH ranked AS (
    SELECT 
        a.name as agent_name,
        r.application_id,
        app.name AS application_name,
        a.agent_version,
        r.submitted_time,
        DATEADD(SECOND, r.submitted_time / 1000, '1970-01-01') AS submitted_time_utc,
        ROW_NUMBER() OVER (PARTITION BY a.id, app.id ORDER BY r.submitted_time DESC) AS rn
    FROM ibm_ucd.ds_agent a
    JOIN ibm_ucd.rt_comp_process_request r ON r.agent_id = a.id
    JOIN ibm_ucd.ds_application app ON r.application_id = app.id
    -- To get agents active after a specific time, uncomment the line below and set the desired date, at the end of the line and uncomment the dashes at the beginning
    -- reverse the ">"" for before a time
    --
    --WHERE DATEADD(SECOND, r.submitted_time / 1000, '1970-01-01') > '2025-10-01 00:00:00'
    -- 
    where 1 = 1
    AND a.ghosted_date = 0
    -- and a.agent_version LIKE '%7.0.3%'
    --   AND (a.agent_version LIKE '%7.1.2%' OR a.agent_version LIKE '%7.0.3%')
)

SELECT agent_name, application_name, agent_version, submitted_time_utc
FROM ranked
WHERE rn = 1
ORDER BY agent_name ASC, application_name ASC, submitted_time_utc DESC;