--- title: "Track test results over time" description: "Send every result to a database table and the past becomes a query — the last run, a pass rate per day, the tests that fail most, how long each takes, and an alert the moment a test fails" url: "https://docs.justcat.it/how-to-guides/organize-and-run-tests/track-test-results-over-time/" --- # Track test results over time ## The pattern A [database output](https://docs.justcat.it/reference/outputs/database-outputs/ "Database outputs") inserts one row per test into a table of yours, as each test finishes — the result, the timing, the message, and the whole definition that produced it (suite, name, data sources, queries, expectation). Every row of one run carries the same `ExecutionGuid`. Keep the table, and every question about the past is a `SELECT`. The output points at a data source the project already has — the warehouse itself is the usual choice, a reporting database works just as well: **Properties** **Data sources** Name : DWH Provider : SqlServer@2 Connection string : %DWH_CONNECTION_STRING% **Output** Database : DWH Table : dbo.CatTestResult Format : xlsx File : LatestTestResults.xlsx **YAML** ```yaml Data sources: - Name: DWH Provider: SqlServer@2 Connection string: "%DWH_CONNECTION_STRING%" Output: - Database: DWH Table: dbo.CatTestResult - Format: xlsx File: LatestTestResults.xlsx ``` The second output is optional: the table is for history and reports, the Excel file is for the person who asks "what happened last night" and wants something to open. Every CAT tool writes both — CAT Studio while you develop the tests, CAT CLI or the PowerShell module from a pipeline — so the history starts on the first run, not on the first scheduled one. Database outputs need the Team or Enterprise plan; the Starter and Professional plans write Excel only. ## Create the table yourself CAT creates the table when it may, but a CAT account should not be allowed to. Create it from the script on the database's page — [SQL Server](https://docs.justcat.it/reference/outputs/sqlserver/ "SQL Server"), [PostgreSQL](https://docs.justcat.it/reference/outputs/postgres/ "PostgreSQL"), [Oracle](https://docs.justcat.it/reference/outputs/oracle/ "Oracle") — and give CAT `INSERT` on it, nothing more. Columns are matched by name, so any subset of the script's columns works, and your own columns are fine when they are nullable or have a default. For reports, keep at least `ExecutionGuid`, `Project`, `FullName`, `TestResult`, `StartedOn`, `FinishedOn` and `RawMessage`; the rest is there when you want to see the query that failed without opening the project. The full list of what a row carries is on [Properties](https://docs.justcat.it/reference/outputs/properties/ "Properties"). Several projects write to one table without a conflict — `Project` tells them apart, and every query below filters on it. Several environments are best kept as one table per environment (`Table: dev.CatTestResult`, `Table: prod.CatTestResult`), with a view that unions them under an `Environment` literal for the reports. ## The reports Every query is here for each database, against the table from that database's page: `dbo.CatTestResult` on SQL Server, `public.cat_test_result` on PostgreSQL, `AERO.CAT_TEST_RESULT` on Oracle — change the schema and the project name, nothing else. The text between the queries names the columns as SQL Server spells them; `StartedOn` is `started_on` on PostgreSQL and `STARTED_ON` on Oracle. Each query is a plain `SELECT`; a table or a view like these is what Power BI, Grafana or Excel reads, and the dashboard is one visual per query. ### The last run What the front page of a dashboard shows: every test of the newest run, the ones that did not pass first. **SQL Server** ```sql WITH LastRun AS ( SELECT TOP 1 ExecutionGuid FROM dbo.CatTestResult WHERE Project = 'Flights' GROUP BY ExecutionGuid ORDER BY MIN(StartedOn) DESC ) SELECT r.FullName, r.TestResult, r.FinishedOn, r.RawMessage FROM dbo.CatTestResult r JOIN LastRun l ON l.ExecutionGuid = r.ExecutionGuid ORDER BY CASE r.TestResult WHEN 'Passed' THEN 1 ELSE 0 END, r.FullName; ``` **PostgreSQL** ```sql WITH last_run AS ( SELECT execution_guid FROM public.cat_test_result WHERE project = 'Flights' GROUP BY execution_guid ORDER BY MIN(started_on) DESC LIMIT 1 ) SELECT r.full_name, r.test_result, r.finished_on, r.raw_message FROM public.cat_test_result r JOIN last_run l ON l.execution_guid = r.execution_guid ORDER BY CASE r.test_result WHEN 'Passed' THEN 1 ELSE 0 END, r.full_name; ``` **Oracle** ```sql WITH LAST_RUN AS ( SELECT EXECUTION_GUID FROM AERO.CAT_TEST_RESULT WHERE PROJECT = 'Flights' GROUP BY EXECUTION_GUID ORDER BY MIN(STARTED_ON) DESC FETCH FIRST 1 ROWS ONLY ) SELECT r.FULL_NAME, r.TEST_RESULT, r.FINISHED_ON, r.RAW_MESSAGE FROM AERO.CAT_TEST_RESULT r JOIN LAST_RUN l ON l.EXECUTION_GUID = r.EXECUTION_GUID ORDER BY CASE r.TEST_RESULT WHEN 'Passed' THEN 1 ELSE 0 END, r.FULL_NAME; ``` The newest run is the one whose first test started last — not `MAX(FinishedOn)`, which a long test of an older run can win. `RawMessage` is the verdict and the sample rows, short enough for a grid; `Message` has the description and the queries under it. ### Pass rate per day The trend line. One row per day, passed over everything that ran: **SQL Server** ```sql SELECT CAST(StartedOn AS DATE) AS Day, COUNT(*) AS Tests, SUM(CASE WHEN TestResult = 'Passed' THEN 1 ELSE 0 END) AS Passed, CAST(100.0 * SUM(CASE WHEN TestResult = 'Passed' THEN 1 ELSE 0 END) / COUNT(*) AS DECIMAL(5, 1)) AS PassRate FROM dbo.CatTestResult WHERE Project = 'Flights' AND StartedOn >= DATEADD(DAY, -90, SYSUTCDATETIME()) GROUP BY CAST(StartedOn AS DATE) ORDER BY Day; ``` **PostgreSQL** ```sql SELECT started_on::date AS day, COUNT(*) AS tests, COUNT(*) FILTER (WHERE test_result = 'Passed') AS passed, ROUND(100.0 * COUNT(*) FILTER (WHERE test_result = 'Passed') / COUNT(*), 1) AS pass_rate FROM public.cat_test_result WHERE project = 'Flights' AND started_on >= (now() AT TIME ZONE 'utc') - INTERVAL '90 days' GROUP BY started_on::date ORDER BY day; ``` **Oracle** ```sql SELECT TRUNC(STARTED_ON) AS RUN_DAY, COUNT(*) AS TESTS, SUM(CASE WHEN TEST_RESULT = 'Passed' THEN 1 ELSE 0 END) AS PASSED, ROUND(100 * SUM(CASE WHEN TEST_RESULT = 'Passed' THEN 1 ELSE 0 END) / COUNT(*), 1) AS PASS_RATE FROM AERO.CAT_TEST_RESULT WHERE PROJECT = 'Flights' AND STARTED_ON >= SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '90' DAY GROUP BY TRUNC(STARTED_ON) ORDER BY RUN_DAY; ``` A nightly schedule makes this one run per day; with several runs a day, group by `ExecutionGuid` and plot `MIN(StartedOn)` instead. `StartedOn` is UTC — a run that starts at 23:30 local may land on the next day. ### The tests that fail most Where the time goes. Every test that did not pass at least once in the last month, the worst first: **SQL Server** ```sql SELECT FullName, SUM(CASE WHEN TestResult = 'Failed' THEN 1 ELSE 0 END) AS Failed, SUM(CASE WHEN TestResult = 'Error' THEN 1 ELSE 0 END) AS Errors, COUNT(*) AS Runs, MAX(CASE WHEN TestResult <> 'Passed' THEN StartedOn END) AS LastProblem FROM dbo.CatTestResult WHERE Project = 'Flights' AND StartedOn >= DATEADD(DAY, -30, SYSUTCDATETIME()) GROUP BY FullName HAVING SUM(CASE WHEN TestResult <> 'Passed' THEN 1 ELSE 0 END) > 0 ORDER BY SUM(CASE WHEN TestResult IN ('Failed', 'Error') THEN 1 ELSE 0 END) DESC, FullName; ``` **PostgreSQL** ```sql SELECT full_name, COUNT(*) FILTER (WHERE test_result = 'Failed') AS failed, COUNT(*) FILTER (WHERE test_result = 'Error') AS errors, COUNT(*) AS runs, MAX(started_on) FILTER (WHERE test_result <> 'Passed') AS last_problem FROM public.cat_test_result WHERE project = 'Flights' AND started_on >= (now() AT TIME ZONE 'utc') - INTERVAL '30 days' GROUP BY full_name HAVING COUNT(*) FILTER (WHERE test_result <> 'Passed') > 0 ORDER BY COUNT(*) FILTER (WHERE test_result IN ('Failed', 'Error')) DESC, full_name; ``` **Oracle** ```sql SELECT FULL_NAME, SUM(CASE WHEN TEST_RESULT = 'Failed' THEN 1 ELSE 0 END) AS FAILED, SUM(CASE WHEN TEST_RESULT = 'Error' THEN 1 ELSE 0 END) AS ERRORS, COUNT(*) AS RUNS, MAX(CASE WHEN TEST_RESULT <> 'Passed' THEN STARTED_ON END) AS LAST_PROBLEM FROM AERO.CAT_TEST_RESULT WHERE PROJECT = 'Flights' AND STARTED_ON >= SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '30' DAY GROUP BY FULL_NAME HAVING SUM(CASE WHEN TEST_RESULT <> 'Passed' THEN 1 ELSE 0 END) > 0 ORDER BY SUM(CASE WHEN TEST_RESULT IN ('Failed', 'Error') THEN 1 ELSE 0 END) DESC, FULL_NAME; ``` `Failed` and `Error` are different problems — the data broke the rule, or the test could not run — so keep them in two columns; a test with thirty errors needs a connection fixed, not a data owner. See [Results](https://docs.justcat.it/reference/tests/results/ "Results"). ### How long a test takes The one that says which test deserves a `Timeout` or a cheaper query: **SQL Server** ```sql SELECT FullName, AVG(DATEDIFF(MILLISECOND, StartedOn, FinishedOn)) / 1000.0 AS AvgSeconds, MAX(DATEDIFF(MILLISECOND, StartedOn, FinishedOn)) / 1000.0 AS MaxSeconds, COUNT(*) AS Runs FROM dbo.CatTestResult WHERE Project = 'Flights' AND StartedOn >= DATEADD(DAY, -30, SYSUTCDATETIME()) GROUP BY FullName ORDER BY AvgSeconds DESC; ``` **PostgreSQL** ```sql SELECT full_name, ROUND(AVG(EXTRACT(EPOCH FROM finished_on - started_on)), 3) AS avg_seconds, ROUND(MAX(EXTRACT(EPOCH FROM finished_on - started_on)), 3) AS max_seconds, COUNT(*) AS runs FROM public.cat_test_result WHERE project = 'Flights' AND started_on >= (now() AT TIME ZONE 'utc') - INTERVAL '30 days' GROUP BY full_name ORDER BY avg_seconds DESC; ``` **Oracle** ```sql SELECT FULL_NAME, ROUND(AVG(SECONDS), 3) AS AVG_SECONDS, ROUND(MAX(SECONDS), 3) AS MAX_SECONDS, COUNT(*) AS RUNS FROM ( SELECT FULL_NAME, EXTRACT(DAY FROM FINISHED_ON - STARTED_ON) * 86400 + EXTRACT(HOUR FROM FINISHED_ON - STARTED_ON) * 3600 + EXTRACT(MINUTE FROM FINISHED_ON - STARTED_ON) * 60 + EXTRACT(SECOND FROM FINISHED_ON - STARTED_ON) AS SECONDS FROM AERO.CAT_TEST_RESULT WHERE PROJECT = 'Flights' AND STARTED_ON >= SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '30' DAY ) GROUP BY FULL_NAME ORDER BY AVG_SECONDS DESC; ``` A test whose maximum is far above its average is one whose query waits on something — a lock, a load still running — and that is a scheduling problem before it is a test problem. Running the slow ones in their own thread is on [Run tests in parallel](https://docs.justcat.it/how-to-guides/organize-and-run-tests/run-tests-in-parallel/ "Run tests in parallel"). ## An alert when a test fails A pipeline already fails the build on a failed test — the [pipeline guides](https://docs.justcat.it/how-to-guides/pipelines-and-schedulers/ "Pipelines and schedulers") show how. The history is for what the pipeline does not do: tell a person who is not watching the pipeline. The simplest alert needs no scheduler: a stored procedure as the output instead of a table. CAT calls it once per test, with the same values the table would get; the procedure inserts the row, and when the result is not `Passed` it does whatever the database can do. CAT reads the procedure's parameters before the run and passes those it recognizes, matched by name, so a procedure may take fewer parameters than the one on its page — as long as what it inserts satisfies the table. Every block below catches its own errors, and that part is not optional. An error raised by the procedure stops the tests behind it (see [the limits](#the-limits)), so a mail that cannot go out — Database Mail off, no network ACL, the mail server down — must fail quietly and leave the row saved. **SQL Server** SQL Server sends the mail itself, with Database Mail. Take `dbo.SaveCatTestResults` from the [SQL Server](https://docs.justcat.it/reference/outputs/sqlserver/#stored-procedure "SQL Server") page as it is and add one block after its `INSERT`: ```sql IF @TestResult <> 'Passed' BEGIN TRY DECLARE @Subject NVARCHAR(255) = CONCAT('CAT ', @TestResult, ': ', @FullName); EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Alerts', @recipients = 'data-team@example.com', @subject = @Subject, @body = @RawMessage; END TRY BEGIN CATCH END CATCH ``` The project names the procedure instead of the table — `Procedure: dbo.SaveCatTestResults` — and the account needs `EXECUTE` on it. **PostgreSQL** PostgreSQL cannot send mail, so the procedure hands the failure on: a row in an alerts table, which a job (cron, a pipeline, an Azure Function) reads and mails, then sets `sent_on`. The table: ```sql CREATE TABLE public.cat_alert ( alert_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, full_name VARCHAR(700) NOT NULL, test_result VARCHAR(30) NOT NULL, raw_message TEXT NULL, created_on TIMESTAMP NOT NULL DEFAULT (now() at time zone 'utc'), sent_on TIMESTAMP NULL ); ``` Take `public.save_cat_test_result` from the [PostgreSQL](https://docs.justcat.it/reference/outputs/postgres/ "PostgreSQL") page as it is and add one block after its `insert`: ```sql if c_test_result <> 'Passed' then begin insert into public.cat_alert (full_name, test_result, raw_message) values (c_full_name, c_test_result, c_raw_message); exception when others then null; end; end if; ``` The project names the procedure instead of the table — `Procedure: public.save_cat_test_result` — and the account needs `EXECUTE` on it. **Oracle** Oracle sends the mail itself with `UTL_MAIL`, once a DBA has installed it, set `SMTP_OUT_SERVER` and opened a network ACL to the mail server for the package owner. Take the `CAT_TEST_MGMT` package from the [Oracle](https://docs.justcat.it/reference/outputs/oracle/ "Oracle") page as it is and add one block after the `INSERT` in its body: ```sql IF C_TEST_RESULT <> 'Passed' THEN BEGIN UTL_MAIL.SEND( sender => 'cat@example.com', recipients => 'data-team@example.com', subject => 'CAT ' || C_TEST_RESULT || ': ' || C_FULL_NAME, message => DBMS_LOB.SUBSTR(C_RAW_MESSAGE, 4000, 1)); EXCEPTION WHEN OTHERS THEN NULL; END; END IF; ``` `RAW_MESSAGE` is an `NCLOB`; the mail gets its first 4 000 characters. The project names the package instead of the table — `Procedure: AERO.CAT_TEST_MGMT` — and the account needs `EXECUTE` on it. It runs per test. A run with two hundred failures sends two hundred mails, so decide what an alert is before you write one: a mail only when the previous row of the same `FullName` was `Passed` (a new failure, not a known one), a mail for one suite only (`@Suite = 'Smoke tests'`; `c_suite` on PostgreSQL, `C_SUITE` on Oracle), or no mail at all from the procedure — an insert into an alerts table that a job reads every morning and sends as one digest. The procedure is a hook; the policy is yours. ## The limits * **The table grows.** One row per test per run: five hundred tests every night are about 180 000 rows a year, and a failed row holds its sample of offending rows in `RawMessage` and `Message`. A retention delete on `StartedOn` once a month is the usual answer; keep the aggregates (the daily pass rate) in a small table of their own if the trend should outlive the rows. * **The full name is the identity of a test across runs.** Rename a test, move it to another suite or change its `Order`, and its history starts again under the new name. A stable `Key` on the test is an alternative identity, and it is on the row. * **Columns are matched by name.** Rename a column in the table and CAT stops writing it, without a word; a column CAT does not know must be nullable or have a default, or the first insert fails and stops its thread (next point). The table is read once before the first test, so a change made during a run is seen on the next one. * **An error in the output stops the tests behind it.** When the insert or the procedure fails, the thread that ran the test stops: its remaining tests do not run, and the run ends without a summary (CAT CLI exits with 1). A column CAT cannot fill, a missing permission or an alert that raises an error all do it — which is why the alert blocks above catch their own. * **Times are UTC.** `StartedOn` and `FinishedOn` are UTC in every database; convert in the report, not in the table. * **A killed run has rows.** Tests that finished before the run was stopped are in the table — a run with fewer rows than tests is one that did not finish, which the last-run query makes visible when it is compared with the previous run's count. ## Related * [Database outputs](https://docs.justcat.it/reference/outputs/database-outputs/ "Database outputs") — the mechanics every database output shares. * [SQL Server](https://docs.justcat.it/reference/outputs/sqlserver/ "SQL Server") · [PostgreSQL](https://docs.justcat.it/reference/outputs/postgres/ "PostgreSQL") · [Oracle](https://docs.justcat.it/reference/outputs/oracle/ "Oracle") — the table and procedure scripts. * [Properties](https://docs.justcat.it/reference/outputs/properties/ "Properties") — every field a row carries. * [Run your tests](https://docs.justcat.it/how-to-guides/organize-and-run-tests/run-your-tests/ "Run your tests") · [Pipelines and schedulers](https://docs.justcat.it/how-to-guides/pipelines-and-schedulers/ "Pipelines and schedulers") — the runs that fill the table.