AWS Code Sample
Catalog

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

athena-sample-queries-appstream-usage-data_template.yml

athena-sample-queries-appstream-usage-data-template.yml provides a CloudFormation template that creates and saves various sample queries in your Amazon Athena account. These queries help you analyze Amazon AppStream 2.0 usage reports data.

# Copyright 2010-2019 Amazon.com, Inc. or its affiliates. All Rights Reserved. # # This file is licensed under the Apache License, Version 2.0 (the "License"). # You may not use this file except in compliance with the License. A copy of # the License is located at # # http://aws.amazon.com/apache2.0/ # # This file is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR # CONDITIONS OF ANY KIND, either express or implied. See the License for the # specific language governing permissions and limitations under the License. AWSTemplateFormatVersion: '2010-09-09' Description: "Sample Amazon Athena queries to analyze Amazon AppStream 2.0 usage reports data" Resources: AverageSessionLengthCurrentMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "Average AppStream 2.0 session length per stack in current month" Name: AS2_avg_session_length_curr_mo QueryString: "-- Average session length per stack for AppStream 2.0 user sessions launched during the current month -- Each session duration rounded up to whole minute and whole hour before being averaged SELECT stack_name, ROUND(AVG(CEIL(CAST(session_duration_in_seconds AS DOUBLE) / 60)), 2) AS avg_session_length_in_minutes, ROUND(AVG(CEIL(CAST(session_duration_in_seconds AS DOUBLE) / 3600)), 2) AS avg_session_length_in_hours FROM \"appstream-usage\".\"sessions\" WHERE year = DATE_FORMAT(current_date, '%Y') AND month = DATE_FORMAT(current_date, '%m') AND session_start_time >= DATE_FORMAT(current_date, '%Y-%m-01') GROUP BY stack_name ORDER BY avg_session_length_in_minutes DESC;" AverageSessionLengthPreviousMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "Average AppStream 2.0 session length per stack in previous month" Name: AS2_avg_session_length_prev_mo QueryString: "-- Average session length per stack for AppStream 2.0 user sessions launched during the previous month -- Each session duration rounded up to whole minute and whole hour before being averaged SELECT stack_name, ROUND(AVG(CEIL(CAST(session_duration_in_seconds AS DOUBLE) / 60)), 2) AS avg_session_length_in_minutes, ROUND(AVG(CEIL(CAST(session_duration_in_seconds AS DOUBLE) / 3600)), 2) AS avg_session_length_in_hours FROM \"appstream-usage\".\"sessions\" WHERE ((year = DATE_FORMAT(date_add('month', -1, current_date), '%Y') AND month = DATE_FORMAT(date_add('month', -1, current_date), '%m')) OR (year = DATE_FORMAT(current_date, '%Y') AND month = DATE_FORMAT(current_date, '%m') AND day <= '05')) AND session_start_time >= DATE_FORMAT(date_add('month', -1, current_date), '%Y-%m-01') AND session_start_time < DATE_FORMAT(current_date, '%Y-%m-01') GROUP BY stack_name ORDER BY avg_session_length_in_minutes DESC;" DailySessionsCurrentMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "AppStream 2.0 sessions per day in current month" Name: AS2_daily_sessions_curr_mo QueryString: "-- Number of AppStream 2.0 sessions per day for the current month SELECT SUBSTRING(session_start_time, 1, 10) AS report_date, COUNT(DISTINCT user_session_id) AS num_sessions FROM \"appstream-usage\".\"sessions\" WHERE year = DATE_FORMAT(current_date, '%Y') AND month = DATE_FORMAT(current_date, '%m') AND session_start_time >= DATE_FORMAT(current_date, '%Y-%m-01') GROUP BY 1 ORDER BY 1" DailySessionsPreviousMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "AppStream 2.0 sessions per day in previous month" Name: AS2_daily_sessions_prev_mo QueryString: "-- Number of AppStream 2.0 sessions per day for the previous month SELECT SUBSTRING(session_start_time, 1, 10) AS report_date, COUNT(DISTINCT user_session_id) AS num_sessions FROM \"appstream-usage\".\"sessions\" WHERE ((year = DATE_FORMAT(date_add('month', -1, current_date), '%Y') AND month = DATE_FORMAT(date_add('month', -1, current_date), '%m')) OR (year = DATE_FORMAT(current_date, '%Y') AND month = DATE_FORMAT(current_date, '%m') AND day <= '05')) AND session_start_time >= DATE_FORMAT(date_add('month', -1, current_date), '%Y-%m-01') AND session_start_time < DATE_FORMAT(current_date, '%Y-%m-01') GROUP BY 1 ORDER BY 1" AllAppStreamSessionsCurrentMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "All AppStream 2.0 sessions from current month" Name: AS2_sessions_curr_mo QueryString: "-- AppStream 2.0 sessions monthly report: all user sessions launched during the current month SELECT * FROM \"appstream-usage\".\"sessions\" WHERE year = DATE_FORMAT(current_date, '%Y') AND month = DATE_FORMAT(current_date, '%m') AND session_start_time >= DATE_FORMAT(current_date, '%Y-%m-01') ORDER BY session_start_time;" AllAppStreamSessionsPreviousMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "All AppStream 2.0 sessions from previous month" Name: AS2_sessions_prev_mo QueryString: "-- AppStream 2.0 sessions monthly report: all user sessions launched during the previous month SELECT * FROM \"appstream-usage\".\"sessions\" WHERE ((year = DATE_FORMAT(date_add('month', -1, current_date), '%Y') AND month = DATE_FORMAT(date_add('month', -1, current_date), '%m')) OR (year = DATE_FORMAT(current_date, '%Y') AND month = DATE_FORMAT(current_date, '%m') AND day <= '05')) AND session_start_time >= DATE_FORMAT(date_add('month', -1, current_date), '%Y-%m-01') AND session_start_time < DATE_FORMAT(current_date, '%Y-%m-01') ORDER BY session_start_time;" StreamingHoursPerUserCurrentMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "AppStream 2.0 streaming hours per user in current month" Name: AS2_users_curr_mo QueryString: "-- AppStream 2.0 streaming hours per user in current month -- Each session duration rounded up to whole hour SELECT authentication_type_user_id AS user, SUM(CEIL(CAST(session_duration_in_seconds AS DOUBLE) / 3600)) AS streaming_hours FROM \"appstream-usage\".\"sessions\" WHERE year = DATE_FORMAT(current_date, '%Y') AND month = DATE_FORMAT(current_date, '%m') AND session_start_time >= DATE_FORMAT(current_date, '%Y-%m-01') GROUP BY 1 ORDER BY 2 DESC;" StreamingHoursPerUserPreviousMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "AppStream 2.0 streaming hours per user in previous month" Name: AS2_users_prev_mo QueryString: "-- AppStream 2.0 streaming hours per user in previous month -- Each session duration rounded up to whole hour SELECT authentication_type_user_id AS user, SUM(CEIL(CAST(session_duration_in_seconds AS DOUBLE) / 3600)) AS streaming_hours FROM \"appstream-usage\".\"sessions\" WHERE ((year = DATE_FORMAT(date_add('month', -1, current_date), '%Y') AND month = DATE_FORMAT(date_add('month', -1, current_date), '%m')) OR (year = DATE_FORMAT(current_date, '%Y') AND month = DATE_FORMAT(current_date, '%m') AND day <= '05')) AND session_start_time >= DATE_FORMAT(date_add('month', -1, current_date), '%Y-%m-01') AND session_start_time < DATE_FORMAT(current_date, '%Y-%m-01') GROUP BY 1 ORDER BY 2 DESC;" UsersPerApplicationCurrentMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "Users per AppStream 2.0 app in current month" Name: AS2_users_per_app_curr_mo QueryString: "-- Users per AppStream 2.0 app in current month SELECT DISTINCT apps.application_name, sessions.authentication_type_user_id FROM \"appstream-usage\".\"applications\" apps INNER JOIN \"appstream-usage\".\"sessions\" sessions ON ( apps.user_session_id = sessions.user_session_id AND apps.year = DATE_FORMAT(current_date, '%Y') AND apps.month = DATE_FORMAT(current_date, '%m')) WHERE sessions.year = DATE_FORMAT(current_date, '%Y') AND sessions.month = DATE_FORMAT(current_date, '%m') AND sessions.session_start_time >= DATE_FORMAT(current_date, '%Y-%m-01') ORDER BY apps.application_name" UsersPerApplicationPreviousMonth: Type: AWS::Athena::NamedQuery Properties: Database: appstream-usage Description: "Users per AppStream 2.0 app in previous month" Name: AS2_users_per_app_prev_mo QueryString: "-- Users per AppStream 2.0 app in previous month SELECT DISTINCT apps.application_name, sessions.authentication_type_user_id FROM \"appstream-usage\".\"applications\" apps INNER JOIN \"appstream-usage\".\"sessions\" sessions ON ( apps.user_session_id = sessions.user_session_id AND ((apps.year = DATE_FORMAT(date_add('month', -1, current_date), '%Y') AND apps.month = DATE_FORMAT(date_add('month', -1, current_date), '%m')) OR (apps.year = DATE_FORMAT(current_date, '%Y') AND apps.month = DATE_FORMAT(current_date, '%m') AND apps.day <= '05'))) WHERE ((sessions.year = DATE_FORMAT(date_add('month', -1, current_date), '%Y') AND sessions.month = DATE_FORMAT(date_add('month', -1, current_date), '%m')) OR (sessions.year = DATE_FORMAT(current_date, '%Y') AND sessions.month = DATE_FORMAT(current_date, '%m') AND sessions.day <= '05')) AND sessions.session_start_time >= DATE_FORMAT(date_add('month', -1, current_date), '%Y-%m-01') AND sessions.session_start_time < DATE_FORMAT(current_date, '%Y-%m-01') ORDER BY apps.application_name"

Sample Details

Service: appstream2

Last tested: 2019-05-17

Author: AWS

Type: full-example

On this page: