AWS Inventory In SQL

• Tweet
• 

        // AWS inventory to relational database

#!/bin/sh

db_name="aws_inventory"
mysql -e"drop database $db_name"
mysql -e"create database $db_name"

grep -w ^INSTANCE /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/instance_list.txt
grep -w ^TAG /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/instance_tag.txt
grep -w ^VOLUME /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/volume_list.txt
grep -w ^ATTACHMENT /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/volume_attachment.txt
grep -w ^IMAGE /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/image_list.txt
grep -w ^BLOCKDEVICE /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/blockdevice.txt
grep -w ^SNAPSHOT /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/snapshot_list.txt
grep -w ^AVAILABILITYZONE /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/zone_list.txt
grep -w ^ADDRESS /home/vserv/ec2_audit.txt > /var/lib/mysql/$db_name/address_list.txt


mysql $db_name << "my_heredoc"
set foreign_key_checks=0;

create table volume_attachment (
volume_name varchar(100),
volume_id varchar(100),
volume_instance varchar(100),
volume_mount varchar(100),
volume_status varchar(100),
volume_date varchar(100),
primary key (volume_id, volume_instance),
key (volume_instance),
constraint volume_attach_id foreign key (volume_id) references volume_list(volume_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE instance_list (
  instance_name varchar(100) DEFAULT NULL,
  instance_id varchar(100) DEFAULT NULL,
  image_id varchar(100) DEFAULT NULL,
  instance_ip varchar(100) DEFAULT NULL,
  instance_private_zone varchar(100) DEFAULT NULL,
  instance_status varchar(100) DEFAULT NULL,
  instance_keypair varchar(100) DEFAULT NULL,
  filler1 varchar(100) DEFAULT NULL,
  filler2 varchar(100) DEFAULT NULL,
  instance_type varchar(100) DEFAULT NULL,
  instance_date varchar(100) DEFAULT NULL,
  instance_zone varchar(100) DEFAULT NULL,
  instance_kernel varchar(100) DEFAULT NULL,
  instance_r varchar(100) DEFAULT NULL,
  filler3 varchar(100) DEFAULT NULL,
  instance_monitoring varchar(100) DEFAULT NULL,
  instance_ip_public varchar(100) DEFAULT NULL,
  instance_private varchar(100) DEFAULT NULL,
  filler4 varchar(100) DEFAULT NULL,
  filler5 varchar(100) DEFAULT NULL,
  instance_ebs varchar(100) DEFAULT NULL,
  instance_spot varchar(100) DEFAULT NULL,
  instance_code varchar(100) DEFAULT NULL,
  filer6 varchar(100) DEFAULT NULL,
  filler7 varchar(100) DEFAULT NULL,
  filler8 varchar(100) DEFAULT NULL,
  instance_details varchar(100) DEFAULT NULL,
  primary key (instance_id),
  key (instance_zone),
  key(image_id),
constraint volume_attachment_instance foreign key (instance_id) references volume_attachment (volume_instance),
constraint image_list_id foreign key (image_id) references image_list(image_id),
constraint zone_list foreign key (instance_zone) references zone_list(zone_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE instance_tag (
  tag_name varchar(100) DEFAULT NULL,
  tag_type varchar(100) DEFAULT NULL,
  tag_instance varchar(100) DEFAULT NULL,
  tag_status varchar(100) DEFAULT NULL,
  tag_details varchar(100) DEFAULT NULL,
  key (tag_instance),
constraint instance_tag_name foreign key (tag_instance) references instance_list(instance_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


create table snapshot_list (
snap_name varchar(100), 
snap_id varchar(100), 
snap_vol varchar(100), 
snap_status varchar(100), 
snap_date varchar(100), 
snap_percent varchar(100), 
snap_owner varchar(100), 
snap_filler varchar(100), 
snap_created varchar(100),
primary key (snap_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


create table volume_list (
volume_name varchar(100),
volume_id varchar(100),
volume_filler varchar(100),
volume_snap varchar(100),
volume_zone varchar(100),
volume_status varchar(100),
volume_date varchar(100),
primary key (volume_id), 
key (volume_zone),
key (volume_snap),
constraint volume_list_id foreign key (volume_zone) references instance_list(instance_zone),
constraint volume_list_snap foreign key (volume_snap) references snapshot_list(snap_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 

create table image_list (
image_name varchar(100), 
image_id varchar(100), 
image_details varchar(100), 
image_owner varchar(100), 
image_status varchar(100), 
image_private varchar(100), 
image_filler varchar(100),
image_bit varchar(100), 
image_machine varchar(100),
image_kernel varchar(100), 
image_filler1 varchar(100),
image_filler2 varchar(100),
image_ebs varchar(100), 
image_paravirtual varchar(100) ,
primary key(image_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


create table blockdevice (
block_name varchar(100), 
block_mount varchar(100), 
block_id varchar(100), 
block_filler varchar(100) ,
primary key(block_id),
constraint blockdevice_id foreign key (block_id) references volume_attachment(volume_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE zone_list (
  filler varchar(100) DEFAULT NULL,
  zone_id varchar(100) DEFAULT NULL,
  filler1 varchar(100) DEFAULT NULL,
  region varchar(100) DEFAULT NULL,
  primary key (zone_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE address_list (
  filler varchar(100) DEFAULT NULL,
  public_ip varchar(100) DEFAULT NULL,
  instance_id varchar(100) DEFAULT NULL,
  primary key (public_ip), 
  key (instance_id),
constraint address_list_instance_id foreign key (instance_id) references instance_list(instance_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


load data infile 'instance_list.txt' into table instance_list fields terminated by '\t';
load data infile 'instance_tag.txt' into table instance_tag fields terminated by '\t';
load data infile 'volume_list.txt' into table volume_list fields terminated by '\t';
load data infile 'volume_attachment.txt' into table volume_attachment fields terminated by '\t';
load data infile 'image_list.txt' into table image_list fields terminated by '\t';
load data infile 'blockdevice.txt' into table blockdevice fields terminated by '\t';
load data infile 'snapshot_list.txt' into table snapshot_list fields terminated by '\t';
load data infile 'zone_list.txt' into table zone_list fields terminated by '\t';
load data infile 'address_list.txt' into table address_list fields terminated by '\t';

my_heredoc

Tags:
• aws