[Snort-users] How do I convert a snort source IP Number to IP address in Microsoft SQL Server

Joe Stocker jstocker101 at ...125...
Mon May 10 07:23:05 EDT 2004


Hello,
I am unable to convert the new snort database log format of an IP number 
from 2130706433 back to 127.0.0.1 using Microsoft SQL Server.
I have been unable to find any examples except for MySQL and PostgreSQL.
In MySQL you can use the inet_ntoa function: SELECT ip_src, 
inet_ntoa(ip_src) FROM iphdr;
In PostgreSQL you can write your own function.
But how would you write that function in Microsoft SQL?
The ACID web page has a FAQ which describes how this should theoretically 
work:
http://acidlab.sourceforge.net/acid_faq.html#faq_e1
Let IP  = the 32-bit unsigned integer representation of the IP address
    ip1 = octet 1 of 4 (high-order)
    ip2 = octet 2 of 4
    ip3 = octet 3 of 4
    ip4 = octet 4 of 4 (low-order)

    >>  = bitwise shift right operator; takes an operand of the number bits 
to shift
    AND = bitwise AND operator

Then,
   ip1 = IP >> 24
   ip2 = (IP AND 00000000 11111111 00000000 00000000) >> 16
   ip3 = (IP AND 00000000 00000000 11111111 00000000) >> 8
   ip4 = (IP AND 00000000 00000000 00000000 11111111)

   IP = ip1 . ip2 . ip3 . ip4
***problem*** There is no >> operator in Microsoft SQL.
The choices are ^ (Bitwise Exlusive OR), & (Bitwise AND), | (Bitwise OR)

My attempt thus far in Microsoft SQL was:
select (convert(bigint,2130706433) ^ 24 -- Returns 02130706457 (BAD!) -- 
Should be 127
select (convert(bigint,2130706433) ^ 16) & 255  -- Returns 17 (BAD!) -- 
Should be 0
select (convert(bigint,2130706433) ^ 8) & 255  -- Returns 9 (BAD!) -- Should 
be 0
select convert(bigint,2130706433) & 255    -- Returns 1 for the last octet 
(GOOD!)


What am I doing wrong?
Thanks!

_________________________________________________________________
Stop worrying about overloading your inbox - get MSN Hotmail Extra Storage! 
http://join.msn.com/?pgmarket=en-us&page=hotmail/es2&ST=1/go/onm00200362ave/direct/01/





More information about the Snort-users mailing list