A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://cloud.google.com/asset-inventory/docs/query-assets-with-sql below:

Query assets with SQL | Cloud Asset Inventory Documentation

Skip to main content Query assets with SQL

Stay organized with collections Save and categorize content based on your preferences.

Note: This feature requires access to the Security Command Center Premium or Enterprise tier, or Gemini Cloud Assist. For new sign ups to the Security Command Center Premium or Enterprise tier, it can take up to 6 hours for access to the API, and up to 48 hours for historical data to be ready.

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:

    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:

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) Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell, which automatically logs you into the gcloud CLI. You can check the active account by running gcloud auth list. 
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) Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login. You can check the active account by running gcloud auth list. 
$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) Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell, which automatically logs you into the gcloud CLI. You can check the active account by running gcloud auth list. 
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) Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login. You can check the active account by running gcloud auth list. 
$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:

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) Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell, which automatically logs you into the gcloud CLI. You can check the active account by running gcloud auth list. 
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) Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login. You can check the active account by running gcloud auth list. 
$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%'

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["This feature lets you query assets within your project, folder, or organization using a BigQuery SQL-compatible dialect, but requires Security Command Center Premium or Enterprise tier, or Gemini Cloud Assist access."],["You must enable the Cloud Asset Inventory API and have the correct roles to use the API for querying."],["Queries must be in `SELECT` format, use standard BigQuery SQL, have results under 10GB, and complete in under 6 hours."],["You can query different tables depending on whether the content type is `RESOURCE` or non-`RESOURCE`, and you can also use the `STANDARD_METADATA` table for resource standard metadata."],["Queries can be run using the Google Cloud console, `gcloud` CLI, or REST API, and you can export the results to a BigQuery dataset in the `US` multi-region."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4