steampipe plugin install aws

Table: aws_lightsail_instance - Query AWS Lightsail Instances using SQL

The AWS Lightsail Instance is a part of Amazon Lightsail service, providing a simple virtual private server solution. It offers easy-to-use instances with a variety of applications and stacks, including WordPress, Joomla, and more. These instances are ideal for simpler workloads, quick deployments, and developers seeking a smooth transition to the cloud.

Table Usage Guide

The aws_lightsail_instance table in Steampipe provides you with information about instances within AWS Lightsail. This table allows you, as a DevOps engineer, to query instance-specific details, including instance state, attached static IP, associated bundles, and associated tags. You can utilize this table to gather insights on instances, such as instances with specific tags, instances in a certain state, instances associated with a specific bundle, and more. The schema outlines the various attributes of the Lightsail instance for you, including the instance name, creation timestamp, location, blueprint ID, and more.

Examples

Instance count in each availability zone

Determine the distribution of instances across different availability zones to effectively manage resources and optimize performance.

select
availability_zone as az,
bundle_id,
count(*)
from
aws_lightsail_instance
group by
availability_zone,
bundle_id;
select
availability_zone as az,
bundle_id,
count(*)
from
aws_lightsail_instance
group by
availability_zone,
bundle_id;

List stopped instances created for more than 30 days

Determine the areas in which AWS Lightsail instances have been inactive for more than 30 days. This query is useful for identifying potential resource inefficiencies and cost-saving opportunities.

select
name,
state_name
from
aws_lightsail_instance
where
state_name = 'stopped'
and created_at <= (current_date - interval '30' day);
select
name,
state_name
from
aws_lightsail_instance
where
state_name = 'stopped'
and created_at <= date('now', '-30 day');

List public instances

Identify instances where your AWS Lightsail instances are publicly accessible. This is useful for reviewing your network security and ensuring your instances are not exposed to unnecessary risks.

select
name,
state_name,
bundle_id,
region
from
aws_lightsail_instance
where
public_ip_address is not null;
select
name,
state_name,
bundle_id,
region
from
aws_lightsail_instance
where
public_ip_address is not null;

List of instances without application tag key

Analyze your AWS Lightsail instances to identify those that do not have an 'application' tag assigned. This can help streamline your resource management and ensure consistent tagging practices across your cloud environment.

select
name,
tags
from
aws_lightsail_instance
where
not tags :: JSONB ? 'application';
select
name,
tags
from
aws_lightsail_instance
where
not json_valid(tags)
or json_extract(tags, '$.application') is null;

Hardware specifications of the instances

Explore the hardware specifications of your instances to assess their computing power and memory capacity. This is particularly useful in optimizing resource allocation and performance in your AWS Lightsail instances.

select
name,
hardware ->> 'CpuCount' as "CPU Count",
hardware ->> 'RamSizeInGb' as "RAM Size (in GB)"
from
aws_lightsail_instance;
select
name,
json_extract(hardware, '$.CpuCount') as "CPU Count",
json_extract(hardware, '$.RamSizeInGb') as "RAM Size (in GB)"
from
aws_lightsail_instance;

Schema for aws_lightsail_instance

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
add_onsjsonbAn array of objects representing the add-ons enabled on the instance.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntextThe Amazon Resource Name (ARN) specifying the instance.
availability_zonetextThe Availability Zone where the instance is located.
blueprint_idtextThe blueprint ID (e.g., os_amlinux_2016_03).
blueprint_nametextThe friendly name of the blueprint (e.g., Amazon Linux).
bundle_idtextThe bundle for the instance (e.g., micro_1_0).
created_attimestamp with time zoneThe timestamp when the instance was created.
hardwarejsonbThe size of the vCPU and the amount of RAM for the instance.
ip_address_typejsonbThe IP address type of the instance.
ip_v6_addressesjsonbThe IPv6 addresses of the instance.
is_static_ipbooleanA Boolean value indicating whether this instance has a static IP assigned to it.
metadata_optionsjsonbThe metadata options for the Amazon Lightsail instance.
nametext=The name of the instance.
networkingjsonbInformation about the public ports and monthly data transfer rates for the instance.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
private_ip_addresstextThe private IP address of the instance.
public_ip_addresstextThe public IP address of the instance.
regiontextThe AWS Region in which the resource is located.
resource_typetextThe type of resource.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
ssh_key_nametextThe name of the SSH key being used to connect to the instance.
state_codebigintThe status code for the instance.
state_nametextThe status of the instance.
support_codetextThe support code.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags assigned to the instance.
titletextTitle of the resource.
usernametextThe user name for connecting to the instance.

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)" -- aws

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

steampipe_export_aws --config '<your_config>' aws_lightsail_instance