Skip to main content
Lantern Home

 

Splunk Lantern

Identifying GCP CloudSQL database connections

  1. Last updated
  2. Save as PDF

Logging connections to your CloudSQL database in the Splunk platform is an effective way to keep track of new client interactions. In the examples in this article, we parse the logs to identify the client IP, database user, and database of each operation.

If you haven't already configured your Splunk platform deployment to ingest GCP Cloud SQL logs from Postgres, MySQL, and SQL Server databases, review Monitoring Google Cloud SQL first.  

Data required

How Splunk software can help with this use case

Use the following general base search to get the right source type, source, and specific database (if desired).

index="gcp-auditlogs" 
    sourcetype="google:gcp:pubsub:platform"
    source="projects/project_id/subscriptions/cloudsql_auditlogs
_sub_id" 
    "data.resource.labels.database_id"="gcp-appdcloudplatfo-nprd
-id:db_name"

PostgreSQL

<base search>
| spath data.textPayload 
| rename data.textPayload AS textPayload 
| rex field=textPayload "db=(?<db_name>[^,]+),user=(?<db_user>[^,]+),host=(?<db_host>\S+)"
| rename db_host AS ip
| table _time, db_name, db_user, ip, textPayload

MySQL

<base_search>
| spath data.textPayload
| rex field=data.textPayload "^(?P<timestamp>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{6}Z)\s+(?P<user>\w+)\[\w+\]\s+@\s+\[(?P<ip>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]\d+\s+\d+\s+(?P<operation_type>\w+)\s+(?P<sql_command>.*)$"
| dedup ip
| table _time, user, ip, data.textPayload

SQL Server

<base_search>
| spath data.protoPayload.requestMetadata.callerIp
| spath data.protoPayload.authenticationInfo.principalEmail
| spath data.resource.labels.database_id
| spath data.protoPayload
| rename data.protoPayload.requestMetadata.callerIp AS ip
| rename data.protoPayload.authenticationInfo.principalEmail AS db_user
| rename data.protoPayload as textPayload
| rex field=data.resource.labels.database_id ":(?<db_name>[^:]+)$"
| table _time, db_name, db_user, ip, textPayload

Next steps

Now that you have learned how to identify database connections in your GCP CloudSQL database, check out the other use cases available for this service: