Results 1 to 9 of 9
  1. #1
    Join Date
    May 2007
    Posts
    4

    Unanswered: MS SQL - Query Analyzer by IP range

    I need to run reports from our database that stores IP addresses when a software license is used. So, I need to know how to run SQL queries to retrieve certain IP addresses from specified ranges. For example:

    192.168.1.0 - 192.168.2.255

    192.168.3.11 - 192.168.7.128

    10.10.10.8 - 10.10.12.255

    What commands would help me accomplish pulling all IP addresses in a range. I know I would have to run multiple reports for each range. I tried using the "between" function and "less than/greater than" functions, but I received mixed results. For example:

    SELECT * FROM testtable WHERE ip LIKE '192.168.[1-2].[0-255]'

    I also tried -

    SELECT * FROM testtable WHERE ip>'192.168.1.0' and ip<'192.168.2.255'

    Any help would be greatly appreciated! Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Parse your IP addresses into four separate columns. That would make it easy to search or filter by ranges.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2007
    Posts
    4
    blindman, thank you for the feedback... however, the original DB configured the IP address as it's own field... at this point, there is over 10million or so records, so modifying the DB would be time consuming... any other thoughts would be greatly appreciated...

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Create a function called IP2DWord or so. Its pretty straigtforward. First octet * 256^3 + second octet * 256^2 + third octet*256 + fourth octet. If you use this function on both starting and ending address you can easily select ranges.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Define: "time consuming"?
    There have been a number of questions about IP addresses in a range - I suggest you search the forum www.dbforums.com/search.php and there were some very good suggestions around and about (see above ).
    George
    Home | Blog

  6. #6
    Join Date
    May 2007
    Posts
    4
    roac, i looked into your suggestion and it seems to be the way to go... thnx all for the feedback...

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you parse the IP address on the fly using a function, then you can toss any indexing out the window. On 10,000,000+ records don't expect a great response time.
    You asked for advice, and I gave it. You are FAR better off parsing the IP Address into four separate columns in your table. You can index them, and you only need to parse each address once. If you absolutely have to have the IP Address as a single column in your table, then keep it and create four additional columns in the table that you populate automatically using insert/update triggers.
    You can do this right, or you can do this wrong. You can take shortcuts now, or you can save programming, debugging, and execution time over the life of the application.
    'Nuff said.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    May 2007
    Posts
    4
    For reporting purposes it is required for the IP address to be in 1 column. I will look into separating each IP segment into it's on column, as I agree there's more flexibility when running reports specifically for IP ranges.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rbandoy
    For reporting purposes it is required for the IP address to be in 1 column.
    Reporting purposes do not require the data to be in one column. A view, sproc, or SQL statement can easily combine the components for reporting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •