I would like to implement attribute type IPaddress in CIDR notation, so storing both IP and mask in same field.
Main issue is the listing of NetworkInterfaces connected to a SubNet. For that I need to establish the max and minimum IPs of the subnet and list CIDR IPs between them.
The big problem here is that INET_ATON function don't work with CIDR notation and OQL don't support regular SQL string functions like LEFT and INSTR to trim the mask prefix.
Additionaly cannot be treated as string as the alphabetical order don't match the IP/long order of the IPs (For example 192.168.1.15 goes before 192.168.1.2 despite being a an IP lower than).
I think about store it internally as two SQL fields (as IP+prefix or IP+mask as is currently designed), but then I have the problem of dealing with a 'computed' field: How I let user enter as only one field and show it on the UI as an unique field. Additionaly how I manage to do OQL queries of one field which in reality are two SQL fields ?
Well, once I achieved this, my next step is (IMHO) a good enhancement: Make IPaddress hyperlinks automatically with the subnet which belongs without using AttribExternalKey (as this can be IPcalc computed from the field itself).
I think I can achieve this overwriting the asHTML method to return the hyperlink to the parent subnet.
Hence my questions are:
- How can I implement CIDR? using two fields? implement OQL functions for string or CIDR?
- Which is better way to go on?
I hope I explained well my problem and intention, don't hesitate to comment/suggest/ask details, please.
Thanks much!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
you should have a look into the OQL documentation and maybe also the examples. You can use JOIN to join two tables on a key.
To compute the IP`s of a subnet, you can write your own function and explode the ip and calc the range depending on the netmask.
The behavior of the sorting is normal because "sort" sorts normally each alphanumerical string by string and 15 comes before 2 because 1 is lower than 2. You can either change that behavior of sort or just explode the ip and sort sort the array values. http://php.net/manual/de/function.sort.php <sort flags> maybe won`t work because of the dots. (not sure)
I would explode the ip and netmask because the array will have always 4 key-value pairs in the right order and you can go through each key-value pair and compare it with the netmask.
Sorry if I misunderstood you. I just try to get your needs. I`m not using the Network functions of iTop.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
JOIN is not useful here. What I want is something "SELECT NetworkInterface WHERE ip.address < subnet.ipmax AND ip.address >= subnet.ipmin (see the code on subnet class function DisplayBareRelations)
Where ip.addrees is a string in CIDR notation and subnet.ipmax / ipmin are the min and max IPs computed for the subnet. What I prepend is to link IPs with subnet without the need of using a key (only using IP subnet calculation) and as I don't use an AttributeExternalKey I cannot use JOIN because I don't have the key to JOIN ON.
Computing IPs of a subnet is easy, but is not efficient to compute a list of may be 65536 IPs (the IPs on a class B network for example) and then search one by one on the NetworkInterface table. Better to do it at OQL/SQL level.
Additionally I am not trying to do a sort at PHP code level I need to be able to compare IPs at OQL level to be sure that an IP address is inside a subnet (look the code of SubNet class as example).
Thanks
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I would like to implement attribute type IPaddress in CIDR notation, so storing both IP and mask in same field.
Main issue is the listing of NetworkInterfaces connected to a SubNet. For that I need to establish the max and minimum IPs of the subnet and list CIDR IPs between them.
The big problem here is that INET_ATON function don't work with CIDR notation and OQL don't support regular SQL string functions like LEFT and INSTR to trim the mask prefix.
Additionaly cannot be treated as string as the alphabetical order don't match the IP/long order of the IPs (For example 192.168.1.15 goes before 192.168.1.2 despite being a an IP lower than).
I think about store it internally as two SQL fields (as IP+prefix or IP+mask as is currently designed), but then I have the problem of dealing with a 'computed' field: How I let user enter as only one field and show it on the UI as an unique field. Additionaly how I manage to do OQL queries of one field which in reality are two SQL fields ?
Well, once I achieved this, my next step is (IMHO) a good enhancement: Make IPaddress hyperlinks automatically with the subnet which belongs without using AttribExternalKey (as this can be IPcalc computed from the field itself).
I think I can achieve this overwriting the asHTML method to return the hyperlink to the parent subnet.
Hence my questions are:
- How can I implement CIDR? using two fields? implement OQL functions for string or CIDR?
- Which is better way to go on?
I hope I explained well my problem and intention, don't hesitate to comment/suggest/ask details, please.
Thanks much!
Hi,
you should have a look into the OQL documentation and maybe also the examples. You can use JOIN to join two tables on a key.
To compute the IP`s of a subnet, you can write your own function and explode the ip and calc the range depending on the netmask.
The behavior of the sorting is normal because "sort" sorts normally each alphanumerical string by string and 15 comes before 2 because 1 is lower than 2. You can either change that behavior of sort or just explode the ip and sort sort the array values.
http://php.net/manual/de/function.sort.php <sort flags> maybe won`t work because of the dots. (not sure)
I would explode the ip and netmask because the array will have always 4 key-value pairs in the right order and you can go through each key-value pair and compare it with the netmask.
Sorry if I misunderstood you. I just try to get your needs. I`m not using the Network functions of iTop.
JOIN is not useful here. What I want is something "SELECT NetworkInterface WHERE ip.address < subnet.ipmax AND ip.address >= subnet.ipmin (see the code on subnet class function DisplayBareRelations)
Where ip.addrees is a string in CIDR notation and subnet.ipmax / ipmin are the min and max IPs computed for the subnet. What I prepend is to link IPs with subnet without the need of using a key (only using IP subnet calculation) and as I don't use an AttributeExternalKey I cannot use JOIN because I don't have the key to JOIN ON.
Computing IPs of a subnet is easy, but is not efficient to compute a list of may be 65536 IPs (the IPs on a class B network for example) and then search one by one on the NetworkInterface table. Better to do it at OQL/SQL level.
Additionally I am not trying to do a sort at PHP code level I need to be able to compare IPs at OQL level to be sure that an IP address is inside a subnet (look the code of SubNet class as example).
Thanks