Hi guys,
This morning I setup the admin-console dashboard, a template proposed by AWS to measure usage metrics on dashboards for entreprises QS accounts.
I finished the setup and I’m able to see the dashboard, and also the datasets, but for any reason in the CFN-Main dataset, I’m getting null values for all the date fields that allow to measure the usage (see picture attached).
Do you know what can be causing it? The glue jobs are running well, also the spice refresh in QS
Thanks in advance!
Hi! Thank you for using this template. Could you let us know which region you’re running in? If it’s not us-east-1, you may need to go into Athena and modify the DDL of the CloudTrail table to include all regions. Here’s the updated DDL: CREATE EXTERNAL TABLE cloudtrail_logs_pp_all_regions(
eventversion string COMMENT ‘from deserializer’,
useridentity struct<type:string,principalid:string,arn:string,accountid:string,invokedby:string,accesskeyid:string,username:string,sessioncontext:struct<attributes:structmfaauthenticated:string,creationdate:string,sessionissuer:structtype:string,principalid:string,arn:string,accountid:string,username:string>> COMMENT ‘from deserializer’,
eventtime string COMMENT ‘from deserializer’,
eventsource string COMMENT ‘from deserializer’,
eventname string COMMENT ‘from deserializer’,
awsregion string COMMENT ‘from deserializer’,
sourceipaddress string COMMENT ‘from deserializer’,
useragent string COMMENT ‘from deserializer’,
errorcode string COMMENT ‘from deserializer’,
errormessage string COMMENT ‘from deserializer’,
requestparameters string COMMENT ‘from deserializer’,
responseelements string COMMENT ‘from deserializer’,
additionaleventdata string COMMENT ‘from deserializer’,
requestid string COMMENT ‘from deserializer’,
eventid string COMMENT ‘from deserializer’,
resources array<structarn:string,accountid:string,type:string> COMMENT ‘from deserializer’,
eventtype string COMMENT ‘from deserializer’,
apiversion string COMMENT ‘from deserializer’,
readonly string COMMENT ‘from deserializer’,
recipientaccountid string COMMENT ‘from deserializer’,
serviceeventdetails string COMMENT ‘from deserializer’,
sharedeventid string COMMENT ‘from deserializer’,
vpcendpointid string COMMENT ‘from deserializer’)
PARTITIONED BY (
region string COMMENT ‘AWS region’,
timestamp string COMMENT ‘timestamp’
)
ROW FORMAT SERDE
‘com.amazon.emr.hive.serde.CloudTrailSerde’
STORED AS INPUTFORMAT
‘com.amazon.emr.cloudtrail.CloudTrailInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’
LOCATION
‘s3://cloudtrail-awslogs-889399602426-frf8blhu-isengard-do-not-delete/AWSLogs/889399602426/CloudTrail/’
TBLPROPERTIES (
‘classification’=‘cloudtrail’,
‘projection.enabled’=‘true’,
‘projection.region.type’=‘enum’,
‘projection.region.values’=‘us-east-1,us-east-2,us-west-1,us-west-2,eu-west-1,eu-central-1,ap-southeast-1,ap-southeast-2,ap-northeast-1,ap-northeast-2,sa-east-1’, – add regions as you need
‘projection.timestamp.type’=‘date’,
‘projection.timestamp.format’=‘yyyy/MM/dd’,
‘projection.timestamp.range’=‘2022/08/01,NOW’,
‘projection.timestamp.interval’=‘1’,
‘projection.timestamp.interval.unit’=‘DAYS’,
‘storage.location.template’=‘s3://cloudtrail-awslogs-889399602426-frf8blhu-isengard-do-not-delete/AWSLogs/889399602426/CloudTrail/${region}/${timestamp}’
)
Hi Ying,
Thanks for your answer. I’m using us-east-1 so I guess that’s not the problem… What would you suggest?
Thx a lot
Sorry to hear about that. Could you please share the DDL of the athena table? I can take a look of the DDL to try to figure it out.
Yes, of course:
CREATE EXTERNAL TABLE `cloudtrail_logs_pp`(
`eventversion` string COMMENT 'from deserializer',
`useridentity` struct<type:string,principalid:string,arn:string,accountid:string,invokedby:string,accesskeyid:string,username:string,sessioncontext:struct<attributes:struct<mfaauthenticated:string,creationdate:string>,sessionissuer:struct<type:string,principalid:string,arn:string,accountid:string,username:string>>> COMMENT 'from deserializer',
`eventtime` string COMMENT 'from deserializer',
`eventsource` string COMMENT 'from deserializer',
`eventname` string COMMENT 'from deserializer',
`awsregion` string COMMENT 'from deserializer',
`sourceipaddress` string COMMENT 'from deserializer',
`useragent` string COMMENT 'from deserializer',
`errorcode` string COMMENT 'from deserializer',
`errormessage` string COMMENT 'from deserializer',
`requestparameters` string COMMENT 'from deserializer',
`responseelements` string COMMENT 'from deserializer',
`additionaleventdata` string COMMENT 'from deserializer',
`requestid` string COMMENT 'from deserializer',
`eventid` string COMMENT 'from deserializer',
`resources` array<struct<arn:string,accountid:string,type:string>> COMMENT 'from deserializer',
`eventtype` string COMMENT 'from deserializer',
`apiversion` string COMMENT 'from deserializer',
`readonly` string COMMENT 'from deserializer',
`recipientaccountid` string COMMENT 'from deserializer',
`serviceeventdetails` string COMMENT 'from deserializer',
`sharedeventid` string COMMENT 'from deserializer',
`vpcendpointid` string COMMENT 'from deserializer')
PARTITIONED BY (
`timestamp` string COMMENT 'timestamp')
ROW FORMAT SERDE
'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT
'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://aws-cloudtrail-logs-174249281185-1d74a176/AWSLogs/174249281185/CloudTrail/us-east-1'
TBLPROPERTIES (
'classification'='cloudtrail',
'projection.enabled'='true',
'projection.timestamp.format'='yyyy/MM/dd',
'projection.timestamp.interval'='1',
'projection.timestamp.interval.unit'='DAYS',
'projection.timestamp.range'='2025/07/01,NOW',
'projection.timestamp.type'='date',
'storage.location.template'='s3://aws-cloudtrail-logs-174249281185-1d74a176/AWSLogs/174249281185/CloudTrail/us-east-1/${timestamp}')