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<agentstatus:struct<arn:string,name:string,starttimestamp:string,type:string>,configuration:struct<agenthierarchygroups:string,firstname:string,lastname:string,routingprofile:struct<arn:string,concurrency:array<struct<availableslots:int,channel:string,maximumslots:int>>,defaultoutboundqueue:struct<arn:string,channels:array<string>,name:string>,inboundqueues:array<struct<arn:string,channels:array<string>,name:string>>,name:string>,username:string>,contacts:array<struct<channel:string,connectedtoagenttimestamp:string,contactid:string,initialcontactid:string,initiationmethod:string,queue:struct<arn:string,name:string>,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<agentstatus:struct<arn:string,name:string,starttimestamp:string,type:string>,configuration:struct<agenthierarchygroups:string,firstname:string,lastname:string,routingprofile:struct<arn:string,concurrency:array<struct<availableslots:int,channel:string,maximumslots:int>>,defaultoutboundqueue:struct<arn:string,channels:array<string>,name:string>,inboundqueues:array<struct<arn:string,channels:array<string>,name:string>>,name:string>,username:string>,contacts:array<struct<channel:string,connectedtoagenttimestamp:string,contactid:string,initialcontactid:string,initiationmethod:string,queue:struct<arn:string,name:string>,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"
