Code

CSS, max-height

I wish I had something of substance to add today, but I’ve been overwhelmed by personal stuff.  My oldest daughter has been sick with strep for the last week, so I’ve been having to rearrange a lot of my schedule so I can hang out with her when needed.  Consequently, I’m just wiped in the evenings. 

Anyway, you may have noticed that I’ve been playing around with my website, especially with my new blogroll page.  I”ve been adding some blogs to it using RSS feeds, and one thing has become clear: different RSS feeds behave differently when asked to return an item.  For example, some blog postings auto truncate themselves after a few phrases; for example, Brent Ozar:

Brent Ozar

[feedsnap, 1]http://feeds.feedburner.com/BrentOzar-SqlServerDba[/feedsnap]

Contrast this with Andy Warren’s feed:

It Depends (Andy Warren)

[feedsnap, 1]http://feeds.feedburner.com/itdepends[/feedsnap]

Now, while I like reading Andy’s stuff, I don’t necessarily want a bunch of lengthy articles on my blogroll page; I tried to figure out a way to limit the amount of text returned, when I tripped across this nifty CSS property: max-height. From DevGuru: The max-height property is used to set the maximum height of an element. Other properties can be used to set the height, width, maximum width, minimum height and the minimum width. This property is not inherited. (note that Devguru also specifies that this property is not currently supported by any browser; it works in IE7. Go figure).

Anyway, I simply add the max-height to my div container wrapped around the feedsnap plugin, and suddenly the RSS feed truncates after a certain pixel height.

<div style=”overflow:hidden;max-height:100px”></div>

It Depends (Andy Warren)

[feedsnap, 1]http://feeds.feedburner.com/itdepends[/feedsnap]

 

Anyway, I hope this puts some ideas in your head.

2008-12-16 by CSS 3

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

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

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