Google Cloud Platform

Logging Query Language & MySQL Queries

Splunk to GCL Query Translation

Logging Q

Logs Explorer

Logs explorer uses LQL (Logging Query Language) which is a simpler query language, which means it is better suited to quick simple searches.  As a NOC Engineer I use this to quickly gather data regarding an incident happening in the production environment.

Log Analytics

Log Analytics uses MySQL to allow you to gather and extract data.  This allows me to pinpoint more specific data and visualise this data into tables and charts, as well as create dashboards for the broader team to quickly diagnose the possible cause for an incident.  For the extraction in these queries I use a mix of JSON_VALUE & JSON_EXTRACT, depending on if the field being extracted includes integer  or string data.

Logins by Error Message
Logs Explorer

resource.type="RESOURCE"

logName=("projects/GCP_PROJECT/logs/FIRST_BUCKET" OR "projects/GCP_PROJECTS/logs/SECOND_BUCKET")

SEARCH("/login")

severity>=ERROR

Log Analytics

SELECT  

timestamp AS Time_Window,  -- Group by minute (change format if needed)  

JSON_VALUE(json_payload, '$.ip_address') AS IP,  

JSON_VALUE(json_payload, '$.resource') AS Error_Type,  

JSON_VALUE(json_payload, '$.request_uri') AS URL,  

COUNT(*) AS Error_Count  -- Number of occurrences of the error type

FROM  

`GCP-PROJECT.us.BUCKET._AllLogs`

WHERE  

JSON_VALUE(json_payload.message) LIKE '%/login%'  -- Filter for logs containing '/login'   

AND Severity IN ('WARNING', 'ERROR', 'Critical')  -- Filter for severity levels

GROUP BY  Time_Window, Error_Type, IP, URL  -- Group by time window and error type

ORDER BY  Time_Window ASC  -- Ensure results are ordered by time

LIMIT 1000;  -- Limit to 1000 results for easier handling in a chart

Service 5xx Errors
Logs Explorer

resource.type="RESOURCE"

logName="projects/GCP_PROJECT/logs/FIRST_BUCKET"

jsonPayload.owner="SERVICE"

jsonPayload.http_status=("500" OR "503")

Log Analytics

SELECT

timestamp,

json_payload.http_method,

JSON_VALUE(json_payload, '$.service') AS Service,

JSON_VALUE(json_payload, '$.http_status') AS Status

FROM

`GCP-PROJECT.us.BUCKET._AllLogs`

WHERE

CAST(json_value(json_payload.http_status) AS INT64) = 500

Dashboards

Dash

These dashboards were created in Log Analytics using MySQL.  They are a mix of tables, line charts and bar charts. Most include COUNT (*) AS Instances in their query as my team needs to track how many times a service, rate limit or error exists in the log.