Large-scale deployments on Google Compute Engine may consist of hundreds of dynamic and transient components such as instances, disks, networks, and firewall rules. To manage and debug these resources, we often have to look back in time to find answers to questions such as:
  • What was the distribution of instances across zones for the past month?
  • What were the instances with production tag T at time range X?
  • What instances used an external IP address X?
  • What was the aggregated size of disks by zone for the past 7 days?

Wouldn’t it be wonderful if there was a timeline to query using a familiar language such as SQL that has the interactive speed of BigQuery? Building on the Data Pipeline sample application, we have published the Cloud History Tool for Google Compute Engine pipeline that:
  1. Reads the current Compute Engine instance, disk, and operations data using the Compute Engine REST API.
  2. Transforms the data.
  3. Loads the data into BigQuery.
  4. Uses App Engine Cron Services to repeat steps 1 - 3 at scheduled intervals.
Viola! The timeline is now available in BigQuery ready for analysis. The sample also includes a SQL query cookbook to help you to get started with timeline analysis. For example, you can run the following query from the BigQuery Web Interface to find out which disks were not used and not attached to any instances at a given time:
SELECT D.name, D.zoneName
FROM [cloud_history.Disks] D
LEFT OUTER JOIN FLATTEN([cloud_history.Instances], disks) I
  ON D.name = I.disks.deviceName AND D.snapshotId = I.snapshotId
WHERE
  I.disks.deviceName IS NULL AND
  D.snapshotId = TIMESTAMP("YYYY-MM-DD hh:mm:ss")
To display your results graphically, follow this tutorial on “Updating Google Spreadsheets with data from Google BigQuery”. Here is a sample spreadsheet that shows the average number of Compute Engine instances deployed across zones over time.

The instructions for setting up the Cloud History Tool are available here. We invite you to try it out and extend it to suit your needs.

-Posted by Wally Yau, Solutions Architect