Powershell Multiple variables in foreach loop getting AWS-EC2 instance info

Question

I need to get instanceId and PrivateIpAddress from all my EC2 instances in my environment and insert into table. I have this but doesn't seem to work. I seem to get everything not just the IP and ID.

$instancestate = (get-ec2instance).RunningInstance 

foreach ($instances in $instancestate)
{
 $query = "INSERT INTO prodinstances (Idinstance, IPAddress)
VALUES ('$instances.InstanceId','$instances.PrivateIpAddress')" 
$Rows = Execute-MySQLNonQuery $conn $query 
}

If I change the code

$instancestate = (get-ec2instance).RunningInstance.InstanceId

I get the ID and can insert it in the database. I can also change it to

$instancestate = (get-ec2instance).RunningInstance.PrivateIpAddress

and get the IPAddress and insert that into the database, but when i combine them I get all the info for the EC2 instances which does have .instanceId and .PrivateIpAddress in the list when I hover over the variable $instances. Any Idea how to get both those parameters. My code seems correct but alas it is not...

TessellatingHeckler · Accepted Answer · 2016-07-07 03:38:58Z

1

"VALUES ('$instances.InstanceId'"

is the same as

"VALUES ('" + $instances + ".InstanceId'"

Now it doesn't seem correct. You need $() around it, inside the string:

"VALUES ('$($instances.InstanceId)'"

answered Jul 7, 2016 at 3:38

TessellatingHeckler

29.3k4 gold badges55 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tmac Over a year ago

Thanks, i figured it out last night by assigning the output to a variable like this, but your way @TessellatingHecker is much more eloquent... $MyID =$instances.InstanceId $MyIP = $instances.PrivateIpAddress $query = "INSERT INTO prodinstances (Idinstance, IPAddress) VALUES ('$MyID','$MyIP')" $Rows = Execute-MySQLNonQuery $conn $query }

tmac Over a year ago

I had to change the code and insert only if does not exist but this doesn't seem to be working, it inserts regardless

$instancestate = (get-ec2instance).RunningInstance  foreach ($instances in $instancestate) { $query = "INSERT INTO prodinstances (Idinstance, IPAddress) SELECT Idinstance, IPAddress FROM prodinstances  WHERE NOT EXISTS (SELECT Idinstance, IPAddress FROM prodinstances  WHERE Idinstance = '$($instances.InstanceId)' and IPAddress = '$($instances.PrivateIpAddress)')"    $Rows = Execute-MySQLNonQuery $conn $query  }

tmac · Accepted Answer · 2016-07-07 16:30:14Z

1

Fixed works like a charm...

$instancestate = (get-ec2instance).RunningInstance 
foreach ($instances in $instancestate)
{

$query = "insert into prodinstances (idinstance,IPAddress) VALUES  ('$($instances.InstanceId)', '$($instances.PrivateIpAddress)') 
ON duplicate key update    idinstance='$($instances.InstanceId)',IPAddress='$($instances.PrivateIpAddr ess)'"
$Rows = Execute-MySQLNonQuery $conn $query

}

answered Jul 7, 2016 at 16:30

tmac

377 bronze badges

Collectives™ on Stack Overflow

Powershell Multiple variables in foreach loop getting AWS-EC2 instance info

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related