This document describes how to query and analyze your log and tracedata by using Log Analytics, which provides aSQL-based query interface.SQL lets youperform aggregate analysis, which can help you generate insights and identifytrends. To view your query results, use the tabular form, or visualize thedata with charts.You can also save these tables and charts to your custom dashboards.

About linked BigQuery datasets

You don't need alinked BigQuery dataset to queryyour log data, your trace data, or both data types when you use theLog Analytics page.

You do need a linked BigQuery datasets when youwant to do any of the following:

• Join log or trace data with otherBigQuery datasets.
• Query your log or trace data from another service like theBigQuery Studio page or Looker Studio.
• Improve the performance of the queries that you run from theLog Analyticsby running them on yourBigQuery reserved slots.
• Create analerting policy that monitors the resultof a SQL query. This capability is only supported when log data is queried.For more information, seeMonitor your SQL query results with an alerting policy.

This document doesn't describe how to create a linked dataset, which requiresa data-type specific process. To learn how to create a linked dataset, seeQuery log data by using a linked dataset orQuery trace data by using a linked dataset.

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, run, and save privatequeries on your trace data, 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. Observability views are in Public Preview.
   • Observability Analytics User (roles/observability.analyticsUser) on your project. This role contains the permissions required to save and run private queries, and to run shared queries.
   • Logs Viewer (roles/logging.viewer) on your project
   • Logs View Accessor (roles/logging.viewAccessor) on the project that stores the log views that you want to query.

   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.

Query log and trace data

This section describes the approaches that you can use to query yourlog and trace data:

• Load a system-defined query, edit this query, and then run the query.
• Enter and run a custom query. For example, you might paste in a query youhave or write one. Custom queries can include joins, nested queries, andother complex SQL statements. For examples, seeSample SQL queries.
• Build a query by making menu selections and then run that query.Log Analytics converts your selections into a SQL query, which you can bothview and edit.

Load, edit, and run the system-defined query

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.

   Note: If a window on theLog Analytics pagedisplays a "Get started with Log Analytics" message, then on the window,clickcloseClose.

2. In theViews menu, select a view.

   To find the view to query, use thefilter_listFilter baror scroll through the list:

   • Log views, which are listed in thesegmentLogssection, are ordered by the bucket and view IDs.

   • Analytics views, which are listed in thedata_tableAnalytics Views section, areordered by location and ID of the view.Observability views are in Public Preview.

   • There is one trace view, which is listed in theTraces section. Observability views are in Public Preview.

     If you don't see a view named_Trace.Spans._AllSpans, then yourGoogle Cloud project doesn't contain a observability bucket named_Trace. For information about how to resolve this failure, seeTrace storage initialization fails.

3. Do one of the following:

   • To load a system-defined query that relies on theQuery Builder, whichlets you define the query with menu selections, make sure that theQuery pane displaysQuery Builder. If a SQL editor is shown, thenclicktune Builder.

   • To load a system-defined query that extracts JSON values, then make surethatQuery pane displays the SQL editor. If this pane displaysQuery Builder, then clickcode SQL.

4. In theSchema pane, selectQuery, and then clickOverwrite.

   TheQuery pane displays a system-defined query. If you selected theQuery Builder mode but want to view the SQL query, clickcode SQL.

5. Optional: Modify the query.

6. To run the query, go to the toolbar and selectRun Query.

   Log Analytics presents the query results in a table. However, you can createa chart, and you can also save the table or chart toacustom dashboard. For more information, seeChart SQL query results.

   If the toolbar displaysRun in BigQuery, then you need to switchLog Analytics to use the default query engine. To make this change, in thetoolbar of theQuery pane, clicksettingsSettingsand then selectAnalytics (default).

Enter and run a custom query

To enter a SQL query, then 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.

   Note: If a window on theLog Analytics pagedisplays a "Get started with Log Analytics" message, then on the window,clickcloseClose.

2. In theQuery pane, click thecode SQL.

   • To specify a time range, we recommend that you use the time-rangeselector. If you add aWHERE clause that specifies thetimestamp field, then that value overrides the setting in thetime-range selector and that selector is disabled.

   • For examples, seeSample SQL queries.

   • The view you query determines the format of theFROM clause:

     Log data

     You can querylog views oranalytics views.Use the following format for theFROM clause:

     • Log views:

       FROM`PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`

     • Analytics views:

       FROM`analytics_view.PROJECT_ID.LOCATION.ANALYTICS_VIEW_ID`

     The fields in the previous expressions have the following meaning:

     • PROJECT_ID: The identifier of the project.
     • LOCATION: The location of the log view or the analytics view.
     • BUCKET_ID: The name or ID of the log bucket.
     • LOG_VIEW_ID: The identifier of the log view, which is limited to 100 characters and can include only letters,digits, underscores, and hyphens.
     • ANALYTICS_VIEW_ID: The ID of the analytics view, which is limited to 100 characters and can include only letters,digits, underscores, and hyphens.

     If the query pane displays an error message that references theFROM statement, then the view can't be found.For information about how to resolve this failure, seeErrorFROM clause must contain exactly one log view.

     Trace data

     Preview

     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.

     The SQL editor displays the fully qualified name for the_Trace.Spans._AllSpans view, which has the following form:

     FROM`PROJECT_ID.LOCATION._Trace.Spans._AllSpans`

     The fields in the previous expression have the following meaning:

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

     If the query pane displays an error message that references theFROM statement, then the view can't be found.For information about how to resolve this failure, seeError message stating a view does not exist.

3. To run the query, go to the toolbar and selectRun Query.

   Log Analytics presents the query results in a table. However, you can createa chart, and you can also save the table or chart toacustom dashboard. For more information, seeChart SQL query results.

   If the toolbar displaysRun in BigQuery, then you need to switchLog Analytics to use the default query engine. To make this change, in thetoolbar of theQuery pane, clicksettingsSettingsand then selectAnalytics (default).

Build, edit, and run a query

TheQuery Builder interface lets you build a query by making selections frommenus. Log Analytics converts your selections into a SQL query, which you canview and edit. For example, you might start by using theQuery Builder interface and then switch to the SQL editor to refine yourquery.

Log Analytics can always convert your menu-selections from theQuery Builderinterface into a SQL query. However, not all SQL queries can be representedby theQuery Builder interface. For example, queries with joins can'tbe represented by this interface.

To build a query, 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.

   Note: If a window on theLog Analytics pagedisplays a "Get started with Log Analytics" message, then on the window,clickcloseClose.

2. If theQuery pane displays a SQL editor, then selecttuneBuilder, which opens theQuery Builder pane.

3. Use theSource menu to select the view you want to query. Your selectionsare mapped to theFROM clause in the SQL query.

4. Optional: Use the following menus to restrict or format the result table:

   • Search all fields: Search for matching strings. Your selections aremapped to theWHERE clause in the SQL query.

   • Columns: Select the columns that appear in the result table. Yourselections are mapped to theSELECT clauses in the SQL query.

     When you select a field name in this menu, a dialog opens. In this dialog,you can do the following:

     • Use the menu to aggregate or group your data.

       To prevent syntax errors, any aggregation and grouping you applyto one column is automatically applied to other columns as well.For an example of how to aggregate and group entries, seeGroup and aggregate data by using the Query Builder.

     • Cast a value of any type into another specified data type. For moreinformation, see theCASTdocumentation.

     • Extract a substring of values by using regular expressions. For moreinformation, see theREGEXP_EXTRACTdocumentation.

   • Filters: Add filters when you want to restrict the query to spansthat contain a specific attribute or span ID. The menu lists allavailable filter options. Your selections are mapped to theWHEREclause in the SQL query.

   • Sort By: Set the columns to sort by, and whether the sort isascending or descending. Your selections are mapped to theORDER BYclause in the SQL query.

   • Limit: Set the maximum number of rows in the result table. Yourselections are mapped to theLIMIT clause in the SQL query.

5. To run the query, go to the toolbar and selectRun Query.

   Log Analytics presents the query results in a table. However, you can createa chart, and you can also save the table or chart toacustom dashboard. For more information, seeChart SQL query results.

   If the toolbar displaysRun in BigQuery, then you need to switchLog Analytics to use the default query engine. To make this change, in thetoolbar of theQuery pane, clicksettingsSettingsand then selectAnalytics (default).

Example: Group and aggregate data by using the Query Builder

When you select a column in the Query Builder, each field includes a menu whereyou can add grouping and aggregation. Grouping lets you organize your data intogroups based on the value of one or more columns, and aggregation lets youperform calculations on these groups to return a single value.

Each field that you select in theColumns element has an attached menu withthe following options:

• None: Don't group or aggregate by this field.
• Aggregate: Group fields listed in theColumns element except whenthe field has anAggregate selection. For those fields, compute thevalue by performing an operation on all entries in each grouping.The operation might be to compute the average of a fieldor to do something like count the number of entries in each grouping.
• Group By: Group entries by all fields listed in theColumns element.

The following illustrates how you might construct a query that groups entriesand then performs some type of aggregation.

SELECT-- Truncate the timestamp by hour.TIMESTAMP_TRUNC(timestamp,HOUR)AShour_timestamp,severity,-- Compute average response_size.AVG(http_request.response_size)ASaverage_http_request_response_sizeFROM`PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`WHERE-- Matches log entries that have a response_size.http_request.response_sizeISNOTNULLGROUPBY-- Group log entries by timestamp and severity.TIMESTAMP_TRUNC(timestamp,HOUR),severityLIMIT1000

WITHscope_queryAS(SELECT*FROM`PROJECT_ID.global._Trace._AllSpans`)SELECT-- Report the truncated start time, span name, span kind, average duration and number-- of entries for each group.TIMESTAMP_TRUNC(start_time,HOUR)AShour_start_time,nameASspan_name,kind,AVG(duration_nano)ASaverage_duration_nano,COUNT(*)AScount_allFROMscope_queryGROUPBYTIMESTAMP_TRUNC(start_time,HOUR),name,kindLIMIT100

Display the schema

The schema defines how the data is stored, which includes the fields and theirdata types. This information is important to you because the schema determinesthe fields you query and whether you need to cast fields to different datatypes. For example, to write a query that computes the average latency ofHTTP requests, you need to know how to access the latency field and whether itis stored as an integer like100 or as a string like"100". If the latencydata is stored as a string, then the query must cast the value to a numericvalue before computing an average.

To identify the schema, 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. In theViews menu, select a view.

   TheSchema pane is updated. Log Analytics automatically infers the fields of a column when the data type is JSON. To view how often these inferred fields appear in your data, clickmore_vertOptions and selectView info and description.

   Log data

   For log views, the schema is fixed and corresponds to theLogEntry.For analytics views, you can modify the SQL query to change the schema.

   Trace data

   Preview

   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.

   To learn about the schema, seeStorage schema for trace data.

   If you don't see a view named_Trace.Spans._AllSpans, then yourGoogle Cloud project doesn't contain a observability bucket named_Trace. For information about how to resolve this failure, seeTrace storage initialization fails.

Restrictions

If you want to query multiple views, then those views must reside in the samelocation. For example, if you store two views in theus-east1 location, thenone query can query both views. You can also query two views stored in theus multi-region. However, if a view's location isglobal, then that viewcan reside in any physical location. Therefore, joins between two views thathave the location ofglobal might fail.

For a list of restrictions that apply to log data, seeLog Analytics: Restrictions.

What's next

• Chart SQL query results.
• Save and share queries.
• Sample SQL queries.