[racktables-users] How to get the IPAddress through SQL query
- From: Ankush Grover <ankush174@xxxxxxxxx>
- To: racktables-users@xxxxxxxxxxxxx
- Date: Tue, 20 Dec 2011 17:45:06 +0530
Hi Friends,
I am running Racktables version 0.19.3 on Centos 5.5 32-bit with Mysql
5.0.x. I am trying to write a sql query to fetch the details like Object
Name, Rack Name, Rack Row Name, IPAddress etc.. Query is working fine
except I am not able to get the proper IPAddress(in dotted form) from the
IPv4Allocation table.
Mysql Query to fetch the data:
select distinct(a.name) as Server_Name, a.asset_no as Asset_Number,
c.nameas Rack_Name,
d.name as DataCenter_Name, e.ip as IPAddress from RackObject a, RackSpace
b, Rack c, RackRow d, IPv4Allocation e where a.id=b.obj
ect_id and b.rack_id=c.id and c.row_id=d.id and a.id=e.object_id group by
Server_Name;
Output:
testmachine3133Rack1Data Center12886730305
But the what is need is
testmachin3133Rack1Data Center1172.16.x.x
What sql query should I run to get the IPAddress in the CIDR or in dotted
form.
I tried the below query(taken from the racktables database.php file) but
this script is also not running and returning error.
SELECT id FROM IPv4Network AS subt WHERE IPv4Network.ip & (4294967295 >>
(32 - subt.mask)) << (32 - subt.mask) = subt.ip and subt.mask <
IPv4Network.mask ORDER BY subt.mask DESC limit 1
SELECT id FROM IPv4Network AS subt WHERE IPv4Network.ip & (4294967295 >>
(32 - subt.mask)) << (32 - subt.mask) = subt.ip and subt.mask <
IPv4Network.mask ORDER BY subt.mask DESC limit 1
-> ;
ERROR 1054 (42S22): Unknown column 'IPv4Network.ip' in 'where clause'
Please share the correct sql query to get the proper ipaddress.
Thanks & Regards
Ankush Grover
Other related posts:
