Amazon Aurora PostgreSQL ULM アプリケーションでのログとメトリクスの収集

ログとメトリクスのタイプ

Aurora PostgreSQL ULM 用の Sumo Logic アプリケーションは、以下のログとメトリクスを使用します。

• AWS CloudTrail

• Aurora CloudWatch メトリクス

ログの例

AWS CloudTrail ログの例を以下に示します。

{"eventVersion":"1.05","userIdentity":{"type":"IAMUser","principalId":"AI1234567890QEWUABG5Q",
"arn":"arn:aws:iam::951234567898:user/bwilliams","accountId":"951234567898","accessKeyId":
"ABCDEFGHIHFBOT4FDVK","userName":"jjones","sessionContext":{"attributes":{"mfaAuthenticated":
"true","creationDate":"2018-11-05T11:22:45Z"}},"invokedBy":"signin.amazonaws.com"},"eventTime":
"2018-11-12T06:56:02Z","eventSource":"rds.amazonaws.com","eventName":"DeleteDBCluster","awsRegion"
:"us-east-3","sourceIPAddress":"19.174.45.8","userAgent":"signin.amazonaws.com","requestParameters"
:{"dBClusterIdentifier":"nitinpsql968cluster01","skipFinalSnapshot":false,"finalDBSnapshotIdentifier"
:"psqldb968nitin02-final-snapshot"},"responseElements":{"allocatedStorage":1,"availabilityZones"
:["us-east-1a","us-east-1b","us-east-1c"],"backupRetentionPeriod":2,"databaseName":"nitintestdpsql1",
"dBClusterIdentifier":"nitinpsql968cluster01","dBClusterParameterGroup":"default.aurora-postgresql9.6",
"dBSubnetGroup":"default-vpc-b81fc4d7","status":"available","earliestRestorableTime":"Nov 5, 2018 2:17:31 PM",
"endpoint":"nitinpsql968cluster01.cluster-ci123456789d.us-east-3.rds.amazonaws.com","readerEndpoint"
:"nitinpsql968cluster01.cluster-ro-ci123456789d.us-east-1.rds.amazonaws.com","multiAZ":false,"engine"
:"aurora-postgresql","engineVersion":"9.6.8","latestRestorableTime":"Nov 5, 2018 3:36:06 PM","port"
:5432,"masterUsername":"npandepsql","preferredBackupWindow":"08:59-09:29","preferredMaintenanceWindow"
:"sun:08:09-sun:08:39","readReplicaIdentifiers":[],"dBClusterMembers":[{"dBInstanceIdentifier":"psqldb968nitin02"
,"isClusterWriter":true,"dBClusterParameterGroupStatus":"in-sync","promotionTier":1}],"vpcSecurityGroups"
:[{"vpcSecurityGroupId":"sg-0e81530fe36e37076","status":"active"}],"hostedZoneId":"Z2R2ITUGPM61AM",
"storageEncrypted":true,"kmsKeyId":"arn:aws:kms:us-west-3:951234567898:key/9a3d8016-4cdb-478f-a3a4-9a310fc25307",
"dbClusterResourceId":"cluster-LXLBREEIXOAMLSUUDXVKXFVIDA","dBClusterArn":"arn:aws:rds:us-west-2:951234567898:cluster:nitinpsql968cluster01",
"associatedRoles":[],"iAMDatabaseAuthenticationEnabled":false,"clusterCreateTime":"Nov 5, 2018 2:16:12 PM","engineMode"
:"provisioned","deletionProtection":false,"httpEndpointEnabled":false},"requestID":"0df6f69d-8040-45fa-9171-98043977a14c",
"eventID":"ab48927c-7bd8-4c1d-9d86-0b2f6732949c","eventType":"AwsApiCall","recipientAccountId":"951234567898"}

クエリの例

このセクションでは、ダッシュボードのパネルから引用したログ クエリとメトリクス クエリの例を示します。

(_sourceCategory=*cloudtrail* or _sourceCategory=*AWS_EAGLE*) "\"eventSource\":\"rds.amazonaws.com\"" ("\"engine\":\"aurora-postgresql\"")
| json "userIdentity", "eventSource", "eventName", "awsRegion", "sourceIPAddress", "userAgent", "eventType", "recipientAccountId", "requestParameters", "responseElements", "requestID", "errorCode", "errorMessage" nodrop
| json field=userIdentity "type", "principalId", "arn", "userName", "accountId" nodrop
| json field=userIdentity "sessionContext.attributes.mfaAuthenticated" as mfaAuthenticated nodrop
| json field=requestParameters "dBClusterIdentifier", "engine", "engineMode" as req_dBClusterIdentifier, req_engine, req_engineMode nodrop
| json field=responseElements "dBClusterIdentifier", "engine", "engineMode" as res_dBClusterIdentifier, res_engine, res_engineMode nodrop
| parse field=arn ":assumed-role/*" as user nodrop  
| parse field=arn "arn:aws:iam::*:*" as accountId, user nodrop
| if (isEmpty(errorCode), "Success", "Failure") as eventStatus
| if (isEmpty(userName), user, userName) as user
| if (isEmpty(req_dBClusterIdentifier), res_dBClusterIdentifier, req_dBClusterIdentifier) as dBClusterIdentifier
| if (isEmpty(req_engine), res_engine, req_engine) as engine
| if (isEmpty(req_engineMode), res_engineMode, req_engineMode) as engineMode
| where eventSource = "rds.amazonaws.com" and (req_engine in ("aurora-postgresql") or res_engine in ("aurora-postgresql"))
| timeslice 6h
| count by _timeslice, eventStatus, eventName
| transpose row _timeslice column eventStatus, eventName

_sourceCategory=AWS/RDS/Metric Namespace=AWS/RDS  metric=VolumeWriteIOPs DBClusterIdentifier=* Statistic=Average | avg by DBClusterIdentifier

ログとメトリクスの収集の設定

Aurora PostgreSQL ULM アプリケーションは、CloudTrail イベント ログと CloudWatch ログのモニタリングに使用します。メトリクスにより、データベース リソースの利用状況とスループットのパフォーマンスをモニタリングできます。CloudTrail イベントにより、ユーザによる Aurora サービスとオペレーションの使用をモニタリングできます。

このセクションでは、Aurora PostgreSQL ULM 用の Sumo Logic アプリケーションでログとメトリクスを収集する手順を説明します。

• ステップ 1: AWS CloudTrail Source を使用した AWS CloudTrail イベントの収集
• ステップ 2: AWS CloudWatch メトリクス Source を使用した Aurora CloudWatch メトリクスの収集