Tuesday, September 22, 2009

Mysql Ipaddress subnet wise search

I was encountered to search mysql table with ip address based on subnetmask information. This was part of the netflow tools for our internal use. I exported the flow data from flow-export to mysql database. I created the database "ip address" as Text.The built-in mysql function makes life easy to search the table.

According to Mysql ref: if an ip is 209.207.224.40
The generated number is always in network byte order. For the example just shown, the number is calculated as 209×(256^3) + 207×(256^2) + 224×256 + 40.

To search subnet we need add relevant no # host data to the search.
for example to search 216.239.60.0/23
i need to convert the network detail to number - 3639557120
since /23 i need to add 512 to the network figure so 3639557632
Then if i could write the query .. like this works fine..

mysql> select dstaddr ,dpkts,srcaddr,srcport,dstport from Raw1253672304 where INET_ATON(dstaddr) > 3639557120 and INET_ATON(dstaddr) <>

May be there can be easy way.