Console
Start free

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:

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:

See the gcloud CLI reference for all options.

Example

Run the following command to get the names and asset types of the first two Compute Engine instances in the my-project project.

gcloud asset query \
    --project=my-project \
    --statement="
      SELECT
        name, assetType
      FROM
        compute_googleapis_com_Instance
      LIMIT 2"

Finished job response

The following sample shows a response to the previous example query. The response contains a job reference and tells you whether the job has finished (done: true). If the job has finished, then the queryResult object is populated with the appropriate data, and the results are listed afterward.

done: true
jobReference: 0000000000000000000000000000000000000000000000000000000000000000
queryResult:
  nextPageToken: ''
  totalRows: '2'

name: //compute.googleapis.com/projects/my-project/zones/us-central1-a/instances/instance-1
assetType: compute.googleapis.com/Instance

name: //compute.googleapis.com/projects/my-project/zones/us-central1-c/instances/instance-2
assetType: compute.googleapis.com/Instance

Unfinished job response

If you set a timeout in your request, the query is performed asynchronously and you are sent a response that indicates the job is unfinished (done: false). These sorts of responses contain a job reference and an unpopulated queryResult object:

done: false
jobReference: 0000000000000000000000000000000000000000000000000000000000000000
queryResult:
  nextPageToken: ''
  totalRows: '0'

You can use the jobReference value to retrieve the query results later, after the job has completed and the data is available.

REST

HTTP method and URL:

POST https://cloudasset.googleapis.com/v1/SCOPE_PATH:queryAssets

Request JSON body:

{
  "statement": "SQL_SELECT_QUERY",
  "timeout": "TIMEOUTs",
  "pageSize": "PAGE_SIZE",
  "pageToken": "PAGE_TOKEN"
}

Provide the following values:

  • SCOPE_PATH: Use one of the following values:

    The allowed values are:

  • SQL_SELECT_QUERY: An SQL SELECT query.
  • TIMEOUT: Optional. The maximum time, in seconds, that a client should wait for the query to complete before continuing. Use timeouts to asynchronously run your query, and retrieve the results later with job references.

    • PAGE_SIZE: Optional. The number of results to return per page. The maximum is 500. If the value is set to 0 or a negative value, an appropriate default is selected. A nextPageToken is returned to retrieve subsequent results.

  • PAGE_TOKEN: Optional. Long request responses are separated over multiple pages. When pageToken isn't specified, the first page is returned. Subsequent pages can be called by using the previous response's nextPageToken as the pageToken value.

    • See the REST reference for all options.

    Command examples

    Run one of the following commands to get the names and asset types of the first two Compute Engine instances in the my-project project.

    curl (Linux, macOS, or Cloud Shell)

    curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d '{
            "statement": "
              SELECT
                name, assetType
              FROM
                compute_googleapis_com_Instance
              LIMIT 2"
          }' \
     https://cloudasset.googleapis.com/v1/projects/my-project:queryAssets

    PowerShell (Windows)

    $cred = gcloud auth print-access-token

$headers = @{ 
  "Authorization" = "Bearer $cred"
}


$body = @"
{
  "statement": "
    SELECT
      name, assetType
    FROM
      compute_googleapis_com_Instance
    LIMIT 2"
}
"@

Invoke-WebRequest `
  -Method POST `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -Body $body `
  -Uri "https://cloudasset.googleapis.com/v1/projects/my-project:queryAssets" | Select-Object -Expand Content

    Finished job response

    The following sample shows a response to the previous example query. The response contains a job reference and tells you whether the job has finished ("done": true). If the job has finished, then the queryResult object is populated with the appropriate data.

    Query results are split into rows, an array that contains asset metadata, and schema, an object which describes the schema for each asset in the rows array. This is done to minimize duplication of field names and types in large responses.

    Similarly, f and v are used in the rows array instead of fields and value to keep responses as small as possible.

    {
  "jobReference": "0000000000000000000000000000000000000000000000000000000000000000",
  "done": true,
  "queryResult": {
    "rows": [
      {
        "f": [
          {
            "v": "//compute.googleapis.com/projects/my-project/zones/us-central1-a/instances/instance-1"
          },
          {
            "v": "compute.googleapis.com/Instance"
          }
        ]
      },
      {
        "f": [
          {
            "v": "//compute.googleapis.com/projects/my-project/zones/us-central1-a/instances/instance-2"
          },
          {
            "v": "compute.googleapis.com/Instance"
          }
        ]
      }
    ],
    "schema": {
      "fields": [
        {
          "field": "name",
          "type": "STRING",
          "mode": "NULLABLE",
          "fields": []
        },
        {
          "field": "assetType",
          "type": "STRING",
          "mode": "NULLABLE",
          "fields": []
        }
      ]
    },
    "nextPageToken": "",
    "totalRows": "1"
  }
}

    Unfinished job response

    If you set a timeout in your request, the query is performed asynchronously and you are sent a response that indicates the job is unfinished ("done": false). These sorts of responses contain a job reference and an unpopulated queryResult object:

    {
  "jobReference": "0000000000000000000000000000000000000000000000000000000000000000",
  "done": false,
  "queryResult": {
    "rows": [],
    "schema": {
      "fields": []
    },
    "nextPageToken": "",
    "totalRows": "0"
  }
}

    You can use the jobReference value to retrieve the query results later, after the job has completed and the data is available.

    Retrieve query results later

    To retrieve a request later that has taken time to complete, make one the following requests.

    gcloud

    gcloud asset query \
    --SCOPE \
    --job-reference="JOB_REFERENCE"

    Provide the following values:

    Example

    Run the following command to get the results of a previously run query in the my-project project.

    gcloud asset query \
    --project=my-project \
    --job-reference="0000000000000000000000000000000000000000000000000000000000000000"

    REST

    HTTP method and URL:

    POST https://cloudasset.googleapis.com/v1/SCOPE_PATH:queryAssets

    Request JSON body:

    {
  "jobReference": "JOB_REFERENCE",
  "pageToken": "PAGE_TOKEN"
}

    Provide the following values:

    Command examples

    Run one of the following commands to get the results of a previously run query.

    curl (Linux, macOS, or Cloud Shell)

    curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d '{
            "jobReference": "0000000000000000000000000000000000000000000000000000000000000000"
          }' \
     https://cloudasset.googleapis.com/v1/projects/my-project:queryAssets

    PowerShell (Windows)

    $cred = gcloud auth print-access-token

$headers = @{ 
  "Authorization" = "Bearer $cred"
}


$body = @"
{
  "jobReference": "0000000000000000000000000000000000000000000000000000000000000000"
}
"@

Invoke-WebRequest `
  -Method POST `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -Body $body `
  -Uri "https://cloudasset.googleapis.com/v1/projects/my-project:queryAssets" | Select-Object -Expand Content

    Export query results to BigQuery

    Query results are returned as Query Assets API responses. To export the results to your own BigQuery table, specify a BigQuery destination in the request. If you don't already have one, you must create a BigQuery dataset before making these requests.

    gcloud

    gcloud asset query \
    --SCOPE \
    --billing-project=BILLING_PROJECT_ID \
    --statement="SQL_SELECT_QUERY" \
    --bigquery-table=projects/BIGQUERY_PROJECT_ID/datasets/DATASET_ID/tables/TABLE_NAME \
    --write-disposition="WRITE_METHOD"

    Provide the following values:

    Example

    Run the following command to get the names and asset types of the first two Compute Engine instances in the my-project project, and export the results to the my-table BigQuery table in the my-project project, overwriting the entire table if it already exists.

    gcloud asset query \
  --project=my-project \
  --statement="
    SELECT
      name, assetType
    FROM
      compute_googleapis_com_Instance
    LIMIT 2" \
  --bigquery-table=projects/my-project/datasets/my-dataset/tables/my-table \
  --write-disposition="write-truncate"

    REST

    HTTP method and URL:

    POST https://cloudasset.googleapis.com/v1/SCOPE_PATH:queryAssets

    Headers:

    X-Goog-User-Project: BILLING_PROJECT_ID

    Request JSON body:

    {
  "statement": "SQL_SELECT_QUERY",
  "outputConfig": {
    "bigqueryDestination": {
      "dataset": "projects/BIGQUERY_PROJECT_ID/datasets/DATASET_ID",
      "table": "TABLE_NAME",
      "writeDisposition": "WRITE_METHOD"
    }
  },
  "pageSize": "PAGE_SIZE"
}

    Provide the following values:

  • SCOPE_PATH: Use one of the following values:

    The allowed values are:

  • BILLING_PROJECT_ID: The project ID that the default Cloud Asset Inventory service agent is in that has permissions to manage your BigQuery datasets and tables. Read more about setting the billing project.

  • SQL_SELECT_QUERY: An SQL SELECT query.
  • BIGQUERY_PROJECT_ID: The ID of the project that the BigQuery table is in that you want to export to.
  • DATASET_ID: The ID of the BigQuery dataset.
  • TABLE_NAME: The BigQuery table that you're exporting your metadata to. If it doesn't exist, it's created.

  • WRITE_METHOD: Specifies the behavior if the BigQuery destination table or partition already exists. The following values are supported:

    • PAGE_SIZE: Optional. The number of results to return per page. The maximum is 500. If the value is set to 0 or a negative value, an appropriate default is selected. A nextPageToken is returned to retrieve subsequent results.

    Command examples

    Run one of the following commands to get the names and asset types of the first two Compute Engine instances in the my-project project, and export the results to the my-table BigQuery table in the my-project project, overwriting the entire table if it already exists.

    curl (Linux, macOS, or Cloud Shell)

    curl -X POST \
     -H "X-Goog-User-Project: BILLING_PROJECT_ID" \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d '{
            "statement": "
              SELECT
                name, assetType
              FROM
                compute_googleapis_com_Instance
              LIMIT 2",
            "outputConfig": {
              "bigqueryDestination": {
                "dataset": "projects/my-project/datasets/my-dataset",
                "table": "my-table",
                "writeDisposition": "WRITE_TRUNCATE"
              }
            }
          }' \
     https://cloudasset.googleapis.com/v1/projects/my-project:queryAssets

    PowerShell (Windows)

    $cred = gcloud auth print-access-token

$headers = @{ 
  "X-Goog-User-Project" = "BILLING_PROJECT_ID";
  "Authorization" = "Bearer $cred"
}


$body = @"
{
  "statement": "
    SELECT
      name, assetType
    FROM
      compute_googleapis_com_Instance
    LIMIT 2",
  "outputConfig": {
    "bigqueryDestination": {
      "dataset": "projects/my-project/datasets/my-dataset",
      "table": "my-table",
      "writeDisposition": "WRITE_TRUNCATE"
    }
  }
}
"@

Invoke-WebRequest `
  -Method POST `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -Body $body `
  -Uri "https://cloudasset.googleapis.com/v1/projects/my-project:queryAssets" | Select-Object -Expand Content

    Additional SQL query examples

    The following code samples show specific SQL queries you can use to search for assets, to help you construct your own queries.

    Compute Engine VM instances in a specific region

    Additionally, return their name and when they were created.

    SELECT
  name,
  resource.DATA.creationTimestamp
FROM
  compute_googleapis_com_Instance
WHERE
  resource.location LIKE '%asia%'

    Details of an app running in a Kubernetes pod

    Return the namespace, version, and timestamp of the app ingress-nginx. Learn more about JSON functions used in BigQuery.

    SELECT
  name AS pod_name,
  JSON_EXTRACT_SCALAR(resource.data.metadata, '$.namespace') AS namespace,
  resource.data.metadata.creationTimestamp AS creation_time,
  JSON_EXTRACT_SCALAR(resource.data.metadata.labels, "$['app.kubernetes.io/name']") AS app_label,
  resource.data.metadata.labels['app.kubernetes.io/version'] AS version
FROM
  k8s_io_Pod
WHERE
  JSON_EXTRACT_SCALAR(resource.data.metadata, '$.namespace') = "default"
  AND JSON_EXTRACT_SCALAR(resource.data.metadata.labels, "$['app.kubernetes.io/name']") = "ingress-nginx"

    How many BigQuery datasets are in each project

      SELECT
    ancestor AS project,
    COUNT(*)
  FROM
    bigquery_googleapis_com_Dataset
  CROSS JOIN
    UNNEST (ancestors) AS ancestor
  WHERE
    ancestor LIKE '%project%'
  GROUP BY
    ancestor
  ORDER BY
    2 DESC

    How many Compute Engine VM instances are in each region

    SELECT
  resource.location,
  COUNT(*)
FROM
  compute_googleapis_com_Instance
GROUP BY
  resource.location

    Name and assetType of all resources in a region

    SELECT
  name,
  assetType
FROM
  STANDARD_METADATA
WHERE
  resource.location LIKE '%asia%'

    Publicly available Cloud Storage buckets

    Additionally, return their name.

    SELECT
  name
FROM
  IAM_POLICY
CROSS JOIN
  UNNEST(iamPolicy.bindings) AS binding
WHERE
  ('allUsers' IN UNNEST(binding.members)
  OR 'allAuthenticatedUsers' IN UNNEST(binding.members))
  AND assetType = 'storage.googleapis.com/Bucket'

    Subnetworks that don't have attached VM instances

    SELECT
  subnetwork_table.name
FROM
  compute_googleapis_com_Subnetwork AS subnetwork_table
LEFT JOIN (
  SELECT
    interface.subnetwork AS subnetwork
  FROM
    compute_googleapis_com_Instance
  CROSS JOIN
    UNNEST(resource.DATA.networkInterfaces) AS interface) AS instance_table
ON
  SUBSTR(subnetwork_table.name, 25) = SUBSTR(instance_table.subnetwork,38)
WHERE
  instance_table.subnetwork IS NULL
  AND NOT subnetwork_table.name LIKE '%default%'