ablog

不器用で落着きのない技術者のメモ

Athena で CloudTrail のイベントを集計する

AWS

CloudTrail を S3 に保存しておき（設定方法はコチラ）、Athena で集計してみた。

  • eventsource で集計
select  eventsource, count(1) as cnt 
from default.cloudtrail_logs_cloudtrail_do_not_delete 
group by eventsource
order by cnt desc
eventsource count
s3.amazonaws.com 1111063
ec2.amazonaws.com 86762
sts.amazonaws.com 52597
athena.amazonaws.com 10359
ssm.amazonaws.com 8277
glue.amazonaws.com 2114
cloudformation.amazonaws.com 1882
kms.amazonaws.com 1604
elasticmapreduce.amazonaws.com 1136
cloudtrail.amazonaws.com 1100
monitoring.amazonaws.com 991
autoscaling.amazonaws.com 634
iam.amazonaws.com 447
rds.amazonaws.com 430
logs.amazonaws.com 262
lambda.amazonaws.com 216
config.amazonaws.com 136
elasticloadbalancing.amazonaws.com 120
signin.amazonaws.com 95
redshift.amazonaws.com 88
sns.amazonaws.com 79
quicksight.amazonaws.com 28
sqs.amazonaws.com 9
route53.amazonaws.com 7
dynamodb.amazonaws.com 6
elasticbeanstalk.amazonaws.com 6
route53domains.amazonaws.com 4
xray.amazonaws.com 2
ds.amazonaws.com 1
  • eventsource、eventname で集計
select  eventsource, eventname, count(1) as cnt 
from default.cloudtrail_logs_cloudtrail_do_not_delete 
group by eventsource, eventname
order by cnt desc
eventsource eventname count
s3.amazonaws.com GetObject 856835
s3.amazonaws.com HeadObject 92374
s3.amazonaws.com PutObject 79119
sts.amazonaws.com AssumeRole 52501
ec2.amazonaws.com DescribeAddresses 30794
s3.amazonaws.com ListObjects 30767
ec2.amazonaws.com DescribeInstances 22475
ec2.amazonaws.com DescribeInstanceStatus 15614
s3.amazonaws.com HeadBucket 10675
s3.amazonaws.com UploadPartCopy 10362
ec2.amazonaws.com DescribeNetworkInterfaces 9756
athena.amazonaws.com GetQueryExecution 9040
s3.amazonaws.com CopyObject 6353
ssm.amazonaws.com UpdateInstanceInformation 5658
ssm.amazonaws.com ListInstanceAssociations 2612
ec2.amazonaws.com DescribeVolumes 2446
cloudformation.amazonaws.com DescribeStackResource 1793
ec2.amazonaws.com DescribeInstanceAttribute 1249
ec2.amazonaws.com DescribeKeyPairs 1053
monitoring.amazonaws.com DescribeAlarms 990
kms.amazonaws.com GenerateDataKey 885
s3.amazonaws.com GetBucketPolicy 709
ec2.amazonaws.com DescribeVolumeStatus 694
s3.amazonaws.com GetBucketAcl 693
kms.amazonaws.com Decrypt 616
cloudtrail.amazonaws.com GetTrailStatus 543
ec2.amazonaws.com DescribeSecurityGroups 504
s3.amazonaws.com DeleteObject 437
s3.amazonaws.com CreateMultipartUpload 421
athena.amazonaws.com GetQueryResults 421
s3.amazonaws.com CompleteMultipartUpload 420
athena.amazonaws.com StartQueryExecution 417
elasticmapreduce.amazonaws.com DescribeCluster 407
cloudtrail.amazonaws.com DescribeTrails 394
s3.amazonaws.com ListBuckets 379
ec2.amazonaws.com DescribeTags 338
autoscaling.amazonaws.com DescribeAutoScalingGroups 282
glue.amazonaws.com GetCatalogImportStatus 266
autoscaling.amazonaws.com DescribeNotificationConfigurations 264
s3.amazonaws.com GetBucketEncryption 264
glue.amazonaws.com GetCrawlerMetrics 258
ec2.amazonaws.com DescribeImages 246
elasticmapreduce.amazonaws.com ListInstanceGroups 234
glue.amazonaws.com GetDatabases 226
elasticmapreduce.amazonaws.com ListBootstrapActions 224
athena.amazonaws.com ListQueryExecutions 204
glue.amazonaws.com GetJobRuns 202
glue.amazonaws.com GetCrawler 182
ec2.amazonaws.com RunInstances 181
ec2.amazonaws.com DescribeVpcs 156
glue.amazonaws.com GetConnections 149
ec2.amazonaws.com DescribeRegions 140
lambda.amazonaws.com ListFunctions20150331 132
glue.amazonaws.com GetClassifiers 131
logs.amazonaws.com CreateLogStream 124
rds.amazonaws.com DescribeDBEngineVersions 122
athena.amazonaws.com BatchGetQueryExecution 122
glue.amazonaws.com GetCrawlers 121
elasticloadbalancing.amazonaws.com DescribeLoadBalancers 120
rds.amazonaws.com DescribeOrderableDBInstanceOptions 115
ec2.amazonaws.com DescribeSubnets 112
ec2.amazonaws.com DescribeAvailabilityZones 109
glue.amazonaws.com GetTable 107
ec2.amazonaws.com DescribeSnapshots 94
ec2.amazonaws.com DescribeStaleSecurityGroups 93
s3.amazonaws.com UploadPart 91
rds.amazonaws.com DescribeDBInstances 90
ec2.amazonaws.com DescribeAccountAttributes 77
logs.amazonaws.com DescribeMetricFilters 76
glue.amazonaws.com GetJobs 76
glue.amazonaws.com GetTriggers 75
kms.amazonaws.com ListAliases 74
ec2.amazonaws.com DescribeRouteTables 71
cloudtrail.amazonaws.com LookupEvents 65
config.amazonaws.com DescribeConfigurationRecorders 65
elasticmapreduce.amazonaws.com ListSteps 64
ec2.amazonaws.com DescribeIdFormat 62
glue.amazonaws.com GetSecurityConfigurations 61
config.amazonaws.com DescribeConfigurationRecorderStatus 61
signin.amazonaws.com RenewRole 59
ec2.amazonaws.com DescribeLaunchTemplates 56
elasticmapreduce.amazonaws.com ListEventsPrivate 54
elasticmapreduce.amazonaws.com ListYarnApplicationsPrivate 54
glue.amazonaws.com GetTables 51
s3.amazonaws.com GetBucketVersioning 50
iam.amazonaws.com ListRolePolicies 50
s3.amazonaws.com GetBucketWebsite 46
s3.amazonaws.com GetBucketTagging 44
sns.amazonaws.com ListTopics 43
iam.amazonaws.com ListInstanceProfiles 42
s3.amazonaws.com ListObjectVersions 41
s3.amazonaws.com GetObjectAcl 40
iam.amazonaws.com ListInstanceProfilesForRole 40
cloudtrail.amazonaws.com GetEventSelectors 38
s3.amazonaws.com GetBucketLocation 37
athena.amazonaws.com GetQueryResultsStream 37
autoscaling.amazonaws.com DescribeScalingPolicies 36
autoscaling.amazonaws.com DescribePolicies 36
ec2.amazonaws.com DescribeNetworkAcls 35
glue.amazonaws.com GetTableVersions 35
ec2.amazonaws.com TerminateInstances 34
s3.amazonaws.com GetBucketNotification 34
glue.amazonaws.com GetPartitions 32
sns.amazonaws.com ListSubscriptions 32
elasticmapreduce.amazonaws.com ListSecurityConfigurations 32
logs.amazonaws.com DescribeExportTasks 32
ec2.amazonaws.com DescribeDhcpOptions 31
iam.amazonaws.com GetRole 31
iam.amazonaws.com ListRoles 30
s3.amazonaws.com GetBucketCors 30
cloudformation.amazonaws.com DescribeStacks 30
iam.amazonaws.com ListAttachedRolePolicies 30
iam.amazonaws.com GetPolicyVersion 29
ec2.amazonaws.com CreateTags 29
signin.amazonaws.com SwitchRole 28
ec2.amazonaws.com DescribeHosts 27
ec2.amazonaws.com DescribeVolumesModifications 27
ec2.amazonaws.com DescribePlacementGroups 27
redshift.amazonaws.com DescribeClusters 24
cloudformation.amazonaws.com DescribeStackEvents 24
ec2.amazonaws.com DescribeInstanceCreditSpecifications 24
lambda.amazonaws.com GetPolicy20150331v2 23
athena.amazonaws.com CreateNamedQuery 22
rds.amazonaws.com DescribeDBSecurityGroups 22
iam.amazonaws.com ListAccountAliases 22
iam.amazonaws.com GetAccountPasswordPolicy 21
iam.amazonaws.com GetAccountSummary 21
s3.amazonaws.com GetBucketRequestPayment 20
s3.amazonaws.com GetBucketLogging 20
lambda.amazonaws.com GetFunction20150331v2 19
glue.amazonaws.com GetJob 19
ec2.amazonaws.com DescribeVpcAttribute 19
cloudformation.amazonaws.com ListStacks 19
iam.amazonaws.com ListPolicyVersions 18
s3.amazonaws.com GetBucketLifecycle 18
elasticmapreduce.amazonaws.com ListReleases 18
ec2.amazonaws.com AuthorizeSecurityGroupIngress 18
elasticmapreduce.amazonaws.com ListInstances 16
logs.amazonaws.com DescribeLogStreams 15
iam.amazonaws.com GetRolePolicy 15
ec2.amazonaws.com DescribeSpotPriceHistory 15
iam.amazonaws.com GetPolicy 13
kms.amazonaws.com ListKeys 13
kms.amazonaws.com DescribeKey 13
ec2.amazonaws.com DescribeVpcEndpoints 13
glue.amazonaws.com GetDevEndpoints 13
glue.amazonaws.com UpdateCrawler 12
glue.amazonaws.com StartCrawler 12
redshift.amazonaws.com DescribeEvents 12
s3.amazonaws.com GetBucketReplication 12
cloudformation.amazonaws.com DescribeStackResources 11
iam.amazonaws.com ListPolicies 11
glue.amazonaws.com StartJobRun 11
s3.amazonaws.com DeleteObjects 10
quicksight.amazonaws.com GetAnalysis 10
rds.amazonaws.com DescribeOptionGroups 10
autoscaling.amazonaws.com DescribeScalingActivities 10
lambda.amazonaws.com ListTags20170331 9
rds.amazonaws.com DescribeDBClusters 9
redshift.amazonaws.com DescribeLoggingStatus 9
iam.amazonaws.com ListEntitiesForPolicy 9
logs.amazonaws.com CreateLogGroup 9
redshift.amazonaws.com DescribeClusterDbRevisions 9
redshift.amazonaws.com DescribeClusterParameterGroups 9
rds.amazonaws.com DescribeEvents 9
lambda.amazonaws.com ListVersionsByFunction20150331 9
iam.amazonaws.com AttachRolePolicy 9
lambda.amazonaws.com ListAliases20150331 9
lambda.amazonaws.com ListEventSourceMappings20150331 9
glue.amazonaws.com GetDevEndpoint 8
elasticmapreduce.amazonaws.com ListSparkStagesPrivate 8
ec2.amazonaws.com ModifyInstanceAttribute 8
s3.amazonaws.com CreateBucket 8
quicksight.amazonaws.com CreateDataSource 8
signin.amazonaws.com ExitRole 8
rds.amazonaws.com DescribePendingMaintenanceActions 7
glue.amazonaws.com GetDataflowGraph 7
rds.amazonaws.com DescribeDBClusterSnapshots 7
rds.amazonaws.com DescribeCertificates 7
glue.amazonaws.com BatchDeleteTable 7
rds.amazonaws.com DescribeRecommendationGroups 7
athena.amazonaws.com BatchGetNamedQuery 6
ec2.amazonaws.com DescribeVpcPeeringConnections 6
athena.amazonaws.com ListNamedQueries 6
ec2.amazonaws.com DescribeVpcEndpointServiceConfigurations 6
ec2.amazonaws.com DeleteNetworkInterface 6
ec2.amazonaws.com DescribeEgressOnlyInternetGateways 6
glue.amazonaws.com CreateTable 6
glue.amazonaws.com GetDatabase 6
elasticmapreduce.amazonaws.com ListSparkExecutorsPrivate 6
ec2.amazonaws.com DescribeFlowLogs 6
s3.amazonaws.com PutBucketNotification 6
ec2.amazonaws.com DescribeCustomerGateways 6
glue.amazonaws.com CreateCrawler 6
iam.amazonaws.com GetInstanceProfile 6
ec2.amazonaws.com DescribeNatGateways 6
rds.amazonaws.com DescribeDBSnapshots 6
ec2.amazonaws.com DescribeVpnConnections 6
ec2.amazonaws.com DescribeInternetGateways 6
ec2.amazonaws.com DescribeVpnGateways 6
redshift.amazonaws.com DescribeClusterSecurityGroups 6
cloudtrail.amazonaws.com ListTags 6
ec2.amazonaws.com RevokeSecurityGroupIngress 6
glue.amazonaws.com UpdateConnection 5
rds.amazonaws.com DescribeDBClusterParameterGroups 5
glue.amazonaws.com GetDataCatalogEncryptionSettings 5
logs.amazonaws.com DescribeLogGroups 5
rds.amazonaws.com DescribeAccountAttributes 5
rds.amazonaws.com DescribeDBParameterGroups 5
ec2.amazonaws.com DescribeVpcClassicLinkDnsSupport 5
dynamodb.amazonaws.com DescribeTable 4
elasticmapreduce.amazonaws.com DescribeSparkApplicationPrivate 4
elasticmapreduce.amazonaws.com ListSparkJobsPrivate 4
iam.amazonaws.com CreateRole 4
ec2.amazonaws.com AssociateAddress 4
glue.amazonaws.com CreateJob 4
quicksight.amazonaws.com CreateAnalysis 4
iam.amazonaws.com DetachUserPolicy 4
iam.amazonaws.com ListGroupsForUser 4
iam.amazonaws.com ListUsers 4
glue.amazonaws.com CreateDevEndpoint 4
glue.amazonaws.com GetConnection 4
sqs.amazonaws.com DeleteQueue 4
redshift.amazonaws.com DescribeClusterSubnetGroups 4
quicksight.amazonaws.com CreateDataSet 4
iam.amazonaws.com CreatePolicyVersion 4
redshift.amazonaws.com DescribeEventSubscriptions 3
redshift.amazonaws.com DescribeReservedNodes 3
ec2.amazonaws.com DescribePrefixLists 3
glue.amazonaws.com GetPlan 3
redshift.amazonaws.com DescribeHsmClientCertificates 3
redshift.amazonaws.com DescribeHsmConfigurations 3
glue.amazonaws.com GetMapping 3
ec2.amazonaws.com CreateNetworkInterface 3
redshift.amazonaws.com DescribeClusterSnapshots 3
elasticmapreduce.amazonaws.com RunJobFlow 3
sqs.amazonaws.com CreateQueue 3
iam.amazonaws.com ListSSHPublicKeys 2
iam.amazonaws.com DeleteAccessKey 2
elasticbeanstalk.amazonaws.com DescribeEnvironments 2
iam.amazonaws.com ListGroups 2
route53domains.amazonaws.com ListDomains 2
iam.amazonaws.com ListServiceSpecificCredentials 2
glue.amazonaws.com DeleteJob 2
lambda.amazonaws.com AddPermission20150331v2 2
autoscaling.amazonaws.com DeleteAutoScalingGroup 2
cloudformation.amazonaws.com DeleteStack 2
iam.amazonaws.com ListUserPolicies 2
elasticmapreduce.amazonaws.com ListSparkTasksPrivate 2
route53.amazonaws.com GetHealthCheckCount 2
sns.amazonaws.com DeleteTopic 2
rds.amazonaws.com DescribeDBLogFiles 2
config.amazonaws.com DescribeConfigRules 2
sqs.amazonaws.com SetQueueAttributes 2
elasticmapreduce.amazonaws.com ListSparkExecutorSummaryPrivate 2
ec2.amazonaws.com DeleteSecurityGroup 2
elasticmapreduce.amazonaws.com SetTerminationProtection 2
cloudformation.amazonaws.com GetTemplateSummary 2
lambda.amazonaws.com RemovePermission20150331v2 2
s3.amazonaws.com PutBucketPolicy 2
ec2.amazonaws.com CreateSecurityGroup 2
iam.amazonaws.com DeleteLoginProfile 2
sns.amazonaws.com GetTopicAttributes 2
iam.amazonaws.com ListAttachedUserPolicies 2
elasticbeanstalk.amazonaws.com DescribeApplications 2
route53.amazonaws.com ListTrafficPolicies 2
dynamodb.amazonaws.com DeleteTable 2
iam.amazonaws.com ListMFADevices 2
config.amazonaws.com DescribePendingAggregationRequests 2
elasticmapreduce.amazonaws.com TerminateJobFlows 2
s3.amazonaws.com DeleteBucket 2
iam.amazonaws.com ListSigningCertificates 2
iam.amazonaws.com ListAccessKeys 2
rds.amazonaws.com ListTagsForResource 2
iam.amazonaws.com PutRolePolicy 2
iam.amazonaws.com DeleteUser 2
route53domains.amazonaws.com ListOperations 2
quicksight.amazonaws.com UpdateAnalysis 2
xray.amazonaws.com GetEncryptionConfig 2
elasticbeanstalk.amazonaws.com DeleteApplication 2
autoscaling.amazonaws.com UpdateAutoScalingGroup 2
route53.amazonaws.com GetHostedZoneCount 2
ec2.amazonaws.com ReleaseAddress 2
autoscaling.amazonaws.com DeleteLaunchConfiguration 2
iam.amazonaws.com DetachRolePolicy 2
cloudtrail.amazonaws.com StartLogging 1
iam.amazonaws.com RemoveUserFromGroup 1
ec2.amazonaws.com DescribeVpcClassicLink 1
cloudtrail.amazonaws.com UpdateTrail 1
ec2.amazonaws.com CreateKeyPair 1
s3.amazonaws.com DeleteBucketPolicy 1
glue.amazonaws.com CreateConnection 1
glue.amazonaws.com CreateDatabase 1
s3.amazonaws.com AbortMultipartUpload 1
monitoring.amazonaws.com PutDashboard 1
ds.amazonaws.com DescribeDirectories 1
iam.amazonaws.com AddRoleToInstanceProfile 1
ec2.amazonaws.com AssociateIamInstanceProfile 1
athena.amazonaws.com StopQueryExecution 1
iam.amazonaws.com CreateInstanceProfile 1
iam.amazonaws.com CreateServiceLinkedRole 1
cloudtrail.amazonaws.com CreateTrail 1
lambda.amazonaws.com UpdateFunctionCode20150331v2 1
cloudtrail.amazonaws.com PutEventSelectors 1
route53.amazonaws.com GetTrafficPolicyInstanceCount 1
lambda.amazonaws.com CreateFunction20150331 1
glue.amazonaws.com DeleteCrawler 1

補足

以下の perl ワンライナーで CSV をはてな記法に変換した。

perl -i.org -pe 's/(\",\"|^\"|\"$)/|/g' 08dadeff-aae3-42f8-95ce-716d9a52ab21.csv

参考