/*

Fetch the cpu usage over time from [ActiveQuery_ResourceUse]
resolution is on a minute, but can be moved to an hour by playing with the group by clause.
The output of this query is used to populate an excel chart to graph the values

28.10.2024, TSC
*/
USE [ODB_JobManager];
GO

WITH cteSrc
AS (SELECT DISTINCT
           [cpu_db_percent],
           [cpu_other_percent],
           [cpu_idle_percent],
           [RunDate],
           CONVERT(VARCHAR(20), [RunDate], 120) AS tstamp
    FROM [dbo].[ActiveQuery_ResourceUse]
    WHERE [cpu_db_percent] IS NOT NULL
)

SELECT * 
FROM (
    SELECT 
        MIN([cteSrc].[tstamp]) AS tstamp,
        DATEPART(DAY, [cteSrc].[RunDate]) AS [day], 
        DATEPART(HOUR, [cteSrc].[RunDate]) AS [hour],
        DATEPART(MINUTE, [cteSrc].[RunDate]) AS [minute],
        AVG([cteSrc].[cpu_db_percent]) AS [avg_cpu_db_percent],
        AVG([cteSrc].[cpu_other_percent]) AS [avg_cpu_other_percent],
        AVG([cteSrc].[cpu_idle_percent]) AS [avg_cpu_idle_percent],
        AVG([cteSrc].[cpu_db_percent]) + AVG([cteSrc].[cpu_other_percent]) AS [avg_cpu_total_percent]
        ,CASE 
            WHEN AVG([cteSrc].[cpu_db_percent]) + AVG([cteSrc].[cpu_other_percent]) >= 80 THEN 1    --cpu usage is over 80 %
            ELSE 0
        END AS [high_cpu_usage_alert]
    FROM [cteSrc]
    WHERE DATEPART(HOUR, [cteSrc].[RunDate]) BETWEEN 7 AND 19
    GROUP BY DATEPART(DAY, [cteSrc].[RunDate]), DATEPART(HOUR, [cteSrc].[RunDate]), DATEPART(MINUTE, [cteSrc].[RunDate])
    --ORDER BY DATEPART(DAY, [cteSrc].[RunDate]), DATEPART(HOUR, [cteSrc].[RunDate]), DATEPART(MINUTE, [cteSrc].[RunDate])
)x
WHERE x.[day] = 28
ORDER BY [x].[day], [x].[hour], [x].[minute]


RETURN

SELECT MIN([cpu_idle_percent]), [RunDate]
FROM [dbo].[ActiveQuery_ResourceUse]
WHERE [cpu_idle_percent] IS NOT NULL 
GROUP BY [RunDate]
HAVING MIN([cpu_idle_percent]) < 20