Skip to content

Commit e15161f

Browse files
mafredrimafredri
committed
rewrite query
1 parent e4f1de7 commit e15161f

File tree

5 files changed

+130
-91
lines changed

5 files changed

+130
-91
lines changed

‎coderd/database/dbmem/dbmem.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3278,7 +3278,7 @@ func (q *FakeQuerier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg
32783278
for _, usageKey := range usageKeys {
32793279
r := database.GetTemplateAppInsightsByTemplateRow{
32803280
TemplateID: usageKey.TemplateID,
3281-
DisplayName: sql.NullString{String: usageKey.DisplayName, Valid: true},
3281+
DisplayName: usageKey.DisplayName,
32823282
SlugOrPort: usageKey.Slug,
32833283
}
32843284
for _, mUserUsage := range usageByTemplateAppUser[usageKey] {

‎coderd/database/querier.go

Lines changed: 2 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

Lines changed: 66 additions & 48 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

Lines changed: 60 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -293,51 +293,70 @@ GROUP BY
293293
t.template_ids, ai.app_name, ai.display_name, ai.icon, ai.is_app;
294294

295295
-- name: GetTemplateAppInsightsByTemplate :many
296-
WITH app_stats_by_user_and_agent AS (
297-
SELECT
298-
s.start_time,
299-
60 as seconds,
300-
w.template_id,
301-
was.user_id,
302-
was.agent_id,
303-
was.slug_or_port,
304-
wa.display_name,
305-
(wa.slug IS NOT NULL)::boolean AS is_app
306-
FROM workspace_app_stats was
307-
JOIN workspaces w ON (
308-
w.id = was.workspace_id
309-
)
310-
-- We do a left join here because we want to include user IDs that have used
311-
-- e.g. ports when counting active users.
312-
LEFT JOIN workspace_apps wa ON (
313-
wa.agent_id = was.agent_id
314-
AND wa.slug = was.slug_or_port
296+
-- GetTemplateAppInsightsByTemplate is used for Prometheus metrics. Keep
297+
-- in sync with GetTemplateAppInsights and UpsertTemplateUsageStats.
298+
WITH
299+
-- This CTE is used to explode app usage into minute buckets, then
300+
-- flatten the users app usage within the template so that usage in
301+
-- multiple workspaces under one template is only counted once for
302+
-- every minute.
303+
app_insights AS (
304+
SELECT
305+
w.template_id,
306+
was.user_id,
307+
-- Both app stats and agent stats track web terminal usage, but
308+
-- by different means. The app stats value should be more
309+
-- accurate so we don't want to discard it just yet.
310+
CASE
311+
WHEN was.access_method = 'terminal'
312+
THEN '[terminal]' -- Unique name, app names can't contain brackets.
313+
ELSE was.slug_or_port
314+
END::text AS app_name,
315+
COALESCE(wa.display_name, '') AS display_name,
316+
(wa.slug IS NOT NULL)::boolean AS is_app,
317+
COUNT(DISTINCT s.minute_bucket) AS app_minutes
318+
FROM
319+
workspace_app_stats AS was
320+
JOIN
321+
workspaces AS w
322+
ON
323+
w.id = was.workspace_id
324+
-- We do a left join here because we want to include user IDs that have used
325+
-- e.g. ports when counting active users.
326+
LEFT JOIN
327+
workspace_apps wa
328+
ON
329+
wa.agent_id = was.agent_id
330+
AND wa.slug = was.slug_or_port
331+
-- Generate a series of minute buckets for each session for computing the
332+
-- mintes/bucket.
333+
CROSS JOIN
334+
generate_series(
335+
date_trunc('minute', was.session_started_at),
336+
-- Subtract 1 microsecond to avoid creating an extra series.
337+
date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
338+
'1 minute'::interval
339+
) AS s(minute_bucket)
340+
WHERE
341+
s.minute_bucket >= @start_time::timestamptz
342+
AND s.minute_bucket < @end_time::timestamptz
343+
GROUP BY
344+
w.template_id, was.user_id, was.access_method, was.slug_or_port, wa.display_name, wa.slug
315345
)
316-
-- This table contains both 1 minute entries and >1 minute entries,
317-
-- to calculate this with our uniqueness constraints, we generate series
318-
-- for the longer intervals.
319-
CROSS JOIN LATERAL generate_series(
320-
date_trunc('minute', was.session_started_at),
321-
-- Subtract 1 microsecond to avoid creating an extra series.
322-
date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
323-
'1 minute'::interval
324-
) s(start_time)
325-
WHERE
326-
s.start_time >= @start_time::timestamptz
327-
-- Subtract one minute because the series only contains the start time.
328-
AND s.start_time < (@end_time::timestamptz) - '1 minute'::interval
329-
GROUP BY s.start_time, w.template_id, was.user_id, was.agent_id, was.slug_or_port, wa.display_name, wa.slug
330-
)
331346

332347
SELECT
333348
template_id,
334-
display_name,
335-
slug_or_port,
336-
COALESCE(COUNT(DISTINCT user_id))::bigint AS active_users,
337-
SUM(seconds) AS usage_seconds
338-
FROM app_stats_by_user_and_agent
339-
WHERE is_app IS TRUE
340-
GROUP BY template_id, display_name, slug_or_port;
349+
app_name AS slug_or_port,
350+
display_name AS display_name,
351+
COUNT(DISTINCT user_id)::bigint AS active_users,
352+
(SUM(app_minutes) * 60)::bigint AS usage_seconds
353+
FROM
354+
app_insights
355+
WHERE
356+
is_app IS TRUE
357+
GROUP BY
358+
template_id, slug_or_port, display_name;
359+
341360

342361
-- name: GetTemplateInsightsByInterval :many
343362
-- GetTemplateInsightsByInterval returns all intervals between start and end

‎coderd/prometheusmetrics/insights/metricscollector.go

Lines changed: 1 addition & 1 deletion
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)