Query assets with SQL

You can query assets in your project, folder, or organization using a BigQuery SQL-compatible dialect.

Before you begin

  1. Enable the Cloud Asset Inventory API in the project you're running Cloud Asset Inventory commands from.

    Enable the Cloud Asset Inventory API

  2. Make sure your account has the correct role to call the Cloud Asset Inventory API. For individual permissions for each call type, see Permissions.

Limitations

Tables you can query

You can query the following tables:

  • For RESOURCE content types, table names in the dataset correspond to the asset type name, assuming that asset type exists. For example, the compute_googleapis_com_Instance table contains Compute Engine instance metadata.

  • For non-RESOURCE content types, table names in the dataset correspond to the RPC/REST content type names. For example, ACCESS_POLICY.

  • To query resource standard metadata across resource types, use the table name STANDARD_METADATA. This includes all fields except resource.DATA, which is specific to each resource type.

Query asset metadata

Console

To query the asset metadata for your project, folder, or organization, complete the following steps:

  1. In the Google Cloud console, go to the Asset query tab on the Asset Inventory page.

    Go to Asset query

    If the Asset query tab doesn't appear, you need access to the Security Command Center Premium or Enterprise tier, or Gemini Cloud Assist.

  2. Change to the project, folder, or organization you want to query.
  3. Click the Asset query tab.

  4. To query asset metadata, either use a sample query or build your own:

    • To use a sample, click an entry in the Query library tab to preview the query. Click Apply to copy that sample into the Edit query box, then either edit the query, or click Run to execute it.
    • To build your own query, enter the query text directly into the Edit query box, then click Run to execute it. To assist in writing your own query, you can click a table in the Select table pane to preview its schema and content. See Query syntax to learn how to construct a query.

    The asset metadata matching the query is shown in the Query result tab.

  5. Optional: To download the query result sets in CSV format, click Export.

    The maximum size of the CSV file is 2 MB. If the download request fails because the file size exceeds this limit, a message appears with instructions for exporting the full results.

gcloud

gcloud asset query \
    --SCOPE \
    --statement="SQL_SELECT_QUERY" \
    --timeout="TIMEOUTs"

Provide the following values:

  • SCOPE: Use one of the following values:

    • project=PROJECT_ID, where PROJECT_ID is the ID of the project that has the assets you want to query.

    • folder=FOLDER_ID, where FOLDER_ID is the ID of the folder that has the assets you want to query.

      How to find the ID of a Google Cloud folder

      Google Cloud console

      To find the ID of a Google Cloud folder, complete the following steps:

      1. Go to the Google Cloud console.

        Go to the Google Cloud console

      2. Click the switcher list box in the menu bar.
      3. Select your organization from the list box.
      4. Search for your folder name. The folder ID is shown next to the folder name.

      gcloud CLI

      You can retrieve the ID of a Google Cloud folder that's located at the organization level with the following command:

      gcloud resource-manager folders list \
    --organization=$(gcloud organizations describe ORGANIZATION_NAME \
      --format="value(name.segment(1))") \
    --filter='"DISPLAY_NAME":"TOP_LEVEL_FOLDER_NAME"' \
    --format="value(ID)"

      Where TOP_LEVEL_FOLDER_NAME is a partial or full string match for the folder's name. Remove the --format flag to see more information about the found folders.

      The previous command doesn't return the IDs of subfolders within folders. To do so, run the following command using a top level folder's ID:

      gcloud resource-manager folders list --folder=FOLDER_ID

    • organization=ORGANIZATION_ID, where ORGANIZATION_ID is the ID of the organization that has the assets you want to query.

      How to find the ID of a Google Cloud organization

      Google Cloud console

      To find the ID of a Google Cloud organization, complete the following steps:

      1. Go to the Google Cloud console.

        Go to the Google Cloud console

      2. Click the switcher list box in the menu bar.
      3. Select your organization from the list box.
      4. Click the All tab. The organization ID is shown next to the organization name.

      gcloud CLI

      You can retrieve the ID of a Google Cloud organization with the following command: