35 lines
946 B
Markdown
35 lines
946 B
Markdown
|
|
```SQL
|
|
WITH DrainingConnectionIds AS (
|
|
SELECT DISTINCT
|
|
connectionId
|
|
FROM
|
|
`phenix-pcast.pcast_logs_us.syslog`,
|
|
UNNEST(REGEXP_EXTRACT_ALL(Message, r"'([^']+)'")) AS connectionId
|
|
WHERE
|
|
Timestamp >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -2 MINUTE)
|
|
AND Message LIKE "%Websocket connectionids preventing drain%"
|
|
),
|
|
SessionStartLogs AS (
|
|
SELECT
|
|
REGEXP_EXTRACT(Message, r"\[([^\]]+)\]") AS ApplicationId,
|
|
REGEXP_EXTRACT(Message, r"connection \[([^\]]+)\]") AS ConnectionId,
|
|
Message
|
|
FROM
|
|
`phenix-pcast.pcast_logs_us.syslog`
|
|
WHERE
|
|
Timestamp >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -90 DAY)
|
|
AND Message LIKE '%Session started with connection%'
|
|
)
|
|
SELECT
|
|
s.ApplicationId,
|
|
s.ConnectionId,
|
|
REGEXP_EXTRACT_ALL(s.Message, r"connection \[(\]]+)\]")[SAFE_OFFSET(1)] AS SessionId
|
|
FROM
|
|
SessionStartLogs AS s
|
|
INNER JOIN
|
|
DrainingConnectionIds AS d
|
|
ON
|
|
s.ConnectionId = d.connectionId
|
|
|
|
``` |