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
- USE sql911;
- go
- SELECT CAST(RemoteLogName AS VARCHAR(16)) AS 'From',
- CAST(Request AS VARCHAR(64)) AS 'Request',
- StatusCode,BytesSent,
- CAST(Referer AS VARCHAR(96)) AS 'Referer'
- FROM logs
- WHERE Cookie IS not null and remotehostname = 'sqlhacks.com'
- ORDER BY remotelogname;
- go
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
- SELECT CAST(RemoteLogName AS VARCHAR(16)) AS 'From',
- CAST(Request AS VARCHAR(64)) AS 'Request',
- StatusCode,BytesSent,
- CAST(Referer AS VARCHAR(96)) AS 'Referer'
- FROM logs
- WHERE Cookie IS not null and remotehostname = 'sqlhacks.com'
- ORDER BY CAST(PARSENAME(remotelogname,4) AS INT),
- CAST(PARSENAME(remotelogname,3) AS INT),
- CAST(PARSENAME(remotelogname,2) AS INT),
- CAST(PARSENAME(remotelogname,1) AS INT);
- go
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)
- The IP addresses are properly sorted in an ascending order.
- Line 7: PARSENAME(remotelogname,4) will split the string in up to 4 segments starting from the right.
- Line 7: CAST ... AS INT converts the first octet from a string to a number, then it will sort in numeric ascending order.
- If parsename finds an empty string, parsename returns a NULL, not empty space.
- 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.