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}')
Hi @cascjoaq
To build an admin dashboard, please refer to the documentation below, it might be helpful for you.
Hi @cascjoaq
It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.
If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.
Thank you!
Hi,
Yes, I shared my DDL but I didn’t get an answer. I also tried to fix this problem by using the link you shared with me but it was actually the link that I also used to setup the dashboard, and I couldn’t find more insights to fix my problem.
I still have the datasets incomplete so I can;t use the dashboard
Hi @cascjoaq
I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!