Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
1 change: 1 addition & 0 deletions .gitattributes
Original file line number Diff line number Diff line change
Expand Up @@ -44,6 +44,7 @@ provisionersdk/proto/*.go linguist-generated=true
*.tfplan.dot linguist-generated=true
site/e2e/google/protobuf/timestampGenerated.ts
site/e2e/provisionerGenerated.ts linguist-generated=true
site/src/api/appFamiliesGenerated.ts linguist-generated=true
site/src/api/countriesGenerated.tsx linguist-generated=true
site/src/api/rbacresourcesGenerated.tsx linguist-generated=true
site/src/api/typesGenerated.ts linguist-generated=true
Expand Down
6 changes: 6 additions & 0 deletions Makefile
Original file line number Diff line number Diff line change
Expand Up @@ -71,6 +71,7 @@ endif
site/src/api/typesGenerated.ts \
site/e2e/provisionerGenerated.ts \
site/src/api/chatModelOptionsGenerated.json \
site/src/api/appFamiliesGenerated.ts \
site/src/api/rbacresourcesGenerated.ts \
site/src/api/countriesGenerated.ts \
site/src/theme/icons.json \
Expand Down Expand Up @@ -614,6 +615,7 @@ scripts/apidocgen/node_modules/.installed: scripts/apidocgen/package.json script

SITE_GEN_FILES := \
site/src/api/typesGenerated.ts \
site/src/api/appFamiliesGenerated.ts \
site/src/api/rbacresourcesGenerated.ts \
site/src/api/countriesGenerated.ts \
site/src/api/chatModelOptionsGenerated.json \
Expand Down Expand Up @@ -1224,6 +1226,7 @@ gen/mark-fresh:
coderd/rbac/scopes_constants_gen.go \
codersdk/apikey_scopes_gen.go \
codersdk/dbenums_gen.go \
site/src/api/appFamiliesGenerated.ts \
site/src/api/rbacresourcesGenerated.ts \
site/src/api/countriesGenerated.ts \
site/src/api/chatModelOptionsGenerated.json \
Expand Down Expand Up @@ -1434,6 +1437,9 @@ codersdk/apikey_scopes_gen.go: scripts/apikeyscopesgen/main.go coderd/rbac/scope
$(call atomic_write,_gen/bin/apikeyscopesgen)
touch "$@"

site/src/api/appFamiliesGenerated.ts: site/node_modules/.installed scripts/typegen/appfamilies.tstmpl scripts/typegen/main.go codersdk/appname.go | _gen _gen/bin/typegen
$(call atomic_write,_gen/bin/typegen appfamilies,./scripts/biome_format.sh)

# NOTE: depends on object_gen.go and scopes_constants_gen.go because
# the generator build compiles coderd/rbac which includes both.
site/src/api/rbacresourcesGenerated.ts: site/node_modules/.installed scripts/typegen/codersdk.gotmpl scripts/typegen/main.go coderd/rbac/object.go coderd/rbac/policy/policy.go \
Expand Down
6 changes: 2 additions & 4 deletions coderd/database/querier.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

49 changes: 12 additions & 37 deletions coderd/database/querier_test.go
Original file line number Diff line number Diff line change
Expand Up @@ -845,8 +845,7 @@ func TestGetTemplateInsightsByTemplate(t *testing.T) {
insertStat(40*time.Second, templateID, userID, otherWorkspaceID, 0, map[string]int64{"reconnecting_pty": 1, "vscode": 1})
insertStat(time.Minute+5*time.Second, templateID, userID, otherWorkspaceID, 1, map[string]int64{"ssh": 1, "vscode": 1})

// An app outside every family is still activity, so its user counts as
// active even though the session lands in no usage bucket.
// A session without an app name is still activity.
unattributedUserID := uuid.New()
insertStat(10*time.Second, templateID, unattributedUserID, uuid.New(), 1, map[string]int64{"unknown": 1})

Expand All @@ -857,7 +856,7 @@ func TestGetTemplateInsightsByTemplate(t *testing.T) {
insertStat(15*time.Second, sharedConnectionTemplateID, sharedConnectionUserID, uuid.New(), 0, map[string]int64{"vscode": 1})
insertStat(30*time.Second, sharedConnectionTemplateID, sharedConnectionUserID, uuid.New(), 1, map[string]int64{"unknown": 1})

// Apps of one family open in the same minute count that minute once.
// Each app counts its own minutes, even when apps overlap.
overlapTemplateID := uuid.New()
overlapUserID := uuid.New()
insertStat(5*time.Second, overlapTemplateID, overlapUserID, uuid.New(), 1, map[string]int64{"vscode": 1, "cursor": 1, "zed": 1, "ssh": 1})
Expand All @@ -872,18 +871,16 @@ func TestGetTemplateInsightsByTemplate(t *testing.T) {
}
require.Len(t, byTemplate, 3)
require.EqualValues(t, 2, byTemplate[templateID].ActiveUsers)
require.Equal(t, database.StringMapOfInt{"vscode": 120, "jetbrains": 60, "reconnecting_pty": 60, "ssh": 120, "unknown": 60}, byTemplate[templateID].SessionFamilyUsageSeconds)
require.Equal(t, database.StringMapOfInt{"vscode": 120, "jetbrains": 60, "ssh": 120, "unknown": 60, "reconnecting_pty": 60}, byTemplate[templateID].SessionAppUsageSeconds)
require.EqualValues(t, 1, byTemplate[sharedConnectionTemplateID].ActiveUsers)
require.Equal(t, database.StringMapOfInt{"vscode": 60, "unknown": 60}, byTemplate[sharedConnectionTemplateID].SessionFamilyUsageSeconds)
require.Equal(t, database.StringMapOfInt{"vscode": 60, "unknown": 60}, byTemplate[sharedConnectionTemplateID].SessionAppUsageSeconds)
require.EqualValues(t, 1, byTemplate[overlapTemplateID].ActiveUsers)
require.Equal(t, database.StringMapOfInt{"vscode": 120, "ssh": 60, "port_forwarding": 60, "unknown": 60}, byTemplate[overlapTemplateID].SessionFamilyUsageSeconds)
require.Equal(t, database.StringMapOfInt{"vscode": 60, "cursor": 120, "zed": 60, "ssh": 60, "some_new_ide": 60, "overflow": 60, "port_forwarding": 60}, byTemplate[overlapTemplateID].SessionAppUsageSeconds)
}

func templateInsightsByTemplateParams(t *testing.T, startTime, endTime time.Time) database.GetTemplateInsightsByTemplateParams {
t.Helper()
appFamilies, err := json.Marshal(codersdk.SessionCountAppFamilies())
require.NoError(t, err)
return database.GetTemplateInsightsByTemplateParams{StartTime: startTime, EndTime: endTime, AppFamilies: appFamilies}
return database.GetTemplateInsightsByTemplateParams{StartTime: startTime, EndTime: endTime}
}

func TestGetWorkspaceAgentUsageStats(t *testing.T) {
Expand Down Expand Up @@ -19638,25 +19635,19 @@ func TestSessionCountsAttributeByFamily(t *testing.T) {
db := database.New(sqlDB)
ctx := context.Background()

cursorTemplate := uuid.New()
zedTemplate := uuid.New()
unknownTemplate := uuid.New()
for _, tc := range []struct {
templateID uuid.UUID
counts map[string]int64
}{
{cursorTemplate, map[string]int64{"cursor": 2}},
{zedTemplate, map[string]int64{"zed": 1}},
{unknownTemplate, map[string]int64{"some_new_ide": 5}},
for _, counts := range []map[string]int64{
{"cursor": 2},
{"zed": 1},
{"some_new_ide": 5},
} {
dbgen.WorkspaceAgentStat(t, db, database.WorkspaceAgentStat{
TemplateID: tc.templateID,
TemplateID: uuid.New(),
UserID: uuid.New(),
AgentID: uuid.New(),
ConnectionCount: 1,
ConnectionMedianLatencyMS: 1,
Usage: true,
SessionCounts: dbgen.SessionCounts(t, tc.counts),
SessionCounts: dbgen.SessionCounts(t, counts),
})
}

Expand All @@ -19667,22 +19658,6 @@ func TestSessionCountsAttributeByFamily(t *testing.T) {
require.Equal(t, int64(1), sessionFamilyCounts(t, stats.SessionCounts)["ssh"])
require.Zero(t, sessionFamilyCounts(t, stats.SessionCounts)["jetbrains"])
require.Zero(t, sessionFamilyCounts(t, stats.SessionCounts)["reconnecting_pty"])

insights, err := db.GetTemplateInsightsByTemplate(ctx, templateInsightsByTemplateParams(t, dbtime.Now().Add(-time.Hour), dbtime.Now().Add(time.Hour)))
require.NoError(t, err)

byTemplate := make(map[uuid.UUID]database.GetTemplateInsightsByTemplateRow, len(insights))
for _, row := range insights {
byTemplate[row.TemplateID] = row
}
require.Equal(t, database.StringMapOfInt{"vscode": 60}, byTemplate[cursorTemplate].SessionFamilyUsageSeconds)
require.Equal(t, database.StringMapOfInt{"ssh": 60}, byTemplate[zedTemplate].SessionFamilyUsageSeconds)

// An app with no family is still activity, so the user is not counted idle.
unknown, ok := byTemplate[unknownTemplate]
require.True(t, ok, "a session with no family must still appear as usage")
require.Equal(t, int64(1), unknown.ActiveUsers)
require.Equal(t, database.StringMapOfInt{"unknown": 60}, unknown.SessionFamilyUsageSeconds)
}

// The rollup stores the app name the agent reported, whether or not the
Expand Down
45 changes: 21 additions & 24 deletions coderd/database/queries.sql.go

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

30 changes: 14 additions & 16 deletions coderd/database/queries/insights.sql
Original file line number Diff line number Diff line change
Expand Up @@ -271,10 +271,8 @@ FROM
-- GetTemplateInsightsByTemplate is used for Prometheus metrics. Keep
-- in sync with GetTemplateInsights and UpsertTemplateUsageStats.
--
-- Session usage is reported per app family. It reads the raw agent stats
-- because the rollup stores minutes per app, which can't show whether two
-- apps of one family were open in the same minute. @app_families maps each
-- app name to its family; names missing from the map count as unknown.
-- Session usage is per app name. It reads raw agent stats because rollup
-- buckets are coarser than the metrics window.
WITH
connected AS (
-- NOTE(mafredri): connection_count covers one report interval, while
Expand All @@ -297,14 +295,14 @@ WITH
BOOL_OR(connection_count > 0)
),
insights AS (
-- A minute counts once per family, however many of its apps were
-- A minute counts once per app, however many of its sessions were
-- open, which COUNT(DISTINCT) does in the grouping. Deduplicating the
-- expanded rows first instead spills to disk once a deployment has a
-- few thousand agents.
SELECT
was.template_id,
was.user_id,
COALESCE(@app_families::jsonb ->> app_name, 'unknown') AS family,
app_name,
COUNT(DISTINCT date_trunc('minute', was.created_at)) AS usage_mins
FROM
workspace_agent_stats AS was
Expand All @@ -320,22 +318,22 @@ WITH
AND was.created_at < @end_time::timestamptz
AND was.session_counts <> '{}'::jsonb
GROUP BY
was.template_id, was.user_id, family
was.template_id, was.user_id, app_name
),
app_family_usage AS (
app_usage AS (
SELECT
template_id,
jsonb_object_agg(family, usage_seconds) AS session_family_usage_seconds
jsonb_object_agg(app_name, usage_seconds) AS session_app_usage_seconds
FROM (
SELECT
template_id,
family,
app_name,
(SUM(usage_mins) * 60)::bigint AS usage_seconds
FROM
insights
GROUP BY
template_id, family
) AS family_seconds
template_id, app_name
) AS app_seconds
GROUP BY
template_id
),
Expand All @@ -352,15 +350,15 @@ WITH
SELECT
active_users.template_id,
active_users.active_users,
app_family_usage.session_family_usage_seconds
app_usage.session_app_usage_seconds
FROM
active_users
JOIN
-- Every counted template has at least one family; both sides come from
-- Every counted template has at least one app; both sides come from
-- insights.
app_family_usage
app_usage
ON
app_family_usage.template_id = active_users.template_id;
app_usage.template_id = active_users.template_id;

-- name: GetTemplateAppInsights :many
-- GetTemplateAppInsights returns the aggregate usage of each app in a given
Expand Down
2 changes: 1 addition & 1 deletion coderd/database/session_usage_test.go
Original file line number Diff line number Diff line change
Expand Up @@ -165,7 +165,7 @@ func TestSessionUsageRollup(t *testing.T) {
require.NoError(t, err)
require.Empty(t, sessionUsageMins(ctx, t, sqlDB, start, user, template))

// Live insights count distinct minutes per family, with no 30-minute cap.
// Live insights count distinct minutes per app, with no 30-minute cap.
live, err := db.GetTemplateInsightsByTemplate(ctx, templateInsightsByTemplateParams(t, start.Add(30*time.Second), start.Add(30*time.Minute)))
require.NoError(t, err)
require.Len(t, live, 0, "no connected report occurs in this partial request window")
Expand Down
2 changes: 1 addition & 1 deletion coderd/database/sqlc.yaml
Original file line number Diff line number Diff line change
Expand Up @@ -130,7 +130,7 @@ sql:
- column: "template_usage_stats.app_usage_mins"
go_type:
type: "StringMapOfInt"
- column: "app_family_usage.session_family_usage_seconds"
- column: "app_usage.session_app_usage_seconds"
go_type:
type: "StringMapOfInt"
- column: "tasks_with_status.workspace_user_acl"
Expand Down
Loading
Loading