This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This document describes how to chart the results ofSQL queries thatyou run on theLog Analytics page. Charts can help you identify patterns andtrends in your data. This document also describes how to save your chart to acustom dashboard.

The following screenshot displays the results of anexample query, which is described later in this document.The screenshot displays the status reported by spans, after aggregating theresponses by time and by theservice_name:

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Observability API.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the API

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Enable the Observability API.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the API

1. To get the permissions that you need to load theLog Analytics page, write and run queries onyour trace data, and to save charts on custom dashboards, ask your administrator to grant you the following IAM roles:

   • Observability View Accessor (roles/observability.viewAccessor) on the observability views that you want to query. This role supports IAM conditions, which let you restrict the grant to a specific view. If you don't attach a condition to the role grant, then the principal can access all observability views.
   • Logs Viewer (roles/logging.viewer) on your project.
   • Monitoring Editor (roles/monitoring.editor) on your project.

   For more information about granting roles, seeManage access to projects, folders, and organizations.

   You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Select the data to chart

To configure what data to display on a chart, build a query by usingSQL.When you select theChart tab, a chart displays your queryresults. After the query has been run and a chart has been generated, you cancustomize the chart configuration by changing the chart type, and selectingcolumns to view different data.

To view your query results as a chart, run a query by doing the following:

1. In the Google Cloud console, go to themanage_searchLog Analytics page:

   Go toLog Analytics

   If you use the search bar to find this page, then select the result whose subheading isLogging.

2. In theQuery pane, enter a query and then clickRun.

3. After the query has completed, in theResults tab, select how you want to view your query results:

   • Table: Tabular format only.

   • Chart: Chart format only

   • Both: Chart and tabular format.

     After you select how to view your query results, you can configure anyfields for the selected visualization, and then save the query and resultsto a custom dashboard. The saved format is tabular when you've selectedtheTable option. Otherwise, the format is a chart.

     For charts, the visualization options let you select the chart type,and select which rows and columns are charted. For more informationabout the chart configuration, seeCustomize chart configuration.

Customize chart configuration

You can customize the chart configuration by changing the chart type, selectingthe dimension and measure to chart, and by applying a breakdown. TheDimension is used to group or categorize rows and is the X-axis value. TheMeasure, or the Y-axis value, is a data series that is plotted against theY-axis.

Change chart type

You can select from the following chart types, depending on what kind of rowsand columns you selected as the dimension and measure, and how you want thatdata to be visualized.

• Bar chart (default) :Bar charts plot data on two axes. If your chart uses a category or stringas the dimension, then you can set the chart configuration for a bar chart tohorizontal or vertical, where the dimension and measure axes are swapped.

• Line chart:Line charts can be used to show data changes over time. When you use a linechart, each time series is shown by a different line that corresponds tothe measure(s) you have selected.

  If your X-axis is time-based, each data point is placed at the beginning ofa time interval. Each data point is connected by linear interpolation.

• Stacked area chart:An area chart is based on a line chart, and the area below each line isshaded. In area charts, the data series are stacked. For example, if you havetwo identical series, the series overlaps in a line chart, but theshaded area is stacked in an area chart.

• Pie or donut chart:A pie chart shows how categories in a dataset relate to the whole dataset,by using a circle to represent the whole dataset and wedges in the circleto represent the categories in the dataset. The size of a wedgeindicates how much, often as a percentage, the category contributes tothe whole.

• Table:A table shows one row for each row in the query result. The columns in thetable are defined by theSELECT clause. If you plan to display datain tabular form on a dashboard, then use aLIMIT clause to restrict thenumber of rows in the result be less than a few hundred.

• Gauge or scorecard:Gauges and scorecards provide you with the most recent value along witha green, amber, or red indicationbased on how that value compares to a set of thresholds.Unlike gauges, which only shows information about the most recentvalue, scorecards can also include information about past values.

  Gauges and scorecards can only display the query result when the queryresult contains at least one row, and that row contains a column with atimestamp and a column with numeric data. The query result can containmultiple rows and more than two columns.

  If you want to perform the time-based aggregation as part of your query,then do the following:

  • Configure your query to aggregate data over a time interval, to orderthe results by descending timestamps, and to limit the number ofrows in the results. You can use theLIMIT clause or use thetime range selector to limit the number of rows in the query result.

    For example, the following queries your trace data, and it aggregatesthe data over one-hour intervals, applies a limit, and sorts theresults:

    SELECTTIMESTAMP_TRUNC(start_time,HOUR)AShour,COALESCE(JSON_VALUE(resource.attributes,'$."service.name"'),JSON_VALUE(attributes,'$."service.name"'),JSON_VALUE(attributes,'$."g.co/gae/app/module"'))ASservice_name,status.codeASstatus,COUNT(*)AScountFROM`PROJECT_ID.LOCATION._Trace.Spans._AllSpans`GROUPBYhour,service_name,statusORDERBYhourDESCLIMIT1000

    The following describes the meaning of the fields in the previousexpression:

    • PROJECT_ID: The identifier of the project.
    • LOCATION: The location of the observability bucket. Youmust use theus location.

    The previous query was used to generate the figure shown at thebeginning of this document. That figure also shows the chartconfiguration.

  • Set theDimension to match the column that reports the time-unit.For example, if your query aggregates data by one hour and creates acolumn namedhour, then set theDimension menu tohour.

  • SelectDisable interval because your query already specifies theaggregation interval. In the example, this interval is one hour.

  • Set theMeasure to the numeric column, and set the function tonone.

    Note: If your query performs time-based aggregation, then make sure thatyou follow the previous guidance. Other settings result in both your queryand Log Analytics performing aggregation operations, which results in thegauge or scorecard displaying unexpected values.

  If you want to rely on Log Analytics to perform the time-based aggregation foryou, then do the following:

  • Configure the time-range selector, which affects the number of rows in the query result.
  • Set theDimension to match the column that reports the time-unit. For example, you might set this menu totimestamp.
  • Set theInterval menu to the aggregation interval to a specific interval. For example, set the value of this field to1 hour. Don't selectAutomatic interval.
  • Set theMeasure to the numeric column, and select a function such assum.

Change dimension and measure

You can choose which rows and columns are charted by selecting the dimensionand measure fields.

• Dimension

  The dimension must be a timestamp, numeric, or string column. By default, the dimension is set tothe first timestamp-based column in the schema. If no timestamp is present in the query, then thefirst string column is selected as the dimension. You can also customize what the dimension is intheChart display panel. When a timestamp column is selected as the dimension, then the chartshows how data changes over time. When a string column is selected as the dimension for a bar chart,you can sort the data by ascending or descending, which sorts the dimension by lexical order. Youcan also keep the `default` sort order, which sorts the dimension based on the values of thecorresponding measure in descending order.

  By default, the interval for timestamps is automaticallyset, but you can also select a custom interval. Automatic intervals changevalues based on the time-range selector to maintain similar sized groups.

  You can also disable the interval, which lets you specify your ownaggregations and time ranges within the query for more complex analysis.Disabling the interval sets the aggregation function of the measurestonone. Only numeric measures are permitted when the dimension intervalis disabled.

  Note: Only theTIMESTAMP column can be used as a timestamp dimension. TouseDATE andDATETIME, you must use theCAST()function to convert the data types toTIMESTAMP.

• Measure

  You can select multiple measures in theChart display panel. When you select a measure, youmust also select the aggregation function to perform on its grouped values, such ascount,sum,average, andpercentile-99.For example,count-distinct returns the number of unique values in a given column.

  If you select theDisable interval checkbox for the dimension, then thenone aggregation function option is available. If the dimension is astring value, theDisable interval checkbox is not shown. However,setting a measure's aggregation functions tonone also disables theinterval.

Add a breakdown

After a chart is generated from your query, you can save that chart to acustom dashboard. Custom dashboards let youdisplay and organize information that is useful to you by using a variety ofwidget types. These dashboards also let you define variables, which aredashboard-level filters that apply only to specific widgets. To apply avariable to a widget, you must modify the query. For more information, seeApply a variable to a widget.

A dashboard displays a warning message for a widget that contains a SQL querywhen the query runs on the BigQuery engine and the queriedresources aren't in the same ownership boundary. Ownership boundaries aredetermined by several factors, one of which is the hierarchy ofthe resources the query uses. To prevent these warnings,Set up a service perimeter.

To save your chart to a dashboard, do the following:

1. In the Google Cloud console, go to themanage_searchLog Analytics page:

   Go toLog Analytics

   If you use the search bar to find this page, then select the result whose subheading isLogging.

2. Run a query to generate a chart, then clickdashboardSave chart in theChart tab.

3. In theSave to dashboard dialog, enter a title for your chart and select the dashboard where you want to save the chart to.

4. Optional: To view the custom dashboard, in the toast, clickView dashboard.

To view a list of custom dashboards that contain charts generated bySQL queries, go to theSave Chart button and clickarrow_drop_downMenu.

Edit a chart saved to a custom dashboard

You can modify a chart after it has been saved to a dashboard. For moreinformation, seeModify a widget's configuration.In theConfigure widget dialog, you can do the following:

• Change the data you query.
• Edit the query that is used to generate a chart.
• Customize the chart configuration to visualize different data.

Limitations

• If your Google Cloud project is in a folder that uses Assured Workloads,then the charts that you generate can't be displayed on a custom dashboard.

• Dashboard-level filters don't apply to charts generated from a SQL query.

• Selected columns must have at least one row with a non-null value.

• If you save a query and customize the chart configuration, then the customchart configuration is not saved.

• If your query already contains aggregations, then the generated chart mightbe different due to additional aggregation that is automatically appliedby Log Analytics.

• JSON paths must be cast to strings or numbers in order to be charted.

What's next

• Save and share a SQL query.
• Sample SQL queries.
• Query a linked dataset in BigQuery.