Embed a "at a glance" status of workspaces

[killgufo]

I could benefit from an overview of the status of a workspace, I wrote a custom SQL with the info I care the most about, it's probably trivial to modify to show more details

SELECT w.name AS Wname, s.name AS Sname,COUNT(*) AS New, COALESCE(AlreadySeen,0) AS AlreadySeen, sr.time
FROM  workspaces w
INNER JOIN findings f ON (f.workspace_id = w.id)
INNER JOIN scans s ON (f.scan_id = s.id )
INNER JOIN runs sr ON (sr.scan_id = s.id )
LEFT OUTER JOIN
(
        -- This table contains all the findings marked new which have a comment or are linked to an issue
        -- And is used only to count the findings
        SELECT w1.name AS wsn,s1.name AS scann, COUNT(*) AS AlreadySeen FROM findings f1
        INNER JOIN workspaces w1 ON (f1.workspace_id = w1.id)
        INNER JOIN scans s1 ON (f1.scan_id = s1.id )
        INNER JOIN runs sr1 ON (sr1.scan_id = s1.id)
        LEFT OUTER JOIN issues2findings i2f ON (i2f.finding_id = f1.id)
        WHERE
        -- Either there is a comment or the finding is linked to an issue
        (f1.remark IS NOT NULL OR i2f.issue_id IS NOT NULL)
        AND f1.status = 1
        AND sr1.time IN (SELECT MAX(time) FROM runs WHERE scan_id = s1.id)
        GROUP BY w1.name,s1.name
) AS alreadys
ON (w.name = alreadys.wsn AND s.name = alreadys.scann)
WHERE  f.status = 1
AND sr.time IN (SELECT MAX(time) FROM runs WHERE scan_id = s.id)
GROUP BY w.name,s.name

UNION

-- Workspaces without new findings
SELECT w.name AS wsn,s.name,0 AS New,0 As AlreadySeen, sr.time
FROM  workspaces w
INNER JOIN scans s ON (w.id = s.workspace_id)
INNER JOIN runs sr ON (s.id = sr.scan_id)
WHERE
sr.time IN (SELECT MAX(time) FROM runs WHERE scan_id = s.id)
-- The scan must not contain any finding marked as new (aka results shown in the first query)
AND s.name NOT IN (
        SELECT s.name
        FROM scans s
        INNER JOIN runs sr ON (sr.scan_id = s.id)
        INNER JOIN findings f ON (f.scan_id = s.id)
        INNER JOIN workspaces w1 ON (w1.id = s.workspace_id)
        -- Only for the last run
        WHERE sr.time IN (SELECT MAX(time) FROM runs WHERE scan_id = s.id)
        -- Per workspace
        AND w1.name = w.name
        AND f.status = 1
)

ORDER BY 1,5 DESC```