Oh! I have slipped the surly bonds of Earth And danced the skies on laughter-silvered wings; Sunward I’ve climbed, and joined the tumbling mirth Of sun-split clouds, – and done a hundred things You have not dreamed of – wheeled and soared and swung High in the sunlit silence.
— Excerpt from "High Flight" by John Gillespie Magee 
Introduction: Into the Clouds!
Recently, we've had a series of Orbital Query Corner articles whose starting point is some kind of immediate vulnerability or threat advisory requiring urgent attention. This will not be one of those. Instead, the purpose of this article is to briefly explore a set of osquery  tables that we recently discovered here at Query Corner Headquarters, providing metadata for instances running in the AWS and Azure clouds.
Querying an AWS EC2 Instance
If your cloud provider of choice is Amazon, then the table you want is called ec2_instance_metadata, and the simplest way to use it is a custom query like this:
SELECT * FROM ec2_instance_metadata;
And the results look like this:
Notice that we have two Orbital nodes in our test environment, one EC2 instance and another named "Win1" that returned no results. We'll get back to that node in a minute, but for the things that do return results, the columns are as follows :
EC2 instance ID
EC2 instance type
Hardware architecture of this EC2 instance
AWS region in which this instance launched
Availability zone in which this instance launched
Private IPv4 DNS hostname of the first interface of this instance
Private IPv4 address of the first interface of this instance
MAC address for the first network interface of this EC2 instance
Comma separated list of security group names
If there is an IAM role associated with the instance, contains instance profile ARN
AMI ID used to launch this EC2 instance
ID of the reservation
AWS account ID which owns this EC2 instance
SSH public key. Only available if supplied at instance launch time
Querying an Azure Instance
If you're using Microsoft Azure as your cloud provider, the process is very similar – except when it's not, which we'll address in just a minute. Our custom query now looks like this:
SELECT * FROM azure_instance_metadata;
And here are the results:
In this case, the situation is reversed: Win1, running in Azure, returns results while our ES2 instance (unsurprisingly) does not; the Azure response columns are as follows :
Azure Region the VM is running in
Name of the VM
Offer information for the VM image (Azure image gallery VMs only)
Publisher of the VM image
SKU for the VM image
Version of the VM image
Linux or Windows
Update domain the VM is running in
Fault domain the VM is running in
Unique identifier for the VM
Azure subscription for the VM
Resource group for the VM
Placement group for the VM scale set
VM scale set name
Availability zone of the VM
Okay, So Now What?
Well, as we can see from the column layouts of the respective tables, each cloud environment exposes some different types of potentially useful metadata. For example, in Azure, you can use the vm_id, which is the same as the system's UUID, to pull in information from other tables. Just as a quick illustration (and to prove to ourselves that this actually works), here we're combining a few items from the system_info table with the Azure metadata table:
SELECT name, vm_id AS uuid, si.cpu_brand, si.cpu_logical_cores, si.hardware_model FROM azure_instance_metadata JOIN system_info si;
This may not be immediately useful (yet) , but it's nice to know that it actually works as expected!
Unfortunately, there's no UUID equivalent in the AWS EC2 metadata, but there are other items of potential interest, like the MAC address, IPv4 address, and local hostname associated with the first network interface on the instance.
How About Tags?
In addition to the metadata tables, there are also osquery tables for tags applied to the instances. The names of these tables are, not very surprisingly, ec2_instance_tags and azure_instance_tags for the respective services. But there's a catch for AWS, at least for the moment. Retrieving the tags of an EC2 instance requires authenticated access with the permission to perform the ec2:DescribeTags action, according to the osquery configuration docs , and there is currently no way to provide those credentials from within Orbital. (We'll let you know if and when that changes.)
Azure has no such requirement, and so for a simple proof-of-concept test, we applied a tag to our Win1 instance in the Azure Portal.
We ran a query:
SELECT * FROM azure_instance_tags;
And got this:
But What If I Use Both Clouds?
Okay, last exercise on this little jaunt through the world of cloud queries. Suppose we have instances running in both AWS and Azure, and want to get some common info on both environments at the same time? For example, both services report very similar location information for which region (and availability zone) an instance runs in. The names, of course, are not exactly the same, so we can't use a JOIN in this case. Instead, we'll use a UNION to combine the results from both tables.
Here's the trick:
SELECT location FROM azure_instance_metadata UNION SELECT region AS location FROM ec2_instance_metadata;
With the following results:
And that's about it for this edition of Orbital Query Corner. We hope that you found it interesting, and perhaps even useful. We hope to see you all again soon, around the next Query Corner!