AWSTemplateFormatVersion: "2010-09-09" Description: "This is the template for creating a solution to analyze Amazon Connect Agent Events logs using Amazon Athena as described in the blog" Parameters: ProjectName: Type: "String" Default: "test123" Description: Enter an all-lowercase single-word name which is between 3 and 56 chars long. An S3 bucket will be created with this name to store all collected data. MinLength : 3 MaxLength : 42 AllowedPattern : "^[a-z0-9]*$" CreateANewGlueDatabaseAndS3BucketForTheProject: Type: "String" Default: "Use existing AWS Glue database and Amazon S3 data collection bucket created for this project" AllowedValues: - "Create a new AWS Glue database and Amazon S3 data collection bucket for this project" - "Use existing AWS Glue database and Amazon S3 data collection bucket created for this project" Description: Choose whether to create a AWS Glue database and an Amazon S3 bucket for this project. If there is an existing bucket named '{ProjectName}' and a database named '{ProjectName}', then choose to reuse them. Conditions: CreateCommonGlueDatabaseAndDestinationBucket: !Equals - !Ref CreateANewGlueDatabaseAndS3BucketForTheProject - Create a new AWS Glue database and Amazon S3 data collection bucket for this project Resources: DataDestinationBucket: Type: "AWS::S3::Bucket" Condition: CreateCommonGlueDatabaseAndDestinationBucket DeletionPolicy: Retain Properties: BucketName: !Sub "${ProjectName}" GlueDatabase: Type: "AWS::Glue::Database" Condition: CreateCommonGlueDatabaseAndDestinationBucket DependsOn: DataDestinationBucket DeletionPolicy: Retain Properties: DatabaseInput: Name: !Sub "${ProjectName}" CatalogId: !Ref "AWS::AccountId" DbAndBucketCreationWaitHandle: Condition: CreateCommonGlueDatabaseAndDestinationBucket DependsOn: GlueDatabase Type: "AWS::CloudFormation::WaitConditionHandle" WaitHandle: Type: "AWS::CloudFormation::WaitConditionHandle" DbAndBucketWaitCondition: Type: "AWS::CloudFormation::WaitCondition" Properties: Handle: !If [CreateCommonGlueDatabaseAndDestinationBucket, !Ref DbAndBucketCreationWaitHandle, !Ref WaitHandle] Timeout: "1" Count: 0 KinesisStreamAE: Type: "AWS::Kinesis::Stream" DependsOn: "DbAndBucketWaitCondition" Properties: Name: !Sub "${ProjectName}-ae" StreamModeDetails: StreamMode: "ON_DEMAND" KinesisFirehoseServiceRoleForAE: Type: "AWS::IAM::Role" Properties: Path: "/service-role/" RoleName: !Sub "${ProjectName}-KFH-AE" AssumeRolePolicyDocument: !Sub | { "Version":"2012-10-17", "Statement":[ { "Effect":"Allow", "Principal":{"Service":"firehose.amazonaws.com"}, "Action":"sts:AssumeRole" } ] } MaxSessionDuration: 3600 ManagedPolicyArns: - !Ref KinesisFirehoseServicePolicyForAE KinesisFirehoseServicePolicyForAE: Type: "AWS::IAM::ManagedPolicy" DependsOn: GlueTableForAE Properties: ManagedPolicyName: !Sub "${ProjectName}-KFH-AE" Path: "/service-role/" PolicyDocument: !Sub | { "Version": "2012-10-17", "Statement": [ { "Sid": "", "Effect": "Allow", "Action": [ "glue:GetTable", "glue:GetTableVersion", "glue:GetTableVersions" ], "Resource": [ "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:catalog", "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:database/${ProjectName}", "arn:aws:glue:${AWS::Region}:${AWS::AccountId}:table/${ProjectName}/connect_ae" ] }, { "Sid": "", "Effect": "Allow", "Action": [ "s3:AbortMultipartUpload", "s3:GetBucketLocation", "s3:GetObject", "s3:ListBucket", "s3:ListBucketMultipartUploads", "s3:PutObject" ], "Resource": [ "arn:aws:s3:::${ProjectName}", "arn:aws:s3:::${ProjectName}/*" ] }, { "Sid": "", "Effect": "Allow", "Action": [ "logs:PutLogEvents" ], "Resource": [ "arn:aws:logs:${AWS::Region}:${AWS::AccountId}:log-group:/aws/kinesisfirehose/${ProjectName}-ae:log-stream:*" ] }, { "Sid": "", "Effect": "Allow", "Action": [ "kinesis:DescribeStream", "kinesis:GetShardIterator", "kinesis:GetRecords", "kinesis:ListShards" ], "Resource": "arn:aws:kinesis:${AWS::Region}:${AWS::AccountId}:stream/${ProjectName}-ae" } ] } KinesisFirehoseDeliveryStreamForAEFromKinesisStream: Type: "AWS::KinesisFirehose::DeliveryStream" DependsOn: - GlueTableForAE Properties: DeliveryStreamName: !Sub "${ProjectName}-FromDataStream-AE" DeliveryStreamType: "KinesisStreamAsSource" KinesisStreamSourceConfiguration: KinesisStreamARN: !Sub "arn:aws:kinesis:${AWS::Region}:${AWS::AccountId}:stream/${ProjectName}-ae" RoleARN: !GetAtt KinesisFirehoseServiceRoleForAE.Arn ExtendedS3DestinationConfiguration: BucketARN: !Sub "arn:aws:s3:::${ProjectName}" BufferingHints: SizeInMBs: 128 IntervalInSeconds: 60 CloudWatchLoggingOptions: Enabled: true LogGroupName: !Sub "/aws/kinesisfirehose/${ProjectName}" LogStreamName: "S3Delivery" CompressionFormat: "UNCOMPRESSED" DataFormatConversionConfiguration: SchemaConfiguration: RoleARN: !GetAtt KinesisFirehoseServiceRoleForAE.Arn DatabaseName: !Sub "${ProjectName}" TableName: "connect_ae" Region: !Ref AWS::Region VersionId: "LATEST" InputFormatConfiguration: Deserializer: OpenXJsonSerDe: {} OutputFormatConfiguration: Serializer: ParquetSerDe: {} Enabled: true EncryptionConfiguration: NoEncryptionConfig: "NoEncryption" Prefix: "ae-base/year=!{timestamp:YYYY}/month=!{timestamp:MM}/day=!{timestamp:dd}/" ErrorOutputPrefix: "ae-erroroutputbase/!{timestamp:yyy/MM/dd}/!{firehose:random-string}/!{firehose:error-output-type}/" RoleARN: !GetAtt KinesisFirehoseServiceRoleForAE.Arn ProcessingConfiguration: Enabled: false S3BackupMode: "Disabled" GlueTableForAE: Type: "AWS::Glue::Table" DependsOn: "DbAndBucketWaitCondition" Properties: DatabaseName: !Sub "${ProjectName}" CatalogId: !Ref "AWS::AccountId" TableInput: Owner: "hadoop" TableType: "EXTERNAL_TABLE" Parameters: EXTERNAL: "TRUE" "parquet.compression": "SNAPPY" "projection.year.type": "integer" "projection.year.range": "2022,2024" "projection.month.type": "integer" "projection.month.range": "01,12" "projection.month.digits": "2" "projection.day.type": "integer" "projection.day.range": "01,31" "projection.day.digits": "2" "projection.enabled": "true" transient_lastDdlTime: "1628108573" StorageDescriptor: Columns: - Name: "awsaccountid" Type: "string" - Name: "agentarn" Type: "string" - Name: "currentagentsnapshot" Type: "struct,configuration:struct>,defaultoutboundqueue:struct,name:string>,inboundqueues:array,name:string>>,name:string>,username:string>,contacts:array,queuetimestamp:string,state:string,statestarttimestamp:string>>,nextagentstatus:string>" - Name: "eventid" Type: "string" - Name: "eventtimestamp" Type: "string" - Name: "eventtype" Type: "string" - Name: "instancearn" Type: "string" - Name: "previousagentsnapshot" Type: "struct,configuration:struct>,defaultoutboundqueue:struct,name:string>,inboundqueues:array,name:string>>,name:string>,username:string>,contacts:array,queuetimestamp:string,state:string,statestarttimestamp:string>>,nextagentstatus:string>" - Name: "version" Type: "string" Location: !Sub "s3://${ProjectName}/ae-base" InputFormat: "org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat" OutputFormat: "org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat" Compressed: false NumberOfBuckets: -1 SerdeInfo: SerializationLibrary: "org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe" Parameters: "serialization.format": "1" Parameters: {} SkewedInfo: SkewedColumnValueLocationMaps: {} StoredAsSubDirectories: false PartitionKeys: - Name: "year" Type: "int" - Name: "month" Type: "int" - Name: "day" Type: "int" Retention: 0 Name: "connect_ae" ViewForAEAgentStatus: Type: "AWS::Glue::Table" DependsOn: "GlueTableForAE" Properties: DatabaseName: !Sub "${ProjectName}" CatalogId: !Ref "AWS::AccountId" TableInput: ViewOriginalText: !Join - '' - - '/* Presto View: ' - Fn::Base64: Fn::Sub: | {"originalSql":"SELECT\n A.row_num startrownum\n, B.row_num endrownum\n, A.dt calldate\n, A.year\n, A.month\n, A.day\n, A.name name\n, A.Status status\n, A.eventtype\n, CAST(A.eventtimestamp AS timestamp) starttime\n, CAST(B.eventtimestamp AS timestamp) endtime\n, \"date_diff\"('second', CAST(A.eventtimestamp AS timestamp), CAST(B.eventtimestamp AS timestamp)) timespentinstatus\n, \"concat\"(\"concat\"(\"concat\"(\"concat\"(\"lpad\"(CAST((\"date_diff\"('second', CAST(A.eventtimestamp AS timestamp), CAST(B.eventtimestamp AS timestamp)) / 3600) AS varchar), 2, '0'), ':'), \"lpad\"(CAST((\"date_diff\"('second', CAST(A.eventtimestamp AS timestamp), CAST(B.eventtimestamp AS timestamp)) / 60) AS varchar), 2, '0')), ':'), \"lpad\"(CAST((\"date_diff\"('second', CAST(A.eventtimestamp AS timestamp), CAST(B.eventtimestamp AS timestamp)) % 60) AS varchar), 2, '0')) timespentinstatus_hhmmss\nFROM\n (\n SELECT\n \"row_number\"() OVER (ORDER BY currentagentsnapshot.configuration.username ASC, eventtimestamp ASC) row_num\n , CAST(\"date_trunc\"('day', \"from_iso8601_timestamp\"(eventtimestamp)) AS timestamp) dt\n , year\n , month\n , day\n , \"from_iso8601_timestamp\"(eventtimestamp) eventtimestamp\n , currentagentsnapshot.agentstatus.name status\n , eventtype\n , \"concat\"(\"concat\"(currentagentsnapshot.configuration.lastname, ' ,'), currentagentsnapshot.configuration.firstname) name\n FROM\n \"${ProjectName}\".\"connect_ae\"\n WHERE ((eventtype <> 'HEART_BEAT') AND (currentagentsnapshot.configuration.routingprofile.name <> 'Basic Routing Profile'))\n) A\n, (\n SELECT\n \"row_number\"() OVER (ORDER BY currentagentsnapshot.configuration.username ASC, eventtimestamp ASC) row_num\n , CAST(\"date_trunc\"('day', \"from_iso8601_timestamp\"(eventtimestamp)) AS timestamp) dt\n , \"from_iso8601_timestamp\"(eventtimestamp) eventtimestamp\n , currentagentsnapshot.agentstatus.name status\n , eventtype\n , \"concat\"(\"concat\"(currentagentsnapshot.configuration.lastname, ' ,'), currentagentsnapshot.configuration.firstname) name\n FROM\n \"${ProjectName}\".\"connect_ae\"\n WHERE ((eventtype <> 'HEART_BEAT') AND (currentagentsnapshot.configuration.routingprofile.name <> 'Basic Routing Profile'))\n) B\n, (\n SELECT\n \"max\"(row_num) last_row\n , dt\n , name\n FROM\n (\n SELECT\n \"row_number\"() OVER (ORDER BY currentagentsnapshot.configuration.username ASC, eventtimestamp ASC) row_num\n , CAST(\"date_trunc\"('day', \"from_iso8601_timestamp\"(eventtimestamp)) AS timestamp) dt\n , eventtimestamp\n , \"concat\"(\"concat\"(currentagentsnapshot.configuration.lastname, ' ,'), currentagentsnapshot.configuration.firstname) name\n FROM\n \"${ProjectName}\".\"connect_ae\"\n WHERE ((eventtype <> 'HEART_BEAT') AND (currentagentsnapshot.configuration.routingprofile.name <> 'Basic Routing Profile'))\n ) C\n GROUP BY 2, 3\n) LastRow\nWHERE ((((A.dt = B.dt) AND (A.name = B.name)) AND (B.row_num = (A.row_num + 1))) OR ((((((A.dt = LastRow.dt) AND (A.name = LastRow.Name)) AND (A.row_num = LastRow.last_row)) AND (B.dt = LastRow.dt)) AND (B.name = LastRow.Name)) AND (B.row_num >= LastRow.last_row)))\nORDER BY 1 ASC\n","catalog":"awsdatacatalog","schema":"${ProjectName}","columns":[{"name":"startrownum","type":"bigint"},{"name":"endrownum","type":"bigint"},{"name":"calldate","type":"timestamp"},{"name":"year","type":"integer"},{"name":"month","type":"integer"},{"name":"day","type":"integer"},{"name":"name","type":"varchar"},{"name":"status","type":"varchar"},{"name":"eventtype","type":"varchar"},{"name":"starttime","type":"timestamp"},{"name":"endtime","type":"timestamp"},{"name":"timespentinstatus","type":"bigint"},{"name":"timespentinstatus_hhmmss","type":"varchar"}]} - ' */' TableType: "VIRTUAL_VIEW" Parameters: comment: "Presto View" presto_view: "true" ViewExpandedText: "/* Presto View */" StorageDescriptor: Columns: - Name: "startrownum" Type: "bigint" - Name: "endrownum" Type: "bigint" - Name: "calldate" Type: "timestamp" - Name: "year" Type: "int" - Name: "month" Type: "int" - Name: "day" Type: "int" - Name: "name" Type: "string" - Name: "status" Type: "string" - Name: "eventtype" Type: "string" - Name: "starttime" Type: "timestamp" - Name: "endtime" Type: "timestamp" - Name: "timespentinstatus" Type: "bigint" - Name: "timespentinstatus_hhmmss" Type: "string" Location: "" Compressed: false NumberOfBuckets: 0 SerdeInfo: {} StoredAsSubDirectories: false Retention: 0 Name: "connect_ae_agent_status" ViewForAEAgentStatusSummary: Type: "AWS::Glue::Table" DependsOn: "GlueTableForAE" Properties: DatabaseName: !Sub "${ProjectName}" CatalogId: !Ref "AWS::AccountId" TableInput: ViewOriginalText: !Join - '' - - '/* Presto View: ' - Fn::Base64: Fn::Sub: | {"originalSql":"SELECT\n CallDate calldate\n, year\n, month\n, day\n, Name agentname\n, Status agentstatus\n, EventType eventtype\n, \"sum\"(TimeSpentInStatus) statustime\n, \"concat\"(\"concat\"(\"concat\"(\"concat\"(\"lpad\"(CAST((\"sum\"(TimeSpentInStatus) / 3600) AS varchar), 2, '0'), ':'), \"lpad\"(CAST((\"sum\"(TimeSpentInStatus) / 60) AS varchar), 2, '0')), ':'), \"lpad\"(CAST((\"sum\"(TimeSpentInStatus) % 60) AS varchar), 2, '0')) statustime_hhmmss\nFROM\n connect_ae_Agent_Status\nGROUP BY 1, 2, 3, 4, 5, 6, 7\nORDER BY 1 ASC, 2 ASC, 3 ASC, 4 ASC, 5 ASC, 6 ASC, 7 ASC\n","catalog":"awsdatacatalog","schema":"${ProjectName}","columns":[{"name":"calldate","type":"timestamp"},{"name":"year","type":"integer"},{"name":"month","type":"integer"},{"name":"day","type":"integer"},{"name":"agentname","type":"varchar"},{"name":"agentstatus","type":"varchar"},{"name":"eventtype","type":"varchar"},{"name":"statustime","type":"bigint"},{"name":"statustime_hhmmss","type":"varchar"}]} - ' */' TableType: "VIRTUAL_VIEW" Parameters: comment: "Presto View" presto_view: "true" ViewExpandedText: "/* Presto View */" StorageDescriptor: Columns: - Name: "calldate" Type: "timestamp" - Name: "year" Type: "int" - Name: "month" Type: "int" - Name: "day" Type: "int" - Name: "agentname" Type: "string" - Name: "agentstatus" Type: "string" - Name: "eventtype" Type: "string" - Name: "statustime" Type: "bigint" - Name: "statustime_hhmmss" Type: "string" Location: "" Compressed: false NumberOfBuckets: 0 SerdeInfo: {} StoredAsSubDirectories: false Retention: 0 Name: "connect_ae_agent_status_summary" ViewForAEAgentLoginLogout: Type: "AWS::Glue::Table" DependsOn: "GlueTableForAE" Properties: DatabaseName: !Sub "${ProjectName}" CatalogId: !Ref "AWS::AccountId" TableInput: ViewOriginalText: !Join - '' - - '/* Presto View: ' - Fn::Base64: Fn::Sub: | {"originalSql":"SELECT\n LoginDate logindate\n, year\n, month\n, day\n, UserName username\n, AgentName agentname\n, LoginEventType logineventtype\n, CAST(LogineventStamp AS varchar) logineventtimestamp\n, (CASE WHEN (LogoutEventType = LoginEventType) THEN ' ' ELSE CAST(LogoutEventType AS varchar) END) logouteventtype\n, (CASE WHEN (LogoutEventType = LoginEventType) THEN 'NA' ELSE CAST(LogoutEventStamp AS varchar) END) logouteventtimestamp\n, LoggedOnTimeInSecs loggedontimeinsecs\n, \"concat\"(\"concat\"(\"concat\"(\"concat\"(\"lpad\"(CAST((LoggedOnTimeInSecs / 3600) AS varchar), 2, '0'), ':'), \"lpad\"(CAST((LoggedOnTimeInSecs / 60) AS varchar), 2, '0')), ':'), \"lpad\"(CAST((LoggedOnTimeInSecs % 60) AS varchar), 2, '0')) loggedontime_hhmmss\nFROM\n (\n SELECT\n Login.dt LoginDate\n , Login.year year\n , Login.month month\n , Login.day day\n , Login.row_num LoginRowNum\n , Login.agent_username UserName\n , Login.agent_name AgentName\n , Login.et LoginEventType\n , CAST(Login.eventtimestamp AS timestamp) LogineventStamp\n , Logout.row_num LogoutRowNum\n , Logout.et LogoutEventType\n , CAST(Logout.eventtimestamp AS timestamp) LogoutEventStamp\n , \"date_diff\"('second', CAST(Login.eventtimestamp AS timestamp), CAST(Logout.eventtimestamp AS timestamp)) LoggedOnTimeInSecs\n FROM\n (\n SELECT\n \"row_number\"() OVER (ORDER BY currentagentsnapshot.configuration.username ASC, eventtimestamp ASC) row_num\n , CAST(\"date_trunc\"('day', \"from_iso8601_timestamp\"(eventtimestamp)) AS timestamp) dt\n , currentagentsnapshot.configuration.username agent_username\n , \"concat\"(\"concat\"(currentagentsnapshot.configuration.lastname, ', '), currentagentsnapshot.configuration.firstname) agent_name\n , currentagentsnapshot.configuration.routingprofile.name routingprofile\n , eventtype et\n , \"from_iso8601_timestamp\"(eventtimestamp) eventtimestamp\n , year year\n , month month\n , day day\n FROM\n ${ProjectName}.connect_ae\n WHERE ((eventtype IN ('LOGIN', 'LOGOUT')) AND (currentagentsnapshot.configuration.routingprofile.name <> 'Basic Routing Profile'))\n ) Login\n , (\n SELECT\n \"row_number\"() OVER (ORDER BY currentagentsnapshot.configuration.username ASC, eventtimestamp ASC) row_num\n , CAST(\"date_trunc\"('day', \"from_iso8601_timestamp\"(eventtimestamp)) AS timestamp) dt\n , currentagentsnapshot.configuration.username agent_username\n , \"concat\"(\"concat\"(currentagentsnapshot.configuration.lastname, ', '), currentagentsnapshot.configuration.lastname) agent_name\n , currentagentsnapshot.configuration.routingprofile.name routingprofile\n , eventtype et\n , \"from_iso8601_timestamp\"(eventtimestamp) eventtimestamp\n FROM\n ${ProjectName}.connect_ae\n WHERE ((eventtype IN ('LOGIN', 'LOGOUT')) AND (currentagentsnapshot.configuration.routingprofile.name <> 'Basic Routing Profile'))\n ) Logout\n , (\n SELECT\n \"row_number\"() OVER (ORDER BY currentagentsnapshot.configuration.username ASC, eventtimestamp ASC) row_num\n , CAST(\"date_trunc\"('day', \"from_iso8601_timestamp\"(eventtimestamp)) AS timestamp) dt\n , currentagentsnapshot.configuration.username agent_username\n , currentagentsnapshot.configuration.routingprofile.name routingprofile\n , eventtype et\n , \"from_iso8601_timestamp\"(eventtimestamp) eventtimestamp\n FROM\n ${ProjectName}.connect_ae\n WHERE ((eventtype IN ('LOGIN', 'LOGOUT')) AND (currentagentsnapshot.configuration.routingprofile.name <> 'Basic Routing Profile'))\n ) NextRow\n , (\n SELECT \"max\"(row_num) maxrows\n FROM\n (\n SELECT \"row_number\"() OVER (ORDER BY currentagentsnapshot.configuration.username ASC, eventtimestamp ASC) row_num\n FROM\n ${ProjectName}.connect_ae\n WHERE ((eventtype IN ('LOGIN', 'LOGOUT')) AND (currentagentsnapshot.configuration.routingprofile.name <> 'Basic Routing Profile'))\n ) \n ) Rownum\n WHERE ((((Login.dt = Logout.dt) AND (Login.agent_username = Logout.agent_username)) AND ((NextRow.row_num = (Login.row_num + 1)) OR (((Login.row_num = Rownum.Maxrows) AND (Logout.row_num = Rownum.Maxrows)) AND (NextRow.row_num >= Rownum.Maxrows)))) AND (CASE WHEN ((((Login.et = 'LOGIN') AND (NextRow.et = 'LOGOUT')) AND (Login.dt = NextRow.dt)) AND (Login.agent_username = NextRow.agent_username)) THEN (Logout.row_num = (Login.row_num + 1)) ELSE (CASE WHEN (Login.et <> 'LOGOUT') THEN (Logout.row_num = Login.row_num) END) END))\n) \n","catalog":"awsdatacatalog","schema":"${ProjectName}","columns":[{"name":"logindate","type":"timestamp"},{"name":"year","type":"integer"},{"name":"month","type":"integer"},{"name":"day","type":"integer"},{"name":"username","type":"varchar"},{"name":"agentname","type":"varchar"},{"name":"logineventtype","type":"varchar"},{"name":"logineventtimestamp","type":"varchar"},{"name":"logouteventtype","type":"varchar"},{"name":"logouteventtimestamp","type":"varchar"},{"name":"loggedontimeinsecs","type":"bigint"},{"name":"loggedontime_hhmmss","type":"varchar"}]} - ' */' TableType: "VIRTUAL_VIEW" Parameters: comment: "Presto View" presto_view: "true" ViewExpandedText: "/* Presto View */" StorageDescriptor: Columns: - Name: "logindate" Type: "timestamp" - Name: "year" Type: "int" - Name: "month" Type: "int" - Name: "day" Type: "int" - Name: "username" Type: "string" - Name: "agentname" Type: "string" - Name: "logineventtype" Type: "string" - Name: "logineventtimestamp" Type: "string" - Name: "logouteventtype" Type: "string" - Name: "logouteventtimestamp" Type: "string" - Name: "loggedontimeinsecs" Type: "bigint" - Name: "loggedontime_hhmmss" Type: "string" Location: "" Compressed: false NumberOfBuckets: 0 SerdeInfo: {} StoredAsSubDirectories: false Retention: 0 Name: "connect_ae_login_logout"