# Using the metric query editor The metric query editor allows you to build two types of queries - **Metric Search** and **Metric Query**. The **Metric Query** option queries data using CloudWatch Metrics Insights. ## Common query editor fields There are three fields that are common to both **Metric Search** and **Metric Query** modes. **Common fields** **Id** The `GetMetricData` API requires that all queries have a unique ID. Use this field to specify an ID of choice. The ID can include numbers, letters, and underscores, and must start with a lowercase letter. If no ID is specified, Amazon Managed Grafana generates an ID using the following pattern: `query[refId of the current query row]`. For example, `queryA` represents the first query row in the panel editor. **Period** A period is the length of time associated with a specific CloudWatch statistic. Periods are defined in numbers of seconds. Valid values include 1, 5, 10, 30, or any multiple of 60. If you keep the period field blank or set to `auto`, then it calculates automatically based on the time range and the CloudWatch retention policy. The formula used is `time range in seconds / 2000`, and then it moves on to the next higher value in an array of predefined periods [60, 300, 900, 3600, 21600, 86400] after removing periods based on retention. To see which period Amazon Managed Grafana is using, choose **Show Query Preview** in the query editor. **Alias** The following alias patterns apply. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/CloudWatch-using-the-metric-query-editor.html) # Using the metric search option To create a valid query in **Metric Search**, you must specify the namespace, metric name and at least one statistic. If **Match Exact** is turned on, you must also specify all dimensions of the metric that you’re querying. The metrics schema must match exactly. For more information, see [CloudWatch search expression syntax ](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/search-expression-syntax.html). If **Match Exact** is turned off, you can specify any number of dimensions by which you want to filter. Up to 100 metrics matching your filter criteria are returned. ## Dynamic queries using dimension wildcard characters You can monitor a dynamic list of metrics by using the asterisk (`*`) wildcard character for one or more dimension values. This helps you monitor metrics for AWS resources, such as EC2 instances or containers. For example, when new instances get created as part of an auto scaling event, they will automatically appear in the graph without you having to track the new instance IDs. This capability is currently limited to retrieving up to 100 metrics. You can choose **Show Query Preview** to see the search expression that is automatically built to support wildcard characters. By default, the search expression is defined in such a way that the queried metrics must match the defined dimension names exactly. This means that in the example only metrics with exactly one dimension with name `InstanceId` are returned. To include metrics that have other dimensions defined, you can turn off **Match Exact**. Turning off **Match Exact** also creates a search expression even if you don’t use wildcard characters. Grafana searches for any metric that matches at least the namespace, metric name, and all defined dimensions. ## Multi-value template variables When defining dimension values based on multi-valued template variables, a search expression is used to query for the matching metrics. This enables the use of multiple template variables in one query. You can also use template variables for queries that have the **Match Exact** option turned off. Search expressions are currently limited to 1024 characters, so your query might fail if you have a long list of values. If you want to query all metrics that have any value for a certain dimension name, we recommend using the asterisk (`*`) wildcard character instead of the `All` option. The use of multi-valued template variables is only supported for dimension values. Using multi-valued template variables for `Region`, `Namespace`, or `Metric Name` is not supported. ## Metric math expressions You can create new time series metrics by operating on top of CloudWatch metrics using mathematical functions. Arithmetic operators, unary subtraction, and other functions are supported and can be applied to CloudWatch metrics. For more information about CloudWatch metric math functions, see [Using metric math](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/using-metric-math.html). As an example, to apply arithmetic operations on a metric, give an ID (a unique string) to the raw metric. You can then use this ID and apply arithmetic operations to it in the `Expression` field of the new metric. If you use the `Expression` field to reference another query, such as `queryA * 2`, you cannot create an alert rule based on that query. ## Period A period is the length of time associated with a specific Amazon CloudWatch statistic. Periods are defined in numbers of seconds. Valid values include 1, 5, 10, 30, or any multiple of 60. If you keep the period field blank or set to **auto**, then it calculates automatically based on the time range. The formula used is `time range in seconds / 2000`, and then it moves on to the next higher value in an array of predefined periods `[60, 300, 900, 3600, 21600, 86400]`. To see which period Amazon Managed Grafana is using, choose **Show Query Preview** in the query editor. ## Deep linking from Grafana panels to the CloudWatch console Choosing a time series in the panel shows a context menu with a link to **View in CloudWatch console**. Choosing that link opens a new tab that takes you to the CloudWatch console and displays all the metrics for that query. If you’re not currently signed in to the CloudWatch console, the link forwards you to the sign-in page. The provided link is valid for any AWS account but only displays the correct metrics if you’re signed in to the AWS account that corresponds to the selected data source in Grafana. This feature is not available for metrics that are based on metric math expressions. # Using the metric query option to query CloudWatch Metrics Insights data **Note** Amazon CloudWatch Metrics Insights is in preview. CloudWatch Metrics Insights features are open to all AWS accounts. Features might be subject to change. You can query CloudWatch Metrics Insights data by choosing the `metric query` mode in the **Metric query editor**. CloudWatch Metrics Insights is a powerful high-performance SQL query engine that you can use to query your metrics at scale. It is a fast, flexible, SQL-based query engine that you can use to identify trends and patterns within all of your CloudWatch metrics in real time. It uses a dialect of SQL. For more information about the Metrics Insights query syntax, see [Query syntax and keywords](#metrics-insights-syntax-keywords). ## Query syntax and keywords CloudWatch Metrics Insights uses a dialect of SQL. The following example shows the query syntax. ``` SELECT FUNCTION(metricName) FROM namespace | [ SCHEMA(namespace[, labelKey [, ...] ]) ] [ WHERE labelKey OPERATOR labelValue [AND|OR|([...])*] [, ...] ] [ GROUP BY labelKey [, ...]] [ ORDER BY FUNCTION() [DESC | ASC] ] [ LIMIT number] ``` Keywords are not case-sensitive, but the identifiers are case-sensitive. Identifiers include the names of metrics, namespaces, and dimensions. The following table provides the query keywords and their descriptions. | Keyword | Description | | --- | --- | | FUNCTION | Required. Specifies the aggregate function to use, and also specifies the name of the metric to query. Valid values are AVG, COUNT, MAX, MIN, and SUM. | | MetricName | Required. For example, CPUUtilization. | | FROM | Required. Specifies the source of the metric. You can specify either the metric namespace that contains the metric to query, or a SCHEMA table function. Some namespace examples are AWS/EC2 and AWS/Lambda. | | SCHEMA | (Optional) Filters the query results to show only the metrics that are an exact match, or the metrics that do not match. | | WHERE | (Optional) Filters the results to show only the metrics that match your specified expression. For example, WHERE InstanceType \$1= 'c3.4xlarge'. | | GROUP BY | (Optional) Groups the query results into multiple time series. For example, GROUP BY ServiceName. | | ORDER BY | (Optional) Specifies the order of time series to return. Options are ASC and DESC. | | LIMIT | (Optional) Limits the number of time series to return. | The following are some examples: + ``` SELECT AVG(CPUUtilization) FROM "AWS/EC2" ``` Matches all `CPUUtilization` metrics in the `AWS/EC2` namespace, ignoring their dimensions, and returns a single aggregated time series. + ``` SELECT AVG(CPUUtilization) FROM SCHEMA("AWS/EC2") ``` Matches only the `CPUUtilization` metrics in the `AWS/EC2` namespace that do not have any dimensions defined. + ``` SELECT AVG(CPUUtilization) FROM SCHEMA("AWS/EC2", InstanceId) ``` Matches only the `CPUUtilization`metrics that were reported to CloudWatch with exactly one dimension, `InstanceId`. + ``` SELECT SUM(RequestCount) FROM SCHEMA("AWS/ApplicationELB", LoadBalancer, AvailabilityZone) ``` Mtches only the `RequestCount` metrics that were reported to CloudWatch from `AWS/ApplicationELB` with exactly two dimensions, `LoadBalancer`and `AvailabilityZone`. Label values must be enclosed by single quotation marks. ### Escape characters In a query, label values must always be surrounded with single quotation marks.   For example, `SELECT MAX(CPUUtilization) FROM "AWS/EC2" WHERE AutoScalingGroupName = 'my-production-fleet'`. Metric namespaces, metric names, and label keys that contain characters other than letters, numbers, and underscores (`_`) must be surrounded by double quotation marks. For example, `SELECT MAX("My.Metric")`. If one of these contains a double quotation mark itself (such as `Bytes"Input"`), you must escape that double quotation mark with backslashes, as in `SELECT AVG("Bytes\"Input\"")`. If a metric namespace, metric name, or label key, contains a word that is a reserved keyword in Metrics Insights, these must also be enclosed in double quotation marks. For example, if you have a metric named `LIMIT`, you would use `SELECT AVG("LIMIT")`. It is also valid to enclose any namespace, metric name, or label in double quotation marks even if it does not include a reserved keyword. ## Builder mode and code mode You can create a query in `Builder` mode or `Code` mode. **To create a query in `Builder` mode** 1. Browse and select a metric namespace, metric name, filter, group, and order options using information from the preceding table. 1. For each of these options, choose from the list of possible options. **To create a query in `Code` mode** 1. Write your query in the code editor. 1. To run the query, choose **Run query **in the code editor. To create a query in the `builder` mode: + Browse and select a metric namespace, metric name, filter, group, and order options using information from the table above. + For each of these options, choose from the list of possible options. Grafana automatically constructs a SQL query based on your selections. To create a query in the `code` mode: + Write your query in the code editor. + To run the query, choose the **Run query **on the code editor. The code editor has a built-in autocomplete feature that gives suggestions for keywords, aggregations, namespaces, metrics, labels, and label values. The suggestions are shown when you enter a space, comma, or dollar sign. You can also use the keyboard combination `CTRL+Space`. Code editor can autocomplete the query. However, use of template variables in the code editor might interfere with the autocompletion. ## CloudWatch Metrics Insights examples **Note** CloudWatch Metrics Insights is in open preview. The preview is open to all AWS accounts and you do not need to request access. Features might be added or changed before announcing General Availability. This section contains examples of useful CloudWatch Metrics Insights queries that you can copy and use directly or copy and modify in query editor. Some of these examples are already available in the console, and you can access them by choosing **Add query** in the **Metrics** view. ### EC2 examples View CPU utilization per instance metrics ``` SELECT AVG(CPUUtilization) FROM "AWS/EC2" GROUP BY InstanceId ``` View the average CPU utilization across the entire fleet ``` SELECT AVG(CPUUtilization) FROM SCHEMA("AWS/EC2", InstanceId) ``` View the 10 instances with the highest average CPU utilization ``` SELECT MAX(CPUUtilization) FROM "AWS/EC2" GROUP BY InstanceId LIMIT 10 ``` View the 10 instances with the highest CPU utilization, ordered by the maximum, in descending order ``` SELECT AVG(CPUUtilization) FROM "AWS/EC2" GROUP BY InstanceId ORDER BY MAX() DESC LIMIT 10 ``` In this case, the CloudWatch agent is collecting a CPUUtilization metric per application. This query filters the average of this metric for a specific application name. ``` SELECT AVG(CPUUtilization) FROM "AWS/CWAgent" WHERE ApplicationName = 'eCommerce' SELECT AVG(ConcurrentExecutions) FROM "AWS/Lambda" ``` View average execution time for the top 10 Lambda functions, ordered by the maximum, in descending order ``` SELECT AVG(Duration) FROM "AWS/Lambda" GROUP BY FunctionName ORDER BY MAX() DESC LIMIT 10 ``` View the maximum, average, and minimum of Lambda execution times ``` SELECT MAX(Duration) FROM "AWS/Lambda" ``` ### Application Load Balancer examples View metrics that have the dimensions **LoadBalancer** and **AvailabilityZone** ``` SELECT SUM(RequestCount) FROM SCHEMA("AWS/ApplicationELB", LoadBalancer, AvailabilityZone) ``` View metrics with number of active concurrent TCP connections ``` SELECT AVG(ActiveConnectionCount) FROM "AWS/ApplicationELB" ``` ### Amazon EBS examples View top 10 average write bytes per volume in descending order ``` SELECT AVG(VolumeWriteBytes) FROM "AWS/EBS" GROUP BY VolumeId ORDER BY MAX() DESC LIMIT 10 ``` View average Amazon EBS volume write time ``` SELECT AVG(VolumeTotalWriteTime) FROM "AWS/EBS" ``` View average Amazon EBS volume idle time ``` SELECT AVG(VolumeIdleTime) FROM "AWS/EBS" View average burst balance per volume SELECT AVG(BurstBalance) FROM "AWS/EBS" GROUP BY VolumeId View average read bytes across Amazon EBS volumes SELECT AVG(VolumeReadBytes) FROM "AWS/EBS" ``` View average write bytes across Amazon EBS volumes ``` SELECT AVG(VolumeWriteBytes) FROM "AWS/EBS" ``` ### Amazon Simple Storage Service examples View average latency group by bucket name ``` SELECT AVG(TotalRequestLatency) FROM "AWS/S3" GROUP BY BucketName ``` View average number of objects per bucket across all Amazon S3 buckets ``` SELECT AVG(NumberOfObjects) FROM "AWS/S3" GROUP BY BucketName ``` ### Amazon Simple Notification Service examples Amazon-simple-notificaation-service-examples ``` SELECT AVG(NumberOfMessagesPublished) FROM "AWS/SNS" ``` View average number of messages failed for each topic name ``` SELECT AVG(NumberOfNotificationsFailed) FROM "AWS/SNS" GROUP BY TopicName ``` ### AWS API usage examples View top 20 AWS APIs by the number of calls in your account ``` SELECT COUNT(CallCount) FROM "AWS/Usage" WHERE "Type" = 'API' GROUP BY "Service", "Resource" ORDER BY SUM() DESC LIMIT 20 ``` ## CloudWatch Metrics Insights limits CloudWatch Metrics Insights currently has the following limits: + You can query only the most recent three hours of data. + A single query can process no more than 10,000 metrics. This means that if the `SELECT`, `FROM`, and `WHERE` clauses would match more than 10,000 metrics, only the first 10,000 of these metrics that are found will be processed by the query. + A single query can return no more than 500 time series. This means that if the query is processing more than 500 metrics, not all metrics will be returned in the query results. If you use an `ORDER BY` clause, then all the metrics being processed will be sorted and the 500 that have the highest or lowest values according to your `ORDER BY` clause will be returned. If you do not include an `ORDER BY` clause, you can't control which 500 matching metrics are returned. + Each `GetMetricData` operation can have only one query, but you can have multiple widgets in a dashboard that each include a query.