steampipe plugin install gcp

Table: gcp_logging_log_entry - Query Google Cloud Logging Log Entries using SQL

Google Cloud Logging is a service that stores logs from your applications, systems, and services on Google Cloud Platform (GCP). It allows you to analyze and export logs to various destinations for long-term storage or further analysis. Google Cloud Logging helps you understand how your applications and services are performing and how they are being used.

Table Usage Guide

The gcp_logging_log_entry table provides insights into Log Entries within Google Cloud Logging. As a System Administrator, explore log entry-specific details through this table, including severity, timestamp, and associated metadata. Utilize it to uncover information about system events, application behavior, and user actions, which can be useful for debugging, auditing, and performance optimization.

Important Notes:

  • For improved performance, it is advised that you use the optional qual timestamp to limit the result set to a specific time period.
  • This table supports optional quals. Queries with optional quals are optimised to use Logging filters. Optional quals are supported for the following columns:

Examples

Basic info

Explore the operations recorded in the Google Cloud Platform's logging service to gain insights into the sequence and timing of events. This can help you understand the operational flow and identify potential issues based on event timings.

select
log_name,
insert_id,
log_entry_operation_first,
log_entry_operation_id,
receive_timestamp
from
gcp_logging_log_entry;
select
log_name,
insert_id,
operation_id,
receive_timestamp
from
gcp_logging_log_entry;

Get log entries by resource type

Analyze the settings to understand the various log entries associated with a specific type of audited resource. This can be particularly useful for pinpointing operational issues or potential security concerns tied to that resource type.

select
log_name,
insert_id,
log_entry_operation_first,
log_entry_operation_last,
resource_type,
span_id,
text_payload
from
gcp_logging_log_entry
where
resource_type = 'audited_resource';
select
log_name,
insert_id,
resource_type,
span_id,
text_payload
from
gcp_logging_log_entry
where
resource_type = 'audited_resource';

List log entries with NOTICE severity

Discover the segments that have log entries with a NOTICE severity. This can help monitor system activity and identify potential issues that may not be critical but are still noteworthy.

select
log_name,
insert_id,
resource_type,
severity,
span_id,
timestamp
from
gcp_logging_log_entry
where
severity = 'NOTICE';
select
log_name,
insert_id,
severity,
span_id,
timestamp
from
gcp_logging_log_entry
where
severity = 'NOTICE';

List log entries in last 30 days

Explore the recent activity within the last month by identifying the log entries. This helps in monitoring system performance and tracking changes, thereby aiding in system maintenance and troubleshooting.

select
log_name,
insert_id,
receive_timestamp,
trace_sampled,
span_id,
timestamp
from
gcp_logging_log_entry
where
timestamp >= now() - interval '30' day;
select
log_name,
insert_id,
receive_timestamp,
trace_sampled,
span_id,
timestamp
from
gcp_logging_log_entry
where
timestamp >= datetime('now', '-30 day');

List log entries that occurred between five to ten minutes ago

Explore the recent activities in your project by identifying log entries that occurred within a specific time frame, in this case, between five to ten minutes ago. This can help in monitoring real-time activities or detecting any irregularities within a short span of time.

select
log_name,
insert_id,
receive_timestamp,
trace_sampled,
severity,
resource_type
from
gcp_logging_log_entry
where
log_name = 'projects/parker-abbb/logs/cloudaudit.googleapis.com%2Factivity'
and timestamp between (now() - interval '10 minutes')
and (now() - interval '5 minutes')
order by
receive_timestamp asc;
select
log_name,
insert_id,
receive_timestamp,
trace_sampled,
severity,
resource_type
from
gcp_logging_log_entry
where
log_name = 'projects/parker-abbb/logs/cloudaudit.googleapis.com%2Factivity'
and timestamp between (datetime('now', '-10 minutes'))
and (datetime('now', '-5 minutes'))
order by
receive_timestamp asc;

Get the last log entries

Explore the most recent activities in your system by checking the last entries in the logs. This can help you monitor system operations, identify potential issues, and maintain a secure and efficient environment.

select
log_name,
insert_id,
operation ->> 'Last' as log_entry_operation_last,
receive_timestamp,
resource_type,
severity,
text_payload
from
gcp_logging_log_entry
where
(operation ->> 'Last') :: boolean;
select
log_name,
insert_id,
json_extract(operation, '$.Last') as log_entry_operation_last,
receive_timestamp,
resource_type,
severity,
text_payload
from
gcp_logging_log_entry
where
json_extract(operation, '$.Last') = 'true';

Filter log entries by log name

Explore the specific log entries by defining a particular log name. This can help in narrowing down the search and making the process of monitoring and troubleshooting more efficient.

select
log_name,
insert_id,
log_entry_operation_first,
log_entry_operation_last,
receive_timestamp,
resource_type,
severity
from
gcp_logging_log_entry
where
log_name = 'projects/parker-abbb/logs/cloudaudit.googleapis.com%2Factivity';
select
log_name,
insert_id,
log_entry_operation_first,
log_entry_operation_last,
receive_timestamp,
resource_type,
severity
from
gcp_logging_log_entry
where
log_name = 'projects/parker-abbb/logs/cloudaudit.googleapis.com%2Factivity';

Get split details of each log entry

Extracting detailed information about specific log entries in a structured and relational manner. It allows for a deeper analysis of the logs by providing contextual information like the sequence of the log entry

select
log_name,
insert_id,
split ->> 'Index' as split_index,
split ->> 'TotalSplits' as total_splits,
split ->> 'Uid' as split_uid
from
gcp_logging_log_entry;
select
log_name,
insert_id,
json_extract(split, '$.Index') as split_index,
json_extract(split, '$.TotalSplits') as total_splits,
json_extract(split, '$.Uid') as split_uid
from
gcp_logging_log_entry;

Get operation details of each log entry

Retrieve the specifics of operation-related details from log entry records. This query can be instrumental in acquiring information regarding the initial operation, concluding operation, and the source of each operation.

select
log_name,
insert_id,
operation_id,
operation ->> 'Producer' as operation_producer,
operation ->> 'First' as operation_first,
operation ->> 'Last' as operation_last
from
gcp_logging_log_entry;
select
log_name,
insert_id,
operation_id,
json_extract(operation, '$.Producer') as operation_producer,
json_extract(operation, '$.First') as operation_first,
json_extract(operation, '$.Last') as operation_last
from
gcp_logging_log_entry;

Filter examples

For more information on Logging log entry filters, please refer to Filter Pattern Syntax.

List log entries of Compute Engine VMs with serverity error

Discover the segments that have logged errors on your Google Compute Engine virtual machines. This query is beneficial in identifying and troubleshooting system faults, ensuring smooth operation of your VMs.

select
log_name,
insert_id,
receive_timestamp,
resource_type,
severity
from
gcp_logging_log_entry
where
filter = 'resource.type = "gce_instance" and (severity = ERROR OR "error")';
select
log_name,
insert_id,
log_entry_operation_first,
log_entry_operation_last,
receive_timestamp,
resource_type,
severity
from
gcp_logging_log_entry
where
filter = 'resource.type = "gce_instance" and (severity = "ERROR" OR severity = "error")';

List events originating from a specific IP address range that occurred over the last hour

This query allows you to monitor and analyze events from a specific IP address range that have occurred in the last hour. It's a useful tool for real-time network security monitoring, helping to promptly identify unusual activity or potential security threats.

select
log_name,
insert_id,
receive_timestamp,
resource_type,
severity,
timestamp
from
gcp_logging_log_entry
where
filter = 'logName = "projects/my_project/logs/my_log" AND ip_in_net(jsonPayload.realClientIP, "10.1.2.0/24")'
and timestamp >= now() - interval '1 hour'
order by
receive_timestamp asc;

Get proto payload details of each log entry

The query is useful for extracting specific information from log entries in a GCP logging system, particularly for entries related to Google Compute Engine (GCE) instances with errors. Extracting specific information from log entries in a GCP logging system, particularly for entries related to Google Compute Engine (GCE) instances with errors.

select
insert_id,
log_name,
proto_payload -> 'authenticationInfo' as authentication_info,
proto_payload -> 'authorizationInfo' as authorization_info,
proto_payload -> 'serviceName' as service_name,
proto_payload -> 'resourceName' as resource_name,
proto_payload ->> '@type' as proto_payload_type,
proto_payload ->> 'methodName' as method_name,
proto_payload ->> 'callerIp' as caller_ip
from
gcp_logging_log_entry
where
filter = 'resource.type = "gce_instance" AND (severity = ERROR OR "error")';
select
insert_id,
log_name,
json_extract(proto_payload, '$.authenticationInfo') AS authentication_info,
json_extract(proto_payload, '$.authorizationInfo') AS authorization_info,
json_extract(proto_payload, '$.serviceName') AS service_name,
json_extract(proto_payload, '$.resourceName') AS resource_name,
json_extract(proto_payload, '$.@type') AS proto_payload_type,
json_extract(proto_payload, '$.methodName') AS method_name,
json_extract(proto_payload, '$.callerIp') AS caller_ip
from
gcp_logging_log_entry
where
filter = 'resource.type = "gce_instance" AND (severity = ERROR OR severity = "error")';

Schema for gcp_logging_log_entry

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
filtertext=The filter pattern for the search.
insert_idtext=A unique identifier for the log entry.
json_payloadjsonbThe log entry payload, represented as a structure that is expressed as a JSON object.
labelsjsonbA map of key, value pairs that provides additional information about the log entry. The labels can be user-defined or system-defined.User-defined labels are arbitrary key, value pairs that you can use to classify logs. System-defined labels are defined by GCP services for platform logs.
locationtextThe GCP multi-region, region, or zone in which the resource is located.
log_nametext=The resource name of the log to which this log entry belongs to.
metadatajsonbAuxiliary metadata for a MonitoredResource object. MonitoredResource objects contain the minimum set of information to uniquely identify a monitored resource instance.
operationjsonbInformation about an operation associated with the log entry, if applicable.
operation_idtext=An arbitrary operation identifier. Log entries with the same identifier are assumed to be part of the same operation.
projecttext=, !=, ~~, ~~*, !~~, !~~*The GCP Project in which the resource is located.
proto_payloadjsonbThe log entry payload, represented as a protocol buffer. Some Google Cloud Platform services use this field for their log entry payloads. The following protocol buffer types are supported; user-defined types are not supported: 'type.googleapis.com/google.cloud.audit.AuditLog' 'type.googleapis.com/google.appengine.logging.v1.RequestLog'
receive_timestamptimestamp with time zone=, >, <, >=, <=The time the log entry was received by Logging.
resourcejsonbThe monitored resource that produced this log entry. Example: a log entry that reports a database error would be associated with the monitored resource designating the particular database that reported the error.
resource_typetext=The monitored resource type.
severitytext=The severity of the log entry.
source_locationjsonbSource code location information associated with the log entry, if any.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
span_idtext=The ID of the Cloud Trace (https://cloud.google.com/trace) span associated with the current operation in which the log is being written.
splitjsonbInformation indicating this LogEntry is part of a sequence of multiple log entries split from a single LogEntry.
tagsjsonbA map of tags for the resource.
text_payloadtext=The log entry payload, represented as a Unicode string (UTF-8).
timestamptimestamp with time zone=, >, <, >=, <=The time the event described by the log entry occurred.
titletextTitle of the resource.
tracetext=The REST resource name of the trace being written to Cloud Trace (https://cloud.google.com/trace) in association with this log entry.
trace_sampledbooleanThe sampling decision of the trace associated with the log entry.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- gcp

You can pass the configuration to the command with the --config argument:

steampipe_export_gcp --config '<your_config>' gcp_logging_log_entry