Telemetry: API error codes are returned for events other than `suggestion_error`
Context
We're sending events from VS Code, Visual Studio, and Neovim, that contain api_status_code
values other than 200 when attached to events that aren't suggestion_error
.
Error status codes (e.g. 401) should only be sent with suggestion_error
events.
Query
Click to expand
WITH suggestions_with_context AS (
/*
we need to extract the context fields from the contexts JSON provided in the raw events
A contexts json look like a list of context attached to an event:
To in this CTE for any event, we use LATERAL FLATTEN to create one row per context per event.
We then extract the context schema and the context data
*/
SELECT
*,
f.value['schema']::VARCHAR AS context_data_schema,
f.value['data'] AS context_data,
f.value['data']['user_agent']::VARCHAR as user_agent,
f.value['data']['model_name']::VARCHAR as model_name,
f.value['data']['model_engine']::VARCHAR as model_engine,
f.value['data']['language']::VARCHAR as language,
f.value['data']['prefix_length']::VARCHAR as prefix_length,
f.value['data']['suffix_length']::VARCHAR as suffix_length,
f.value['data']['api_status_code']::VARCHAR as api_status_code,
f.value['data']['ide_name']::VARCHAR as ide_name,
f.value['data']['language_server_version']::VARCHAR as language_server_version,
TO_DATE(dvce_created_tstamp) as date
FROM legacy.snowplow_structured_events_90, lateral flatten(input => TRY_PARSE_JSON(contexts), path => 'data') f
WHERE
app_id IN ('gitlab_ide_extension', 'gitlab_language_server') -- first version of Visual Studio sending events identifies as 'gitlab_language_server'
AND derived_tstamp >= '2023-12-01'
AND derived_tstamp >= date_trunc('day', dateadd(day, -7299, (dateadd(hour, -8, current_timestamp()))::date))::date
), suggestions_new AS (
SELECT
swc_ide.ide_name as ide,
swc_ide.language_server_version as lsp_ver,
swc.*
FROM
suggestions_with_context swc
JOIN suggestions_with_context swc_ide ON swc.event_id = swc_ide.event_id AND swc_ide.context_data_schema like 'iglu:com.gitlab/ide_extension_version/jsonschema%'
WHERE swc.context_data_schema like 'iglu:com.gitlab/code_suggestions_context/jsonschema/%'
AND swc.context_data not like '%3.76.0%' /* 3.76.0 was first VS Code version sending events but sent duplicates */
)
SELECT
-- IFF((user_agent like 'code-completions-language-server-experiment%'), 'code-completions-language-server-experiment', user_agent) as user_agent,
-- api_status_code,
-- event_action,
ide,
-- lsp_ver,
count(1)
-- contexts
FROM suggestions_new
WHERE
api_status_code is not null
and api_status_code > 200
and event_action != 'suggestion_error'
AND date_trunc(day, date)::date >= date_trunc('day', dateadd(day, -7299, (dateadd(hour, -8, current_timestamp()))::date))::date
AND date_trunc(day, date)::date <= date_trunc('day', dateadd(day, 1, (dateadd(hour, -8, current_timestamp()))::date))::date
GROUP BY 1 --,2,3, 4,5
-- LIMIT 50