How to query the database for build plans that have not been run
Purpose
For auditing purposes, we may want to find builds that have not run for a given period of time. The solution below will present some queries to retrieve this data from the database.
Solutions
Builds which have not been run at all
select b.FULL_KEY
from BUILD b left join BUILDRESULTSUMMARY brs
on (b.FULL_KEY = brs.BUILD_KEY)
where brs.BUILD_KEY is null
and b.BUILD_TYPE in ('CHAIN', 'CHAIN_BRANCH') ;This query should work for all database types.
Builds which have not been run for a given period
In the queries that follow, please replace <period> with values of the form number unit, for example: 6 month.
Postgres
SELECT b.full_key,
Max(brs.build_date) AS last_build
FROM build b
LEFT JOIN buildresultsummary brs
ON (
full_key = build_key)
WHERE brs.build_key IS NOT NULL
AND b.build_type IN ('CHAIN',
'CHAIN_BRANCH')
AND brs.build_completed_date < Now() - interval '1day'
GROUP BY full_key;
MySQL
SELECT b.full_key,
Max(brs.build_date) AS last_build
FROM build b
LEFT JOIN buildresultsummary brs
ON (
full_key = build_key)
WHERE brs.build_key IS NOT NULL
AND b.build_type IN ('CHAIN',
'CHAIN_BRANCH')
AND build_date < Now() - interval <period>
GROUP BY full_key;
Please note the very slight difference between the queries above.
For the following queries, please replace:
<interval>with "day", "week", "month", "year"<num>with the number of intervals<number of days>number of days to check (example:90will search for builds that last ran 90 days ago)
MS SQL Server
SELECT a.FULL_KEY, a.LAST_BUILD
FROM
(SELECT b.FULL_KEY, max(brs.BUILD_DATE) AS LAST_BUILD
FROM BUILD b
LEFT JOIN BUILDRESULTSUMMARY brs ON (full_key = build_key)
WHERE brs.BUILD_KEY IS NOT NULL
AND b.BUILD_TYPE IN ('CHAIN', 'CHAIN_BRANCH')
GROUP BY full_key) a
WHERE a.LAST_BUILD < dateadd(<interval>, GETDATE(), -<num>);
Oracle
select b.FULL_KEY
, b.BUILD_TYPE
, brs.BUILD_COMPLETED_DATE
from BUILD b
join BUILDRESULTSUMMARY brs on b.FULL_KEY = brs.BUILD_KEY
and b.BUILD_ID in (select b.BUILD_ID
from BUILDRESULTSUMMARY brs2
join BUILD b on brs2.BUILD_KEY = b.FULL_KEY
where b.BUILD_TYPE in ('CHAIN','CHAIN_BRANCH')
and brs2.BUILD_COMPLETED_DATE <= TRUNC(SYSDATE) - <number of days>)
order by b.FULL_KEY;Please note that the Oracle and SQL Server queries have not been tested.
In the below query, you can identify plans (including plan branches) that have not been build for over 180 days
Postgres
SELECT b.full_key,
Max(brs.build_date) AS last_build
FROM build b
LEFT JOIN buildresultsummary brs
ON (
full_key = build_key)
WHERE brs.build_key IS NOT NULL
AND b.build_type IN ('CHAIN',
'CHAIN_BRANCH')
AND brs.build_completed_date < Now() - interval '1day'
GROUP BY full_key;
Last modified on Mar 14, 2023
Powered by Confluence and Scroll Viewport.