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.