How to sort and deal with IP addresses in MSSQL

  • When you get IP addresses from logs... it will as a string.
  • Sorting and ordering as a string will give the following 11.x.x.x, then 192.x.x.x, then 201.x.x.x and finally 32.x.x.x
  • Somehow, you must split the data into each octet. Microsoft provides something else that is not related to IP addresses, parsename but it will parse the any string into 4 sections. Each section is separated by a period.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Octet

  • An octet is 8 bits. It is equivalent to a byte in an 8 bit world.
  • IP addresses are made of 4 octets and looks like: [ octet ] . [ octet ] . [ octet ] . [ octet ]
  • IP addresses range from 0.0.0.0 to 255.255.255.255

Data used

  1. USE sql911;
  2. go
  3. SELECT CAST(RemoteLogName AS VARCHAR(16)) AS 'From',
  4.        CAST(Request AS VARCHAR(64)) AS 'Request',
  5.        StatusCode,BytesSent,
  6.        CAST(Referer AS VARCHAR(96)) AS 'Referer'
  7. FROM logs
  8. WHERE Cookie IS not null and remotehostname = 'sqlhacks.com'
  9. ORDER BY remotelogname;
  10. go
[Get Code]
From             Request                                                          StatusCode  BytesSent   Referer
---------------- ---------------------------------------------------------------- ----------- ----------- ------------------------------------------------------------------------------------------------
12.151.72.1      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
12.162.212.24    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
139.131.241.10   GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       "http://www.google.com/search?q=%22Msg+208
142.166.3.123    GET /index.php/Optimize/Truncate-Delete HTTP/1.1                 200         10248       NULL
142.166.3.123    GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4018        NULL
205.200.74.135   GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD
205.200.74.135   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4050        NULL
206.188.0.10     GET / HTTP/1.1                                                   200         19996       NULL
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4144        NULL
206.188.0.12     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         51274       NULL
206.188.0.16     GET /index.php/Optimize/Index-Rebuild-Reorganize HTTP/1.1        200         10516       NULL
206.188.0.16     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         52538       NULL
206.188.0.16     GET /index.php/Administration/Lightweight-diagnostics HTTP/1.1   200         15264       NULL
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         41288       NULL
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         23449       NULL
206.188.0.47     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4115        NULL
206.211.127.2    GET /index.php/Summary/Multiple_Group_By HTTP/1.1                200         36068       "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe
206.211.127.2    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe
212.242.32.164   GET /index.php/Retrieve/Changed-Row HTTP/1.1                     200         13474       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA
212.242.32.164   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA
24.192.144.237   GET /index.php/Retrieve/Format_Numbers HTTP/1.1                  200         14915       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
59.93.7.244      GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By
59.93.7.244      GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By
64.28.26.178     GET /pub/skins/fixflow/css/pm-core.css HTTP/1.1                  200         2112        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       http://www.google.com/search?hl=en&client=safari&rls=en&q=Msg+50000&btnG=Search
64.28.26.178     GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/fontsizer.js HTTP/1.1                     200         4317        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/favicon.ico HTTP/1.1                              200         633         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-main.css HTTP/1.1              200         6192        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-fixflow.css HTTP/1.1           200         2001        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-print.css HTTP/1.1             200         981         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/font-verdana.css HTTP/1.1             200         2752        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /uploads/PmWiki/button-rss.png HTTP/1.1                      200         280         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/sqlhack-04.gif HTTP/1.1                           200         618         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/c-silver.css HTTP/1.1                 200         3771        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/images/silver100-top.jpg HTTP/1.1         200         376         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/images/bullet7.gif HTTP/1.1               200         845         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/wsplus/important.gif HTTP/1.1                           200         1258        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/wsplus/tip.gif HTTP/1.1                                 200         1276        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
71.6.232.27      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
72.34.230.108    GET / HTTP/1.0                                                   200         19843       NULL
72.34.230.108    GET / HTTP/1.0                                                   200         19673       NULL

(52 row(s) affected)

Pay attention to the sorting order of the IP addresses. First we have 12.151.72.1 then we get 206.188.0.16 in the middle and we get 72.34.230.108 at the end.

Sorting IP addresses

  1. SELECT CAST(RemoteLogName AS VARCHAR(16)) AS 'From',
  2.        CAST(Request AS VARCHAR(64)) AS 'Request',
  3.        StatusCode,BytesSent,
  4.        CAST(Referer AS VARCHAR(96)) AS 'Referer'
  5. FROM logs
  6. WHERE Cookie IS not null and remotehostname = 'sqlhacks.com'
  7. ORDER BY CAST(PARSENAME(remotelogname,4) AS INT),
  8.          CAST(PARSENAME(remotelogname,3) AS INT),
  9.          CAST(PARSENAME(remotelogname,2) AS INT),
  10.          CAST(PARSENAME(remotelogname,1) AS INT);
  11. go
[Get Code]
From             Request                                                          StatusCode  BytesSent   Referer
---------------- ---------------------------------------------------------------- ----------- ----------- ------------------------------------------------------------------------------------------------
12.151.72.1      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.151.72.1      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLG
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
12.162.212.24    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
12.162.212.24    GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp_check_constbytable_rowset&hl=en&rls=GGLG
24.192.144.237   GET /index.php/Retrieve/Format_Numbers HTTP/1.1                  200         14915       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
24.192.144.237   GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ
59.93.7.244      GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By
59.93.7.244      GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/Summary/Multiple_Group_By
64.28.26.178     GET /pub/skins/fixflow/css/pm-core.css HTTP/1.1                  200         2112        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       http://www.google.com/search?hl=en&client=safari&rls=en&q=Msg+50000&btnG=Search
64.28.26.178     GET /pub/wsplus/wsplus.css HTTP/1.1                              200         3393        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/fontsizer.js HTTP/1.1                     200         4317        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/favicon.ico HTTP/1.1                              200         633         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-main.css HTTP/1.1              200         6192        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-fixflow.css HTTP/1.1           200         2001        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/layout-print.css HTTP/1.1             200         981         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/rightbar.css HTTP/1.1                 200         787         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/font-verdana.css HTTP/1.1             200         2752        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /uploads/PmWiki/button-rss.png HTTP/1.1                      200         280         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/sqlhack-04.gif HTTP/1.1                           200         618         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/css/c-silver.css HTTP/1.1                 200         3771        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/images/silver100-top.jpg HTTP/1.1         200         376         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/skins/fixflow/images/bullet7.gif HTTP/1.1               200         845         http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/wsplus/important.gif HTTP/1.1                           200         1258        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
64.28.26.178     GET /pub/wsplus/tip.gif HTTP/1.1                                 200         1276        http://www.sqlhacks.com/index.php/FAQs/Error_Messages
71.6.232.27      GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
71.6.232.27      GET /pub/wsplus/csshover.htc HTTP/1.1                            304         NULL        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD
72.34.230.108    GET / HTTP/1.0                                                   200         19843       NULL
72.34.230.108    GET / HTTP/1.0                                                   200         19673       NULL
139.131.241.10   GET /index.php/FAQs/Error_Messages HTTP/1.1                      200         18594       "http://www.google.com/search?q=%22Msg+208
142.166.3.123    GET /index.php/Optimize/Truncate-Delete HTTP/1.1                 200         10248       NULL
142.166.3.123    GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4018        NULL
205.200.74.135   GET /index.php/Administration/Stored-Procedures HTTP/1.1         200         240557      "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD
205.200.74.135   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=sp%5Fsubscriptionsummary&hl=en&sourceid=gd&rls=GGLD
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4050        NULL
206.188.0.10     GET / HTTP/1.1                                                   200         19996       NULL
206.188.0.10     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4144        NULL
206.188.0.12     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         51274       NULL
206.188.0.16     GET /index.php/Optimize/Index-Rebuild-Reorganize HTTP/1.1        200         10516       NULL
206.188.0.16     GET /index.php/Site/AllRecentChanges?action=atom HTTP/1.1        200         52538       NULL
206.188.0.16     GET /index.php/Administration/Lightweight-diagnostics HTTP/1.1   200         15264       NULL
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         41288       NULL
206.188.0.23     GET /index.php/Main/HomePage HTTP/1.1                            200         23449       NULL
206.188.0.47     GET /index.php/Site/AllRecentChanges?action=rss HTTP/1.1         200         4115        NULL
206.211.127.2    GET /index.php/Summary/Multiple_Group_By HTTP/1.1                200         36068       "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe
206.211.127.2    GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?q=Column++is+invalid+in+the+select+list+because+it+is+not+containe
212.242.32.164   GET /index.php/Retrieve/Changed-Row HTTP/1.1                     200         13474       "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA
212.242.32.164   GET /pub/wsplus/csshover.htc HTTP/1.1                            200         3071        "http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=SUNA

(52 row(s) affected)
  1. The IP addresses are properly sorted in an ascending order.
  2. Line 7: PARSENAME(remotelogname,4) will split the string in up to 4 segments starting from the right.
  3. Line 7: CAST ... AS INT converts the first octet from a string to a number, then it will sort in numeric ascending order.
  4. If parsename finds an empty string, parsename returns a NULL, not empty space.
  5. parsename was designed to get the object name, owner name, database name, and server name of a fully qualified name.
%div >important apply=div%
  • Even if you have an index on the IP address, SQL Server will do a table scan because of the order clause with the parsename to split the IP address in the 4 octets.