You can access Censys datasets in BigQuery through a web UI, command-line tool, and REST API as well as through Dataproc (Google's Spark and Hadoop offering). There are also a variety of third-party tools—like Looker and Tableau—that you can use to interact with Censys BigQuery data.
We expose four datasets through BigQuery:
- Certificates. We have a single certificates table (
certificates_public.certificates) that contains all unique X.509 certificates we've found during any of our scans and by synchronizing with publicly known CT servers. [more information]
- Domains. We provide a daily snapshot (
domain_public.current) of the current state of the Alexa Top Million Domains collection. [more information]
- IPv4. We provide a daily snapshots (
ipv4_public.current) of the current state of known IPv4 hosts and how public protocols are configured. [more information]
- IPv4 Banners. IPv4 banners provides a daily snapshot (
ipv4_banners_public.current) of our continuous light-weight scanning. We are continuously scanning IPv4 hosts and populating information on 2029 of the most popular ports. [more information]
BigQuery's SQL dialect is compliant with the SQL 2011 standard and you can query tables once you have added the Censys datasets [more information].
Here are a few simple examples:
Calculate the breakdown of cryptographic keys for certificates:
SELECT COUNT(*), parsed.subject_key_info.key_algorithm.name
GROUP BY parsed.subject_key_info.key_algorithm.name
Find websites using a specific cipher suite:
SELECT domain, alexa_rank
WHERE p443.https.tls.cipher_suite.name = 'TLS_RSA_WITH_AES_256_CBC_SHA'
SELECT ip, autonomous_system.description
WHERE p80.http.get.headers.server LIKE '%Apache%'
Standard SQL Only
You need to use Standard SQL to query the Censys datasets, which is now the default for Google BigQuery. If you have issues running queries, confirm that your query is using Standard SQL by opening the query editor and selecting the "More" button.
In the drop-down menu, select "Query Settings" to open a window similar to the one below. Confirm that any references to Legacy SQL are unselected.
Another option is to include the following command at the top of your SQL statement in the web interface as shown below:
<SQL GOES HERE>
For more information on constructing SQL statements, check out the BigQuery SQL Reference.
A couple of notes from the Censys development team:
- Censys datasets are extremely wide (often containing thousands of columns). This means that seemingly simple queries (e.g., finding a single record about a host:
SELECT * FROM ipv4_public.current WHERE ip = '126.96.36.199'will process several terabytes of data. This can be costly ($5-10 for the single query) if you're not careful. You can easily reduce these costs by only selecting the columns you need and by combining several small queries into a single larger query.
- Standard SQL Only. BigQuery does not allow mixing standard and legacy SQL. This means that Censys views can only be queried using Standard SQL.
- No Direct Export Jobs. The results of queries against Censys views cannot be directly exported to a file on Google Cloud Storage. If you want to export results, you'll need to save the results of your query to a temporary table and export that to an external file.
- No Wildcard Queries. You cannot reference a view in a wildcard table query. Instead, you'll need to explicitly reference the tables you want to query.
If these limitations are causing significant problems, please reach out to our team at firstname.lastname@example.org.
Connecting to BigQuery in a Script
There are two methods of authenticating against Google BigQuery in a non-interactive environment:
- Your User Account. You can use OAUTH if you're running a script personally. [more information]
- Service Account. The service accounts you create in your Google Cloud Project will not automatically be granted access to the Censys BigQuery datasets (access is only granted to your user account and this does not propagate to the cloud project). If you'd like to have a service account added, please reach out to us at email@example.com.