Have a DB table in Vertica DB having 3 columns
1 record will be logged for each Hostname for every 5 mins for CPU Utilization %. For 1 hour, 12 samples of data will be logged into DB table for each Hostname where each row contains Hostname, CPU Util % and recorded time stamp.
Need an SQL Query for below requirement. Between any given Start Date Time and End Date Time, Need records where CPU Utilization % exceeds 80 Percent continuously for 2 or more hours ie; CPU Util % is >=80% continuously for more than >=24 samples
Thanks in advance
I tried with subqueries with group by and joining the subquery results. But no results yet.
Use a subquery to calculate the number of continuous samples where CPU utilization % is greater than or equal to 80% for each hostname. To do this the SUM() window function, with CASE expression, counts the number of samples where CPU utilization % is greater than or equal to 80% for each row and the 23 preceding rows (i.e. a total of 24 rows).
The outer query filters the results to only include rows where that calculated number of continuous samples is greater than or equal to 24 (corresponding to 2 or more hours of continuous CPU utilization % greater than or equal to 80%).
Replace :from_date_time and :to_date_time with the wanted from/to values.
SELECT *
FROM (
SELECT
hostname
, cpu_util
, TIMESTAMP
, SUM(CASE WHEN cpu_util >= 80 THEN 1 ELSE 0 END) OVER (
PARTITION BY hostname ORDER BY TIMESTAMP ROWS BETWEEN 23 PRECEDING AND CURRENT ROW
) AS continuous_samples
FROM cpu_util_table
WHERE TIMESTAMP >= :from_date_time AND TIMESTAMP < :to_date_time
) AS subquery
WHERE continuous_samples >= 24;