Skip to content

Commit 754d6c1

Browse files
mafredrimafredri
committed
feat(coderd/database): rewrite GetTemplateAppInsightsByTemplate to use template_usage_stats 
1 parent 04b5c07 commit 754d6c1

File tree

4 files changed

+101
-95
lines changed

4 files changed

+101
-95
lines changed

‎coderd/database/dbmem/dbmem.go

+1-1
Original file line numberDiff line numberDiff line change
@@ -3275,7 +3275,7 @@ func (q *FakeQuerier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg
32753275
for _, usageKey := range usageKeys {
32763276
r := database.GetTemplateAppInsightsByTemplateRow{
32773277
TemplateID: usageKey.TemplateID,
3278-
DisplayName: sql.NullString{String: usageKey.DisplayName, Valid: true},
3278+
DisplayName: usageKey.DisplayName,
32793279
SlugOrPort: usageKey.Slug,
32803280
}
32813281
for _, mUserUsage := range usageByTemplateAppUser[usageKey] {

‎coderd/database/queries.sql.go

+53-50
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

+46-43
Original file line numberDiff line numberDiff line change
@@ -271,51 +271,54 @@ GROUP BY
271271
t.template_ids, ai.app_name, ai.display_name, ai.icon, ai.is_app;
272272

273273
-- name: GetTemplateAppInsightsByTemplate :many
274-
WITH app_stats_by_user_and_agent AS (
274+
SELECT
275+
tus.template_id,
276+
COUNT(DISTINCT tus.user_id) AS active_users,
277+
app_usage.key::text AS slug_or_port,
278+
COALESCE(wa.display_name, '') AS display_name,
279+
(SUM(app_usage.value::int) * 60)::bigint AS usage_seconds
280+
FROM
281+
template_usage_stats AS tus, jsonb_each(app_usage_mins) AS app_usage
282+
LEFT JOIN LATERAL (
283+
-- Fetch the latest app info for each app based on slug and template.
275284
SELECT
276-
s.start_time,
277-
60 as seconds,
278-
w.template_id,
279-
was.user_id,
280-
was.agent_id,
281-
was.slug_or_port,
282-
wa.display_name,
283-
(wa.slug IS NOT NULL)::boolean AS is_app
284-
FROM workspace_app_stats was
285-
JOIN workspaces w ON (
286-
w.id = was.workspace_id
287-
)
288-
-- We do a left join here because we want to include user IDs that have used
289-
-- e.g. ports when counting active users.
290-
LEFT JOIN workspace_apps wa ON (
291-
wa.agent_id = was.agent_id
292-
AND wa.slug = was.slug_or_port
293-
)
294-
-- This table contains both 1 minute entries and >1 minute entries,
295-
-- to calculate this with our uniqueness constraints, we generate series
296-
-- for the longer intervals.
297-
CROSS JOIN LATERAL generate_series(
298-
date_trunc('minute', was.session_started_at),
299-
-- Subtract 1 microsecond to avoid creating an extra series.
300-
date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
301-
'1 minute'::interval
302-
) s(start_time)
285+
app.display_name,
286+
app.slug
287+
FROM
288+
workspace_apps AS app
289+
JOIN
290+
workspace_agents AS agent
291+
ON
292+
agent.id = app.agent_id
293+
JOIN
294+
workspace_resources AS resource
295+
ON
296+
resource.id = agent.resource_id
297+
JOIN
298+
workspace_builds AS build
299+
ON
300+
build.job_id = resource.job_id
301+
JOIN
302+
workspaces AS workspace
303+
ON
304+
workspace.id = build.workspace_id
303305
WHERE
304-
s.start_time >= @start_time::timestamptz
305-
-- Subtract one minute because the series only contains the start time.
306-
AND s.start_time < (@end_time::timestamptz) - '1 minute'::interval
307-
GROUP BY s.start_time, w.template_id, was.user_id, was.agent_id, was.slug_or_port, wa.display_name, wa.slug
308-
)
309-
310-
SELECT
311-
template_id,
312-
display_name,
313-
slug_or_port,
314-
COALESCE(COUNT(DISTINCT user_id))::bigint AS active_users,
315-
SUM(seconds) AS usage_seconds
316-
FROM app_stats_by_user_and_agent
317-
WHERE is_app IS TRUE
318-
GROUP BY template_id, display_name, slug_or_port;
306+
-- Requires lateral join.
307+
app.slug = app_usage.key
308+
AND workspace.owner_id = tus.user_id
309+
AND workspace.template_id = tus.template_id
310+
ORDER BY
311+
app.created_at DESC
312+
LIMIT 1
313+
) wa
314+
ON
315+
true
316+
WHERE
317+
tus.start_time >= @start_time::timestamptz
318+
AND tus.end_time <= @end_time::timestamptz
319+
AND wa.slug IS NOT NULL -- Check is_app.
320+
GROUP BY
321+
tus.template_id, app_usage.key::text, wa.display_name;
319322

320323
-- name: GetTemplateInsightsByInterval :many
321324
-- GetTemplateInsightsByInterval returns all intervals between start and end

‎coderd/prometheusmetrics/insights/metricscollector.go

+1-1
Original file line numberDiff line numberDiff line change
@@ -194,7 +194,7 @@ func (mc *MetricsCollector) Collect(metricsCh chan<- prometheus.Metric) {
194194
// Custom apps
195195
for _, appRow := range data.apps {
196196
metricsCh <- prometheus.MustNewConstMetric(applicationsUsageSecondsDesc, prometheus.GaugeValue, float64(appRow.UsageSeconds), data.templateNames[appRow.TemplateID],
197-
appRow.DisplayName.String, appRow.SlugOrPort)
197+
appRow.DisplayName, appRow.SlugOrPort)
198198
}
199199

200200
// Built-in apps

0 commit comments

Comments
 (0)