Converting IP addresses to bigints in T-SQL

Because I really want to keep up the rhythm of posting at least one code slice a week, here’s a snippet I wrote a long time ago. In our business, we often want to compare ranges of IP addresses, and so we need to convert them from an quad-based notation (x.x.x.x) to an integer basis. This particular method uses the system function in SQL Server called PARSENAME(), which simply splits a period-delimited varchar into it’s seperate components.

Below is how you flip from quad to integer and back; enjoy!


DECLARE @SrcIP varchar(15),
@SrcIPNbr bigint

SET @SrcIP = ‘190.10.10.1’

SET @SrcIPNbr = (CONVERT(bigint, PARSENAME(@SrcIP,4))*POWER(256,3))
+ (CONVERT(bigint, PARSENAME(@SrcIP,3))*POWER(256,2))
+ (CONVERT(bigint, PARSENAME(@SrcIP,2)*POWER(256,1)))
+ CONVERT(bigint, PARSENAME(@SrcIP,1))

SELECT @SrcIPNbr

SELECT CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,3))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,2))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,1))%256) + ‘.’ +
CONVERT(varchar(3),FLOOR(@SrcIPNbr/POWER(256,0))%256)

2008-12-10 by SQL 4

We’re committed now!


Tonight, during the AtlantaMDF planning session, I finally took the bait and committed to planning a SQL Saturday. I’ve been thinking about it for a while, but was delaying until I got more information about the other user group experience in the region (Code Camp). I finally got word that they’ve picked a date (mid March), so we can begin moving forward with our plans.

I’m a big proponent of the code camp model, so I’m looking forward to helping set the wheels in motion; however, I’m a little worried that I’m in over my head. I’m probably going to be blogging about this a lot over the next few weeks (months), so be prepared. Here’s my short list of stuff to do:

  • Contact Andy Warren to officially say we’re doing this.
  • Report back with some tentative dates to the rest of the AtlantaMDF leadership team.
  • Start identifying vendors, volunteers, and speakers.

Our local Microsoft rep has suggested that we could use the Microsoft office if we limited the number of attendees, which is reasonable. That’s a big chunk of time time that we can save. Looks like this is really happening; exciting times ahead.

2008-12-09 by Conferences 0

Testing a blogging client

So, this is a simple post (which most of you wil probably ignore), but I wanted to try out w.bloggar. Part of the reason is to take advantage of some of the client side tools, but probably the biggest reason is to add a little footnote to each post letting you know what music I’m listening to. Figured it would give you a little insight into my own warped brain 🙂

[Listening to: Long Black Veil – The Proclaimers – Finest (03:03)]
2008-12-08 by Blogging is FUN! 0

Weekly Link RoundUp 2008-12-05

Not a lot of time spent reading this week; it’s Nutcracker week (my daughter dances in the ballet), and my schedule has been nuts.  On top of that, my wife’s birthday was this week, I blew up my site, and my parents are in town.  However, what I have read is worth sharing:

Brent Ozar has been writing some great articles about Technical Blogging.  I linked to part 3, but there are 5 in the series.  Get them while they’re fresh.

Speaking of Brent, I’ve been browsing the SQLServerPedia project.  At some point, I plan to actually start contributing, but like I said, busy week this week.

TJay Belt has been thinking some more about the PASS high (thanks to the SQLBatman). 

Catching up on Andy Warren’s blog; I haven’t read it since PASS, so it was nice to catch up on it  (I even got a  name drop).  He has a rather important post titled Why I Want To Be On The PASS Board; now that’s he’s elected, let’s see what he can do about it.  I’m glad he’s on the board, but I think that just like regular politics, we need to remind our representatives of the promises they make from time to time.  

That’s it for this week; keep reading, and keep coding.

2008-12-05 by Weekly Link Roundup 0

it’s all in how you look at it…

Not sure how to classify this bit of useless knowledge, but I’m putting it out there in case it sparks some-one’s creative interest.  Most of my day job entails parsing firewall syslog data; syslog may be a standard method of streaming data from a firewall, but there’s really very little standard about what is contained inside the syslog stream.  Each vendor has their own method of recording what events occurred on the firewall; if you’re trying to compare data between firewall vendors, it gets complicated.

Here’s a stream from a Watchguard (version 8.x) firewall:

firewalld[126]: deny out eth1 293 udp 20 127 192.168.10.211 192.9.202.3 137 137 (default)

Here’s one from a Cisco Pix:

%PIX-6-710005: UDP request discarded from 10.1.1.26/137 to inside:10.1.255.255/netbios-ns

Now, there are various methods of parsing the data; if you’ve ever used SQL Server to parse a message string, you know that it can get quite ugly.  Especially if the data is irregular.  Speaking of regularity, you can use regular expressions if you’re parsing the data with a client (such as VBScript or .NET), but it’s not always the easiest thing to do.  However, some of the firewall vendors (Watchguard,version 9) have recently become my friends (as in I don’t really know them, but I like what they’re doing), and adopted a quote-delimited standard for syslog traffic, like so:

disp="Deny"   pri="1" policy="Unhandled Internal Packet-00" src_ip="192.168.16.141" dst_ip="192.168.16.255" pr="netbios-dgm/udp" src_port="138" dst_port="138" src_intf="1-Trusted" dst_intf="Firebox"      rc="101" msg="denied" pckt_len="229" ttl="128"

Why do I like this so much?  Parsing in SQL is still a bear, and the streams may still be irregular (different events may have different attributes in different orders), so using VBScript and regular expressions haven’t really improved.  Look at the sample again; what happens if we slap a little extra information to the beginning and end?

<Msg disp="Deny"   pri="1" policy="Unhandled Internal Packet-00" src_ip="192.168.16.141" dst_ip="192.168.16.255" pr="netbios-dgm/udp" src_port="138" dst_port="138" src_intf="1-Trusted" dst_intf="Firebox"      rc="101" msg="denied" pckt_len="229" ttl="128" />

Suddenly, we have an XML fragment, which is MUCH easier to parse and manipulate, even in T-SQL (2005):

DECLARE @Msg XML
SET @Msg =‘<Msg disp="Deny" pri="1" policy="Unhandled Internal Packet-00" src_ip="192.168.16.141" dst_ip="192.168.16.255" pr="netbios-dgm/udp" src_port="138" dst_port="138" src_intf="1-Trusted" dst_intf="Firebox" rc="101" msg="denied" pckt_len="229" ttl="128" />‘


SELECT @Msg.value (‘(Msg/@disp)[1]‘,‘VARCHAR(50)‘)


I realize this is a very specific tip for a very select group of people, but I thought the exercise was worth pointing out; raw data may not always be in the format we want, but if it is standard and predictable, there may be simple methods of manipulating it.  XML is where you find it.

2008-12-04 by XML 0

a few more tweaks….

So, I spent part of the day reading Brent’s great series on starting a technical blog, and paid a lot of attention to his most recent post How to Start a Technical Blog, Part 3: WordPress Plugins.  I’ve got to hand it to the WordPress developers; there’s a lot of cool stuff out there.  I added several of the plugins he mentioned; my favorite is MobilePress, which automatically makes my blog mobile friendly.  You can now get gumbo to go.

I’m also continuing to tweak the theme, and have added some items to the RSS feeds.  For those of you WordPress bloggers, a question: at what point do you consider a theme to be no longer the original?  I like this theme, but I’m working very hard to make it mine.

More to come.

2008-12-04 by Blogging is FUN! 0

Writing Dynamic SQL

I’m currently working on presentation for Dynamic SQL, and I thought I’d share one of my favorite tricks.  I had actually posted it to USENET earlier, so you can read the whole thread here, complete with some very useful suggestions, but here’s a synopsis:

As you’re probably aware, writing dynamic SQL statements can be a bear, and often involve breaking in and out of a concatenated string like so:

DECLARE @sql nvarchar(MAX)
SELECT @sql = N'SELECT * FROM '+ @DatabaseName
                + '.'+ @SchemaName
                + '.'+ @TableName
                + ' WHERE '+ @ColumnName + ' = ' + @Value
EXEC sp_executesql @sql

(Note that I am NOT endorsing the use of this example, or the cost or benefit of dynamic SQL as a whole; I’m simply showing a method of writing dynamic SQL when needed).

I use a REPLACE statement to avoid dinking around with the extra quotation marks, and thanks to suggestions by Erland and others (in the above thread), I can also mitigate some of the risk of SQL injection, like so:

SET @SQL = N'SELECT *
             FROM |DatabaseName|.|SchemaName|.|TableName|
             WHERE |ColumnName| = @param_value'

SET @SQL = REPLACE(@SQL,'|DatabaseName|',QUOTENAME(@DatabaseName))
SET @SQL = REPLACE(@SQL,'|SchemaName|',QUOTENAME(@SchemaName))
SET @SQL = REPLACE(@SQL,'|TableName|',QUOTENAME(@TableName))
SET @SQL = REPLACE(@SQL,'|ColumnName|',QUOTENAME(@ColumnName))

Happy coding!

2008-12-03 by SQL 1

Playing with themes

One of the benefits of starting over is that I now have access to the latest themes; I like this one, it’s simple, and I’ll be able to tweak it to fit my needs pretty easily.  More to come, but I think I’m done for the evening.

2008-12-03 by Blogging is FUN! 0

starting over (expletives deleted)

So…. my support provider upgraded their service this month, and I thought to myself: “Self, since you’re in an upgrading mood, AND you’ve decided to take this blogging thing for real, why not upgrade your site to the newest WordPress”.

I should learn to not listen to myself, or least when I listen, approach any ideas that that guy has with extreme caution.  Since my site provider now has a handy-dandy installation option for WordPress, I figured it would be easy to just click the button and upgrade.  Unfortunately, I didn’t read the bit about clicking the button would OVERWRITE ANY DATABASE YOU HAVE.  Guess what?  Everything’s gone.

You would also think that being the database professional I am, I would believe in the almighty backup.  Well, I do believe… I just don’t practice that belief.  No backup.  I guess I’ve been working as a developer too long, trusting in my DBA’s to provide me with a backup when I need it.  On top of that, I’m stupid. 

I thought to myself: “Self, since you’re paying for support, see if they have a backup procedure”.  I thought, “Good idea, self!” (obviously, I didn’t pay attention to the earlier advice about not listening to that guy).  I contacted support, and while they DO have a backup process, I had wiped my tables in between the time that they had moved them to the new server and started backing them up.  Furthermore, the old server is now gone (apparantly taking the backup with it).

So, I’m starting over.  At least this way, I get a clean installation, and I get rid of the funky frames I had on the old site.  The bad news is that I’ve lost the one or two decent posts I’ve posted over the years.  At least I can repost them, and you, dear readers, will think they are fresh.

2008-12-02 by Blogging is FUN! 2