So.....no? Has anyone out there every tried to do an audit of what security groups are assigned to what AWS EC2 instances across an entire environment? It's freakin' nightmare...if i could just throw that stuff on an Orion report it sure would be sweet....
or maybe someone (like aLTeReGo ) could point me in the direction of a database table where some of this information (cloud instance IP addresses, attached security groups) is stored so i can try to hack together some kind of custom sql report...?
I am looking into this for you... I did some digging and I was able to crack part of it into a query but I am still
coming up empty on the IP addresses. Those don't seem to be in the places I would expect.
There are tables with a prefix VIM_Cloud and CLM that hold most of the data you are looking for.
Try the query below for starters on the security groups and I will ping back once I figure out where we stashed the IPs
SELECT ci.Name, ci.PublicDNSName, ci.PrivateDNSName, csg.SecurityGroupID, csg.SecurityGroupName FROM VIM_CloudInstanceNodes ci, VIM_CloudSecurityGroups csg
WHERE ci.VirtualMachineID = csg.VirtualMachineID
Here is a more complete SWQL query that should get the IP Addresses as well:
SELECT ci.Name, ci.OrionIdColumn,PublicDNSName, ci.PrivateDNSName, csg.SecurityGroupId, csg.SecurityGroupName, vmip.IPAddress
FROM Orion.Cloud.Instances ci, Orion.Cloud.SecurityGroups csg, orion.VIM.VirtualMachineIPAddresses vmip
WHERE ci.VirtualMachineID = csg.VirtualMachineId AND ci.VirtualMachineID = vmip.VirtualMachineID