Chart query results with Log Analytics

This document describes how to chart your Log Analytics query results, which lets you identify patterns and trends in your log data. Log Analytics lets you search and aggregate logs to generate useful insights by using SQL queries.

After you run a query, the query results can be viewed in a table, or converted into a chart. For example, to see which severity types your logs are generating, create a chart that displays the counts of logs generated over the past 12 hours and break down the logs by severity. The following screenshot illustrates data points broken down into different severity types:

Before you begin

• Ensure that you have a log bucket that has been upgraded to use Log Analytics. The Logs Storage page lists your log buckets and their configuration information.

  In the navigation panel of the Google Cloud console, select Logging, and then select Logs Storage:

  Go to Logs Storage

• To get the permissions that you need to load the Log Analytics page and to run queries, ask your administrator to grant you the Logs Viewer (roles/logging.viewer) IAM role on Cloud Logging. For more information about granting roles, see Manage access.

  You might also be able to get the required permissions through custom roles or other predefined roles.

  The permissions that you need to view log entries and run queries on the Log Analytics page are the same as those that you need to view logs on the Logs Explorer page. For information about additional roles that you need to query views on user-defined buckets or to query the _AllLogs view of the _Default log bucket, see Cloud Logging roles.

• To get the permissions that you need to create and manage dashboards by using the Google Cloud console, ask your administrator to grant you the Monitoring Editor (roles/monitoring.editor) IAM role on your project. For more information about granting roles, see Manage access.

  You might also be able to get the required permissions through custom roles or other predefined roles.

Select the data to chart

To configure what data to display on a chart, build a query by using SQL. When you select the Chart tab, Logging automatically generates a chart based on your query results. After the query has been run and a chart has been generated, you can customize the chart configuration by changing the chart type, and selecting columns to view different data.

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

1. In the navigation panel of the Google Cloud console, select Logging, and then select Log Analytics:

   Go to Log Analytics

2. In the Query pane, enter a query and then click Run.

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

   • Table: View only a table of the query results.

   • Chart: View only a chart of the query results.

   • Both: View both a table and a chart side by side.

4. If you selected the Chart or Both tabs, then you can customize the chart configuration by changing the chart type, and customizing which rows and columns are charted. For more information about the chart configuration, see Customize chart configuration.

Customize chart configuration

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

Change chart type

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

• Bar chart (default)

  Bar charts plot data on two axes. If your chart uses a category or string as the dimension, then you can set the chart configuration for a bar chart to horizontal 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 line chart, each time series is shown by a different line that corresponds to the measure(s) you have selected.

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

• Area chart An area chart is based on a line chart, and the area below each line is shaded. In area charts, the data series are stacked. For example, if you have two identical series, the series overlaps in a line chart, but the shaded area is stacked in an area chart.

• Pie 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 circle to represent the categories in the dataset. The size of a wedge indicates how much, often as a percentage, the category contributes to the whole.

Change dimension and measure

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

Note: If your query results contain a column that has long strings, such as error messages, and you select that column to be the dimension or breakdown, then your chart might have errors or unexpected values.

• Dimension

  The dimension must be a timestamp, numeric, or string column. By default, the dimension is set to the first timestamp-based column in the schema. If no timestamp is present in the query, then the first string column is selected as the dimension. You can also customize what the dimension is in the Chart display panel. When a timestamp column is selected as the dimension, then the chart shows how data changes over time.

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

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

  Note: Only the TIMESTAMP column can be used as a timestamp dimension. To use DATE and DATETIME, you must use the CAST() function to convert the data types to TIMESTAMP.

• Measure

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

  If you select the Disable interval checkbox for the dimension, then the none aggregation function option is available. If the dimension is a string value, the Disable interval checkbox is not shown. However, setting a measure's aggregation functions to none also disables the interval.

Add a breakdown

To split a single data series into multiple data series based on another column, add a breakdown.

When you select a breakdown, choose columns that contain a small number of short and meaningful labels, like region_name, instead of fields that might contain a large number of strings or long strings, like textPayload.

For example, see the following chart configuration, where the Dimension field is set to type, the Measure field is set to Count rows, and the Breakdown field is set to severity:

The following chart is an example of a chart with an added breakdown:

In the previous screenshot, you see a stacked data series, where the resource type k8s_container is split into different severity types. This lets you identify how many logs of each severity type were generated by a certain resource.

Note: If your chart contains more than one measure, then you can't add a breakdown.

Save a chart to a custom dashboard

After a chart is generated from your query, you can save that chart to a custom dashboard. Custom dashboards let you display and organize information that is useful to you by using a variety of widget types. For example, you can create a dashboard that provides details about the usage of your Cloud Storage buckets:

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

1. In the navigation panel of the Google Cloud console, select Logging, and then select Log Analytics:

   Go to Log Analytics

2. Run a query to generate a chart, then click Save chart in the Chart tab.

3. In the Save 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, click View dashboard.

To view a list of custom dashboards that contain charts generated by Log Analytics SQL queries, go to the Save Chart button and click Menu.

Edit a chart saved to a custom dashboard

To edit charts generated by Log Analytics SQL queries that are saved to a dashboard, see Modify a widget's configuration. In the Configure widget dialog, you can edit the query that is used to generate a chart, or you can 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 Log Analytics SQL query.

Sample queries

This section provides sample SQL queries for charting your query results. To gain more useful insights from your logs, customize the chart configuration. To use the sample queries, do the following:

1. In the navigation panel of the Google Cloud console, select Logging, and then select Log Analytics:

   Go to Log Analytics

2. Identify the table name for a log view by running the default query:

   In the Log views list, locate the log view, and then select Query. The Query pane is populated with a default query, which includes the name of the table that is queried. The table name has the format project_ID.region.bucket_ID.view_ID.

   For more information about how to access the default query, see Query a log view.

3. Replace TABLE with the name of the table that corresponds to the view that you want to query, then copy the query.

4. Paste the query in the Query pane, then click Run query.

Chart log entries by location and severity

The following query selects the location and severity, with the location cast as a string:

SELECT
  CAST(JSON_VALUE(resource.labels.location) AS STRING) AS location,
  severity,
FROM
  `TABLE`

A sample chart and chart configuration looks like the following:

In the previous screenshot, the chart configuration has the following chart configuration:

• Chart type: Bar chart, horizontal
• Dimension: location, with a limit of 10
• Measure: Count rows
• Breakdown: severity, with a limit of five

Chart BigQuery Data Access audit logs

The following query filters for BigQuery data_access audit logs, and selects certain fields, such as user_email, ip, auth_permission, and job_execution_project. For example, you can create a chart that visualizes the frequency of each principal's BigQuery API usage over time.

SELECT 
  timestamp,
  proto_payload.audit_log.authentication_info.principal_email as user_email,
  proto_payload.audit_log.request_metadata.caller_ip as ip,
  auth.permission as auth_permission,
  auth.granted as auth_granted,
  JSON_VALUE(data_access.resource.labels.project_id) AS job_execution_project,
  SPLIT(proto_payload.audit_log.resource_name, '/')[SAFE_OFFSET(1)] AS referenced_project,
  SPLIT(proto_payload.audit_log.resource_name, '/')[SAFE_OFFSET(3)] AS referenced_dataset,
  SPLIT(proto_payload.audit_log.resource_name, '/')[SAFE_OFFSET(5)] AS referenced_table
FROM `TABLE` as data_access,
  UNNEST(proto_payload.audit_log.authorization_info) AS auth
WHERE
  log_id="cloudaudit.googleapis.com/data_access"
  AND data_access.resource.type = 'bigquery_dataset'

A sample chart and chart configuration looks like the following:

In the previous screenshot, the chart configuration has the following chart configuration:

• Chart type: Bar chart, vertical
• Dimension: user_email, with a limit of five
• Measure: Count rows
• Breakdown: auth_permission, with a limit of five

Limitations

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

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

• If your query already contains aggregations, then the generated chart might be different due to additional aggregation that is automatically applied by Log Analytics.

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

What's next

• For an overview of Log Analytics, see Log Analytics.

• For sample queries, see Sample SQL queries.

• To learn about how to analyze with audit logs by using Log Analytics, see SQL queries for security insights.