v1.0 for Steampipe, Powerpipe, Flowpipe, 116 plugins, and 44 mods →

turbot/googlesearchconsole

steampipe plugin install googlesearchconsolesteampipe plugin install googlesearchconsole

googlesearchconsole_indexing_status googlesearchconsole_pagespeed_analysis googlesearchconsole_pagespeed_analysis_aggregated googlesearchconsole_site googlesearchconsole_sitemap

Table: googlesearchconsole_indexing_status - Query indexing status of all web pages using SQL

Results of index status inspection for either the live page or the version in Google's index, depending on whether you requested a live inspection or not

Table Usage Guide

The googlesearchconsole_indexing_status table allows users to analyze the index status of pages on their site, including details on coverage state, indexing state, and referring URLs. It's essential for understanding how well your web pages are represented in Google Search.

Important Notes You must specify the following columns in where or join clause to query the table:

site_url: The URL of the property as defined in Search Console. Examples: http://www.example.com/ for a URL-prefix property, or sc-domain:example.com for a Domain property
sitemap_url: The URL of the sitemap that was submitted to Google Search Console. Example: https://www.example.com/sitemap.xml

Examples

Basic indexing status info

Retrieve basic details about the indexing status of pages, including the page's location (URL), coverage state, indexing state, and URLs referring to the page within the sitemap.

select
  loc,
  coverage_state,
  indexing_state,
  referring_urls
from
  googlesearchconsole_indexing_status
where
  sitemap_url = 'https://example.io/sitemap-0.xml'
  and site_url = 'https://example.io/';

select
  loc,
  coverage_state,
  indexing_state,
  referring_urls
from
  googlesearchconsole_indexing_status
where
  sitemap_url = 'https://example.io/sitemap-0.xml'
  and site_url = 'https://example.io/';

List unindexed URLs

Identify URLs within a sitemap that are not indexed by Google. This query helps in pinpointing pages that might need further optimization or review to meet Google's indexing requirements.

select
  loc,
  coverage_state,
  indexing_state
from
  googlesearchconsole_indexing_status
where
  sitemap_url = 'https://example.io/sitemap-0.xml'
  and site_url = 'https://example.io/'
  and coverage_state <> 'Submitted and indexed';

select
  loc,
  coverage_state,
  indexing_state
from
  googlesearchconsole_indexing_status
where
  sitemap_url = 'https://example.io/sitemap-0.xml'
  and site_url = 'https://example.io/'
  and coverage_state <> 'Submitted and indexed';

Get page count by indexing status

This query provides a count of pages grouped by their coverage state. It's useful for assessing the overall indexing health of your site and identifying potential areas for improvement.

select
  coverage_state,
  count(*) as page_count
from
  googlesearchconsole_indexing_status
where
  sitemap_url = 'https://example.io/sitemap-0.xml'
  and site_url = 'https://example.io/'
group by
  coverage_state;

select
  coverage_state,
  count(*) as page_count
from
  googlesearchconsole_indexing_status
where
  sitemap_url = 'https://example.io/sitemap-0.xml'
  and site_url = 'https://example.io/'
group by
  coverage_state;

Schema for googlesearchconsole_indexing_status

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
coverage_state	text		Could Google find and index the page.
crawled_as	text		Primary crawler that was used by Google to crawl your site.
google_canonical	text		The URL of the page that Google selected as canonical. If the page was not indexed, this field is absent.
indexing_state	text		Whether or not the page blocks indexing through a noindex rule.
last_crawl_time	text		Last time this URL was crawled by Google using the primary crawler.
loc	text	=	The URL of the page.
page_fetch_state	text		Whether or not Google could retrieve the page from your server.
referring_urls	jsonb		URLs that link to the inspected URL, directly and indirectly.
result_link	text		Link to Search Console URL inspection.
robots_txt_state	text		Whether or not the page is blocked to Google by a robots.txt rule.
site_url	text	=	The URL of the site.
sitemap_url	text	=	The URL of the sitemap.
user_canonical	text		The URL that your page or site declares as canonical.
verdict	text		High level verdict about whether the URL is indexed (indexed status), or can be indexed (live inspection)

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- googlesearchconsole

You can pass the configuration to the command with the --config argument:

steampipe_export_googlesearchconsole --config '<your_config>' googlesearchconsole_indexing_status