{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":31454,"path":"it-operations-management/service-observability/advanced-query-support.md","title":"Advanced query support for AWS and Azure","bundle":null,"doc_type":null,"release":"australia","body":"\n# Advanced query support for AWS and Azure\n\nUnderstand how to create advanced queries for Service Observability dashboard charts.\n\nAdvanced queries in Service Observability support most vendor-specific query languages. Simply copy a query from the observability chart and paste it into the Advanced query field in Service Observability. However, queries to AWS and Azure must be JSON-formatted and follow the guidelines outlined in this topic.\n\n**Note:** As of version 1.10, you can import charts directly from AWS and Azure. See [Edit data charts on Service Observability dashboard templates](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/it-operations-management/service-observability/edit-service-observability-dashboards.md) for more information.\n\n## AWS advanced queries\n\nData from AWS is accessed using the `GetMetricData` API. Queries can be either a direct request in JSON format to that API or they can be in the form of a `DashboardBody` metric widget. For example, you can use the widget representation for a chart that exists on an AWS CloudWatch dashboard.\n\n## Direct GetMetricData request\n\nThe following example shows a `GetMetricData` JSON query for a chart that displays the average CPU Utilization for an ELB instance.\n\n```\n{ \n\n    \"StartTime\": 1637061900, // can be replaced with “${START}” \n    \"EndTime\": 1637074500, // can be replaced with “${END}” \n    \"MetricDataQueries\": [ \n        { \n            \"Expression\": \"SELECT AVG(CPUUtilization) FROM SCHEMA(\\\"AWS/EC2\\\", InstanceId)\", \n            \"Id\": \"q1\", \n            \"Period\": 300, \n            \"Label\": \"Cluster CpuUtilization\" \n        }, \n        { \n            \"Id\": \"m1\", \n            \"Label\": \"Unhealthy Behind Load Balancer\", \n            \"MetricStat\": { \n                \"Metric\": { \n                    \"Namespace\": \"AWS/ApplicationELB\", \n                    \"MetricName\": \"UnHealthyHostCount\", \n                    \"Dimensions\": [ \n                        { \n                            \"Name\": \"LoadBalancer\", \n                            \"Value\": \"app/EC2Co-EcsEl-EXAMPLE69Q/fdd2210e799e4376\" // can be replaced with “${ENTITIES}” or “${ENTITIES_ELB}” \n                        } \n                    ] \n                }, \n                \"Period\": 300, \n                \"Stat\": \"Average\" \n            } \n        } \n    ] \n} \n```\n\n## Copied source for a single object in an AWS CloudWatch dashboard\n\nThis example shows the JSON copied directly from a single object in the widgets list of the Source view for a CloudWatch dashboard.\n\n```\n{\n  \"type\": \"metric\",\n  \"x\": 9,\n  \"y\": 0,\n  \"width\": 15,\n  \"height\": 5,\n  \"properties\": {\n    \"view\": \"timeseries\",\n    \"metrics\": [\n      [ \"AWS/ApplicationELB\", \"RequestCountPerTarget\", \"TargetGroup\", \"targetgroup/api-tg/1e3dc9z72fe21ca2\", \"AvailabilityZone\", \"us-west-1a\" ],\n      [\"...\", \"targetgroup/api-tg-cs/82a1db5f950073e1\", \".\", \".\" ]\n    ],\n    \"region\": \"us-west-1\"\n  }\n}\n```\n\nBoth types of queries can use template variables to represent a set of entities or the start and end time selected in the UI. For example, instead of querying for a single ELB instance, you can replace the `Value` field for the `LoadBalancer` dimension with `${ENTITY_ELB}`. The query is run against each ELB instance associated with the selected service in the Service Observability UI. The chart displays a time series for each one.\n\n**Note:** JSON objects copied from the source of a CloudWatch dashboard don't include explicit time fields. The values from the time picker in the Service Observability UI are used automatically.\n\n<table id=\"table_tgs_c53_zfc\"><thead><tr><th>\n\nAWS field\n\n</th><th>\n\nTemplate variable\n\n</th></tr></thead><tbody><tr><td>\n\n`Dimensions[n].Value`\n\n</td><td>\n\n-   `ENTITIES`\n-   `ENTITIES_HOST`\n-   `ENTITIES_LAMBDA`\n-   `ENTITIES_API_GATEWAY_HTTP`\n-   `ENTITIES_API_GATEWAY_REST`\n-   `ENTITIES_ELB`\n-   `ENTITIES_RDS`\n\n The `Dimensions[n].Value` field must be a resource identifier key.\n\n</td></tr><tr><td>\n\n`StartTime`\n\n</td><td>\n\n`START`\n\n</td></tr><tr><td>\n\n`EndTime`\n\n</td><td>\n\n`END`\n\n</td></tr></tbody>\n</table>## Azure advanced queries\n\nYou run an advanced query for an Azure-based chart by copying the source from the Azure UI and pasting it in the **Advanced query** field.\n\nTo copy the source:\n\n1.  In the Azure UI, export and download the dashboard.\n2.  Copy the JSON for a single chart. Charts are located at `properties.lenses[i].parts[j].metadata.settings.content.options.chart`.\n3.  Paste the JSON into the query field. The JSON must include at least the following:\n\n    ```\n    ``` \n    { \n    “metrics”: [ \n      “name”: string, \n      “resourceMetadata”: { \n        “id”: string \n        } \n      ] \n    } \n    ``` \n    ```\n\n    The `id` field must be either an Azure resource ID or a [template variable](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/it-operations-management/service-observability/service-observability-template-variables.md).\n\n\n**Parent Topic:**[Service Observability reference](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/it-operations-management/service-observability/service-observability-reference.md)\n\n"}}