View AWS Trusted Advisor recommendations at scale with AWS Organizations

Since 2014, AWS Trusted Advisor has been providing customers with visibility into an individual AWS account and providing recommendations based on known AWS best practices. Trusted Advisor makes recommendations to help customers achieve a better security posture, control their costs, optimize application performance, design better fault tolerance, and maintain control over their AWS service limits (now known as Service Quotas). The value of these recommendations has increased over time, and now customers must consider new blind spots which may be introduced into their AWS environments.

The AWS Trusted Advisor checks available to a customer are based upon their subscribed AWS Support level. For example, AWS Premium Support customers (Business and Enterprise Support) can benefit from the full portfolio of checks. With this announcement of Trusted Advisor, AWS Premium Support customers will now benefit from an Organizational View for Trusted Advisor! This will provide administrators with a unified view of all AWS Trusted Advisor recommendations within their AWS Organization.

As an AWS customer you have likely adopted a multi-account strategy using AWS Organizations to centrally manage and govern your environment across accounts. By viewing a consolidated Trusted Advisor report, you can optimize for a stronger security posture, better performing applications, and more cost-efficient setup across all of your multi-account AWS environment. This post will share initial guidelines on how to use Organizational View for AWS Trusted Advisor to view status across all the accounts within an AWS Organization.

How do you use the Trusted Advisor reports?

The Trusted Advisor Report consists of aggregated Trusted Advisor results across all of your accounts with Premium Support enabled. Organizational view for AWS Trusted Advisor is the guide to get started with the feature. The results include a line-by-line detail entry for each of the Trusted Advisor checks. This report is available in multiple file formats, CSV and JSON.

Examples using the reports

Example analysis of a CSV using Spreadsheet

Example 1 – The following is an example analysis based on a pivot table generated from a CSV file. The example only includes items with ‘Red’ or ‘Yellow’ status, as these are actionable items.

CSV Example 1 – Actionable items

Example 2 – Using the previous example, the following screenshot shows the data filtered to identify cost optimization opportunities.

CSV Example 2 – Cost Optimization Opportunities

Example analysis of JSONs with SQL Queries using Amazon Athena

Amazon Athena provides a SQL interface, which lets you use advanced analytics capabilities to join multiple data sets. Let’s try using multiple JSON files with Amazon Athena.

Pre-requisites

In order to proceed with this sections, it is recommended that you follow the instructions described on the documentation page using other AWS services to view Trusted Advisor reports.

Note: The examples use the format <Database_Name>.<Table_name>. You can replace these values with "athenatacfn"."folder1" if you edit the YAML only with the specified changes.

Let’s explore some example use cases for organization-wide visibility.

Example 1 – Security

End goal: Get a list of all the accounts and regions with unrestricted access to port 20, 21, 1433, 1434, 3306, 3389, 4333, 5432, or 5500. This is flagged as part of the Trusted Advisor check with the ID ‘HCP4007jGY’, which is ‘Security Groups – Specific Ports Unrestricted’.

The sample Amazon Athena query would look like the following:

SELECT distinct("security group id"),
         accountid,
         region
FROM <Database_Name>.<Table_name>
WHERE checkid='HCP4007jGY'
        AND status!='Green'
        AND status!='Yellow' limit 10;

Follow up action: After the list is obtained, Trusted Advisor recommends that you evaluate the rules to enforce the least permissive rules.

Example 2 – Cost optimization

End goal: Find the potential savings of deleting all idle load balancers. Deleting unused resources is often the easiest way to start a cost optimization exercise; this example identifies such opportunities for load balancers. This is flagged as part of the Trusted Advisor check with the ID ‘hjLMh88uM8’, which is ‘Idle Load Balancers’.

The sample Amazon Athena query would look like the following:

SELECT *
FROM <Database_Name>.<Table_name>
WHERE checkid='hjLMh88uM8'
        AND status!='Green' limit 10;

Follow up action: After the list is obtained, Trusted Advisor recommends that you consider deleting load balancers based on the best-anticipated future requirements.

Example 3 – Performance

End goal: Get the list of all the Route 53 alias resource record sets. With a multi-account strategy, there may be situations where cross-account Route 53 aliases are missing. This example lets you identify the performance impact. This is flagged as part of the Trusted Advisor check with the ID ‘B913Ef6fb4’, which is ‘Amazon Route 53 Alias Resource Record Sets’.

The sample Amazon Athena query would look like the following:

SELECT *
FROM <Database_Name>.<Table_name>
WHERE checkid='B913Ef6fb4'
        AND status!='Green' limit 10;

Follow up action: After the list is obtained, Trusted Advisor recommends that you update relevant Route 53 records from CNAME to alias resource records mapping.

Example 4 – Fault Tolerance

End goal: Get the list of all VPN connections where two tunnels are not active. Often customers have VPN connections terminated in a VPC. However, one of the two tunnels remains down and might go unnoticed. This can have implications for workloads if there is a planned maintenance or outage on the active tunnel. This is flagged as part of the Trusted Advisor check with the ID ‘S45wrEXrLz’, which is ‘VPN Tunnel Redundancy’.

The sample Amazon Athena query would look like the following:

SELECT *
FROM <Database_Name>.<Table_name>
WHERE checkid='S45wrEXrLz'
        AND status!='Green' limit 10;

Follow up action: After the list is obtained, Trusted Advisor recommends ensuring that each of the VPN connections has two active tunnels.

Example 5 – Service Limits

End goal: Get the list of accounts where AWS IAM roles are up to 80% of the service limit. When operating a workload spread across multiple accounts, it can be important to keep track of service limits (now known as Service Quotas). This is flagged as part of the Trusted Advisor check with the ID 'oQ7TT0l7J9', which is ‘Service Limit: IAM – Roles’.

The sample Amazon Athena query would look like the following:

SELECT distinct(accountid)
FROM <Database_Name>.<Table_name>
WHERE checkid='oQ7TT0l7J9'
        AND status!='Green'
        AND status!='Blue' limit 10;

Follow up action: After the list is obtained, Trusted Advisor recommends that you request a limit increase based on the best-anticipated future demands.

Example Dashboard of JSONs with Amazon Athena and Amazon QuickSight

Amazon QuickSight provides advanced dashboarding capabilities, including ML Insights. With ML Insights, Amazon QuickSight allows you to forecast, detect anomalies, identify key movers, and embed narratives to tell the story of your data in plain language.

The following is an example Amazon QuickSight dashboard that can be built with the Amazon Athena shown previously. This is based on the approach described on the documentation page using other AWS services to view Trusted Advisor reports. This could be used as part of regular continuous improvement exercises. This dashboard has visibility into multiple AWS accounts under an AWS Organization across multiple regions. Amazon QuickSight also supports Mobile app for iOS and Android devices.

Example Amazon QuickSight Dashboard

The following is an example of an Amazon QuickSight dashboard that can be built using ML Insights after gathering data for several months.

Example Dashboard – ML Insights

The following graphic shows how we used Organizational view for Trusted Advisor to create a report and view that data using other AWS services.

Architectural Diagram for Organizational View for AWS Trusted Advisor

Conclusion

In this post, we showed how to use different AWS services to process Trusted Advisor Organizational reports and gather insights. We also looked into SQL queries and dashboarding capabilities for each of the AWS Trusted Advisor pillars across an entire AWS Organization.

To learn more about AWS Trusted Advisor, see the product documentation. To learn more about AWS Organizational integrations, see the product documentation.

About the author

Saurabh Bangad is an enthusiastic millennial who has Master of Computer Science from The University of Texas, Dallas.

Having completed over five years of being an Amazonian, Saurabh is currently a Sr. Technical Account Manager supporting Global Financial Services customers. Prior to this role, he also worked at Oracle, HP, and LinkedIn.