Showing results for 
Search instead for 
Did you mean: 
Cisco Employee
Cisco Employee

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 [0]

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 [1] 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 [2]:


Column Name Description
instance_id EC2 instance ID
instance_type EC2 instance type
architecture Hardware architecture of this EC2 instance
region AWS region in which this instance launched
availability_zone Availability zone in which this instance launched
local_hostname Private IPv4 DNS hostname of the first interface of this instance
local_ipv4 Private IPv4 address of the first interface of this instance
mac MAC address for the first network interface of this EC2 instance
security_groups Comma separated list of security group names
iam_arn If there is an IAM role associated with the instance, contains instance profile ARN
ami_id AMI ID used to launch this EC2 instance
reservation_id ID of the reservation
account_id AWS account ID which owns this EC2 instance
ssh_public_key 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 [3]:


Column Name Description
location Azure Region the VM is running in
name Name of the VM
offer Offer information for the VM image (Azure image gallery VMs only)
publisher Publisher of the VM image
sku SKU for the VM image
version Version of the VM image
os_type Linux or Windows
platform_update_domain Update domain the VM is running in
platform_fault_domain Fault domain the VM is running in
vm_id Unique identifier for the VM
vm_size VM size
subscription_id Azure subscription for the VM
resource_group_name Resource group for the VM
placement_group_id Placement group for the VM scale set
vm_scale_set_name VM scale set name
zone 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 [4], 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
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!

Links and References

[0] High Flight

[1] Orbital Help: What Is Osquery

[2] osquery/specs/ec2_instance_metadata.table on GitHub

[3] osquery/specs/azure_instance_metadata.table on GitHub



Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: