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