SQL 查詢示例

-- Matches log entries whose timestamp is within the most recent 1 hour. WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) 

SELECT   timestamp, severity, resource.type, log_name, text_payload, proto_payload, json_payload FROM   `PROJECT_ID.LOCATION._Default._AllLogs` -- Limit to 1000 entries LIMIT 1000 

SELECT   -- Display the timestamp, and the part of the name that begins with test.   timestamp, REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name, FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` WHERE   -- Get the value of jobName, which is a subfield in a JSON structure.   JSON_VALUE(json_payload.jobName) IS NOT NULL ORDER BY timestamp DESC LIMIT 20 

-- Matches log entries whose timestamp is within the most recent hour WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) 

-- Matches log entries whose resource type is gce_instance WHERE resource.type = "gce_instance" 

-- Matches log entries whose severity is INFO or ERROR WHERE severity IS NOT NULL AND severity IN ('INFO', 'ERROR') 

-- Matches log entries whose severity level is at least NOTICE WHERE severity_number IS NOT NULL AND severity_number > 200 

SELECT   timestamp, JSON_VALUE(resource.labels.zone) AS zone, json_payload, resource, labels FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` WHERE   -- Matches log entries whose resource type is gce_instance and whose zone is   -- us-central1-f. Because resource has data type JSON, you must use JSON_VALUE   -- to get the value for subfields, like zone.   resource.type = "gce_instance" AND   JSON_VALUE(resource.labels.zone) = "us-central1-f" ORDER BY timestamp ASC 

{    type: "gce_instance"    labels: {       instance_id: "1234512345123451"       project_id: "my-project"       zone: "us-central1-f"    } } 

-- Matches log entries that have a HTTP request_method field. -- Don't compare http_request to NULL. This field has a data type of RECORD. WHERE http_request.request_method IS NOT NULL 

-- Matches log entries whose HTTP request_method is GET or POST. WHERE http_request.request_method IN ('GET', 'POST') 

-- Matches log entries that have an http_request.status field. WHERE http_request.status IS NOT NULL 

-- Compare the value of the status field to NULL. WHERE JSON_VALUE(json_payload.status) IS NOT NULL 

-- Compare the status field to NULL. WHERE json_payload.status IS NOT NULL 

{     status: {         measureTime: "1661517845"     } } 

{     @type: "type.googleapis.com/google.cloud.scheduler.logging.AttemptFinished"     jobName: "projects/my-project/locations/us-central1/jobs/test1"     relativeUrl: "/food=cake"     status: "NOT_FOUND"     targetType: "APP_ENGINE_HTTP" } 

SELECT   -- Truncate the timestamp by hour.   TIMESTAMP_TRUNC(timestamp, HOUR) AS hour,   JSON_VALUE(json_payload.status) AS status,   -- Count the number log entries in each group.   COUNT(*) AS count FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` WHERE   -- Matches log entries that have a status field whose value isn't NULL.   json_payload IS NOT NULL AND JSON_VALUE(json_payload.status) IS NOT NULL GROUP BY   -- Group by hour and status   hour,status ORDER BY hour ASC 

SELECT    -- Count the number of log entries for each resource type    resource.type, COUNT(*) AS count FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` GROUP BY resource.type LIMIT 100 

SELECT   -- Count the number of log entries for each severity.   severity, COUNT(*) AS count FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` WHERE   severity IS NOT NULL GROUP BY severity ORDER BY severity LIMIT 100 

SELECT   -- Count the number of log entries for each log ID.   log_id, COUNT(*) AS count FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` GROUP BY log_id ORDER BY count DESC LIMIT 100 

SELECT   -- Compute the average latency for each group. Because the labels field has a   -- data type of JSON, use JSON_VALUE to get the value of checker_location.   JSON_VALUE(labels.checker_location) AS location,   AVG(http_request.latency.seconds) AS secs, http_request.request_url FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` WHERE   -- Matches log entries when the request_method field is GET.   http_request IS NOT NULL AND http_request.request_method IN ('GET') GROUP BY   -- Group by request URL and location   http_request.request_url, location ORDER BY location LIMIT 100 

SELECT   -- Compute the average number of bytes sent per location. Because labels has   -- a data type of JSON, use JSON_VALUE to get the value of the location field.   -- bytes_sent is a string. Must cast to a FLOAT64 before computing average.   JSON_VALUE(resource.labels.location) AS location,   AVG(CAST(JSON_VALUE(json_payload.bytes_sent) AS FLOAT64)) AS bytes FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` WHERE   resource.type = "gce_subnetwork" AND json_payload IS NOT NULL GROUP BY   -- Group by location   location LIMIT 100 

SELECT   -- Extract the value that begins with test.   -- Count the number of log entries for each name.   REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,   COUNT(*) AS count FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` WHERE   json_payload.jobName IS NOT NULL GROUP BY name ORDER BY count LIMIT 20 

SELECT   timestamp, log_id, proto_payload, severity, resource.type, resource, labels FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` AS t WHERE   -- Search data access audit logs for the IP address that matches 35.193.12.15.   -- The use of backticks prevents the string from being tokenized.   proto_payload IS NOT NULL AND   log_id = "cloudaudit.googleapis.com/data_access" AND   SEARCH(t,"`35.193.12.15`") ORDER BY timestamp ASC LIMIT 20 

  SEARCH(t,"35.193.12.15") 

  SEARCH(t,"`cloudaudit.googleapis.com/data_access`") 

  SEARCH(t,"`Hello World` happy days") 

   SEARCH((text_payload, json_payload) ,"`35.222.132.245`") 

SELECT   timestamp, log_id, proto_payload, severity, resource.type, resource, labels FROM   `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` AS t WHERE   -- Search data access audit logs for the IP address that matches 35.193.12.15.   -- CONTAINS_SUBSTR performs a contains-test.   proto_payload IS NOT NULL AND   log_id = "cloudaudit.googleapis.com/data_access" AND   CONTAINS_SUBSTR(t,"35.193.12.15") ORDER BY timestamp ASC LIMIT 20 

SELECT   -- Do an inner join on two tables by using the span ID and trace ID.   -- Don't join only by span ID, as this field isn't globally unique.   -- From the first view, show the timestamp, severity, and JSON payload.   -- From the second view, show the JSON payload.   a.timestamp, a.severity, a.json_payload, b.json_payload, a.span_id, a.trace FROM  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_1` a JOIN  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_2` b ON   a.span_id = b.span_id AND   a.trace = b.trace LIMIT 100 

SELECT   timestamp, log_name, severity, json_payload, resource, labels -- Create a union of two log views FROM(   SELECT * FROM `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_1`   UNION ALL   SELECT * FROM `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_2` ) -- Sort the union by timestamp. ORDER BY timestamp ASC LIMIT 100