Repository navigation
Expand file tree
/
Copy pathworkspaceagentstats.sql
More file actions
349 lines (342 loc) · 12.4 KB
/
Copy pathworkspaceagentstats.sql
File metadata and controls
349 lines (342 loc) · 12.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
-- name: InsertWorkspaceAgentStats :exec
INSERT INTO
workspace_agent_stats (
id,
created_at,
user_id,
workspace_id,
template_id,
agent_id,
connections_by_proto,
connection_count,
rx_packets,
rx_bytes,
tx_packets,
tx_bytes,
session_counts,
connection_median_latency_ms,
usage
)
SELECT
unnest(@id :: uuid[]) AS id,
unnest(@created_at :: timestamptz[]) AS created_at,
unnest(@user_id :: uuid[]) AS user_id,
unnest(@workspace_id :: uuid[]) AS workspace_id,
unnest(@template_id :: uuid[]) AS template_id,
unnest(@agent_id :: uuid[]) AS agent_id,
jsonb_array_elements(@connections_by_proto :: jsonb) AS connections_by_proto,
unnest(@connection_count :: bigint[]) AS connection_count,
unnest(@rx_packets :: bigint[]) AS rx_packets,
unnest(@rx_bytes :: bigint[]) AS rx_bytes,
unnest(@tx_packets :: bigint[]) AS tx_packets,
unnest(@tx_bytes :: bigint[]) AS tx_bytes,
jsonb_array_elements(@session_counts :: jsonb) AS session_counts,
unnest(@connection_median_latency_ms :: double precision[]) AS connection_median_latency_ms,
unnest(@usage :: boolean[]) AS usage;
-- name: DeleteOldWorkspaceAgentStats :exec
DELETE FROM
workspace_agent_stats
WHERE
created_at < (
SELECT
COALESCE(
-- When generating initial template usage stats, all the
-- raw agent stats are needed, after that only ~30 mins
-- from last rollup is needed. Deployment stats seem to
-- use between 15 mins and 1 hour of data. We keep a
-- little bit more (1 day) just in case.
MAX(start_time) - '1 days'::interval,
-- Fall back to ~6 months ago if there are no template
-- usage stats so that we don't delete the data before
-- it's rolled up.
NOW() - '180 days'::interval
)
FROM
template_usage_stats
)
AND created_at < (
-- Delete at most in batches of 4 hours (with this batch size, assuming
-- 1 iteration / 10 minutes, we can clear out the previous 6 months of
-- data in 7.5 days) whilst keeping the DB load low.
SELECT
COALESCE(MIN(created_at) + '4 hours'::interval, NOW())
FROM
workspace_agent_stats
);
-- name: GetDeploymentWorkspaceAgentStats :one
-- The session count sum runs in its own subquery: decomposing session_counts
-- in the FROM clause would emit one row per app name and multiply the byte and
-- latency aggregates below. Summing per app name and folding the names into
-- families in Go keeps a session reported under a new name counted.
WITH stats AS (
SELECT
agent_id,
created_at,
rx_bytes,
tx_bytes,
connection_median_latency_ms,
session_counts,
ROW_NUMBER() OVER (PARTITION BY agent_id ORDER BY created_at DESC) AS rn
FROM workspace_agent_stats
WHERE created_at > $1
)
SELECT
coalesce(SUM(rx_bytes), 0)::bigint AS workspace_rx_bytes,
coalesce(SUM(tx_bytes), 0)::bigint AS workspace_tx_bytes,
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
coalesce((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY connection_median_latency_ms) FILTER (WHERE connection_median_latency_ms > 0)), -1)::FLOAT AS workspace_connection_latency_50,
coalesce((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY connection_median_latency_ms) FILTER (WHERE connection_median_latency_ms > 0)), -1)::FLOAT AS workspace_connection_latency_95,
coalesce((
SELECT
jsonb_object_agg(app_name, app_sessions)
FROM (
SELECT
sess.app_name,
SUM(sess.sessions::bigint) AS app_sessions
FROM stats, jsonb_each_text(stats.session_counts) AS sess(app_name, sessions)
-- Only the latest row per agent holds current sessions.
WHERE stats.rn = 1
GROUP BY sess.app_name
) AS app_totals
), '{}'::jsonb)::jsonb AS session_counts
FROM stats;
-- name: GetDeploymentWorkspaceAgentUsageStats :one
WITH agent_stats AS (
SELECT
coalesce(SUM(rx_bytes), 0)::bigint AS workspace_rx_bytes,
coalesce(SUM(tx_bytes), 0)::bigint AS workspace_tx_bytes,
coalesce((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_50,
coalesce((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_95
FROM workspace_agent_stats
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
WHERE workspace_agent_stats.created_at > $1 AND connection_median_latency_ms > 0
),
latest_minutes AS (
SELECT
agent_id,
MAX(date_trunc('minute', created_at)) AS minute_bucket
FROM
workspace_agent_stats
WHERE
created_at >= $1
-- Exclude the current partial minute.
AND created_at < date_trunc('minute', now())
AND usage
GROUP BY
agent_id
),
latest_agent_stats AS (
-- Aggregating the per app name sums separately keeps the byte and latency
-- aggregates in agent_stats free of the decomposed rows.
SELECT
coalesce(jsonb_object_agg(app_name, app_sessions), '{}'::jsonb)::jsonb AS session_counts
FROM (
SELECT
sess.app_name,
SUM(sess.sessions::bigint) AS app_sessions
FROM
latest_minutes
JOIN
workspace_agent_stats AS stats
ON
stats.agent_id = latest_minutes.agent_id
AND stats.created_at >= $1
AND stats.created_at >= latest_minutes.minute_bucket
AND stats.created_at < latest_minutes.minute_bucket + '1 minute'::interval
AND stats.usage,
jsonb_each_text(stats.session_counts) AS sess(app_name, sessions)
GROUP BY sess.app_name
) AS app_totals
)
SELECT * FROM agent_stats, latest_agent_stats;
-- name: GetWorkspaceAgentStats :many
WITH agent_stats AS (
SELECT
user_id,
agent_id,
workspace_id,
template_id,
MIN(created_at)::timestamptz AS aggregated_from,
coalesce(SUM(rx_bytes), 0)::bigint AS workspace_rx_bytes,
coalesce(SUM(tx_bytes), 0)::bigint AS workspace_tx_bytes,
coalesce((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_50,
coalesce((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY connection_median_latency_ms)), -1)::FLOAT AS workspace_connection_latency_95
FROM workspace_agent_stats
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
WHERE workspace_agent_stats.created_at > $1 AND connection_median_latency_ms > 0
GROUP BY user_id, agent_id, workspace_id, template_id
), latest_stats AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY agent_id ORDER BY created_at DESC) AS rn
FROM workspace_agent_stats WHERE created_at > $1
), latest_agent_stats AS (
-- rn = 1 leaves one row per agent, and that row's session_counts is
-- already the object this query reports, empty map included.
SELECT
agent_id,
session_counts
FROM latest_stats
WHERE rn = 1
)
SELECT * FROM agent_stats JOIN latest_agent_stats ON agent_stats.agent_id = latest_agent_stats.agent_id;
-- name: GetWorkspaceAgentUsageStats :many
WITH stats AS (
SELECT
*,
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
created_at > $1 AND connection_median_latency_ms > 0 AS reports_latency,
usage AND date_trunc('minute', created_at) = MAX(date_trunc('minute', created_at)) FILTER (
-- Exclude the current partial minute.
WHERE usage AND created_at < date_trunc('minute', now())
) OVER (PARTITION BY agent_id) AS in_latest_usage_minute
FROM workspace_agent_stats
WHERE created_at >= $1
), latest_sessions AS (
-- One row per agent, so joining it below neither multiplies the byte and
-- latency aggregates nor adds groups.
SELECT
agent_id,
coalesce(jsonb_object_agg(app_name, app_sessions), '{}'::jsonb)::jsonb AS session_counts
FROM (
SELECT
stats.agent_id,
sess.app_name,
SUM(sess.sessions::bigint) AS app_sessions
FROM stats, jsonb_each_text(stats.session_counts) AS sess(app_name, sessions)
WHERE stats.in_latest_usage_minute
GROUP BY stats.agent_id, sess.app_name
) AS app_totals
GROUP BY agent_id
)
SELECT
stats.user_id,
stats.agent_id,
stats.workspace_id,
stats.template_id,
MIN(stats.created_at) FILTER (WHERE reports_latency)::timestamptz AS aggregated_from,
coalesce(SUM(stats.rx_bytes) FILTER (WHERE reports_latency), 0)::bigint AS workspace_rx_bytes,
coalesce(SUM(stats.tx_bytes) FILTER (WHERE reports_latency), 0)::bigint AS workspace_tx_bytes,
coalesce((PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY stats.connection_median_latency_ms) FILTER (WHERE reports_latency)), -1)::FLOAT AS workspace_connection_latency_50,
coalesce((PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY stats.connection_median_latency_ms) FILTER (WHERE reports_latency)), -1)::FLOAT AS workspace_connection_latency_95,
-- Repeated so this row keeps the same layout as GetWorkspaceAgentStats, which
-- telemetry converts between.
stats.agent_id,
coalesce(latest_sessions.session_counts, '{}'::jsonb)::jsonb AS session_counts
FROM stats
LEFT JOIN latest_sessions ON latest_sessions.agent_id = stats.agent_id
GROUP BY stats.user_id, stats.agent_id, stats.workspace_id, stats.template_id, latest_sessions.session_counts
HAVING BOOL_OR(reports_latency);
-- name: GetWorkspaceAgentStatsAndLabels :many
WITH agent_stats AS (
SELECT
user_id,
agent_id,
workspace_id,
coalesce(SUM(rx_bytes), 0)::bigint AS rx_bytes,
coalesce(SUM(tx_bytes), 0)::bigint AS tx_bytes
FROM workspace_agent_stats
WHERE workspace_agent_stats.created_at > $1
GROUP BY user_id, agent_id, workspace_id
), latest_stats AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY agent_id ORDER BY created_at DESC) AS rn
FROM workspace_agent_stats
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
WHERE created_at > $1 AND connection_median_latency_ms > 0
), latest_agent_stats AS (
-- rn = 1 leaves one row per agent, so the session object, connection
-- count, and latency are that row's own columns, empty map included.
SELECT
agent_id,
session_counts,
connection_count,
connection_median_latency_ms
FROM latest_stats
WHERE rn = 1
)
SELECT
users.username, workspace_agents.name AS agent_name, workspaces.name AS workspace_name, rx_bytes, tx_bytes,
session_counts,
connection_count, connection_median_latency_ms
FROM
agent_stats
JOIN
latest_agent_stats
ON
agent_stats.agent_id = latest_agent_stats.agent_id
JOIN
users
ON
users.id = agent_stats.user_id
JOIN
workspace_agents
ON
workspace_agents.id = agent_stats.agent_id
JOIN
workspaces
ON
workspaces.id = agent_stats.workspace_id;
-- name: GetWorkspaceAgentUsageStatsAndLabels :many
WITH agent_stats AS (
SELECT
user_id,
agent_id,
workspace_id,
coalesce(SUM(rx_bytes), 0)::bigint AS rx_bytes,
coalesce(SUM(tx_bytes), 0)::bigint AS tx_bytes,
coalesce(MAX(connection_median_latency_ms), 0)::float AS connection_median_latency_ms
FROM workspace_agent_stats
-- The greater than 0 is to support legacy agents that don't report connection_median_latency_ms.
WHERE workspace_agent_stats.created_at > $1 AND connection_median_latency_ms > 0
GROUP BY user_id, agent_id, workspace_id
), latest_stats AS (
SELECT *
FROM workspace_agent_stats
-- We only want the latest stats, but those stats might be
-- spread across multiple rows.
WHERE usage AND created_at > now() - '1 minute'::interval
), latest_sessions AS (
-- Summed per app name here so the connection count below keeps seeing one
-- row per agent instead of one row per app name.
SELECT
agent_id,
coalesce(jsonb_object_agg(app_name, app_sessions), '{}'::jsonb)::jsonb AS session_counts
FROM (
SELECT
latest_stats.agent_id,
sess.app_name,
SUM(sess.sessions::bigint) AS app_sessions
FROM latest_stats, jsonb_each_text(latest_stats.session_counts) AS sess(app_name, sessions)
GROUP BY latest_stats.agent_id, sess.app_name
) AS app_totals
GROUP BY agent_id
), latest_agent_stats AS (
SELECT
latest_stats.agent_id,
coalesce(latest_sessions.session_counts, '{}'::jsonb)::jsonb AS session_counts,
coalesce(SUM(latest_stats.connection_count), 0)::bigint AS connection_count
FROM latest_stats
LEFT JOIN latest_sessions ON latest_sessions.agent_id = latest_stats.agent_id
GROUP BY latest_stats.user_id, latest_stats.agent_id, latest_stats.workspace_id, latest_sessions.session_counts
)
SELECT
users.username, workspace_agents.name AS agent_name, workspaces.name AS workspace_name, rx_bytes, tx_bytes,
coalesce(session_counts, '{}'::jsonb)::jsonb AS session_counts,
coalesce(connection_count, 0)::bigint AS connection_count,
connection_median_latency_ms
FROM
agent_stats
LEFT JOIN
latest_agent_stats
ON
agent_stats.agent_id = latest_agent_stats.agent_id
JOIN
users
ON
users.id = agent_stats.user_id
JOIN
workspace_agents
ON
workspace_agents.id = agent_stats.agent_id
JOIN
workspaces
ON
workspaces.id = agent_stats.workspace_id;