PArse syslog message into data fields

[pconrad]

I've got a syslog server posting to MS SQL. It works great as far as posting all the data. However, it puts all the useful information into one big text field called message.
How can I parse that data & place into separate data fields?

Sample text:
3625: Jun 9 13:35:54.392: %CRYPTO-MESSAGE_UP: (Server) Mode=CLIENTMODE Client_type=UNK NOWN User=user Group=TGROUP2 Client_public_a ddr=165.217.90. 165 Server_public_a ddr=57.13.107.1 8 Assigned_client _addr=192.168.0 .3

[ChrisGNZ]

Hi there

We've just constructed a Syslog service that records into SQL as well (code available from here )

The problem is that there is no standard for the format of a syslog message itself - it is basically freetext.

Having said that, if you know the format that common routers or firewalls use, then you can write your own format filters.

For example, I have a Linux based firewall sending syslog messages from the IPTables packet filter. These are in a fairly standard format from the PF klogd, so I wrote the following SQL view :

create view PacketFilter
as
select substring(message, 8, patindex('%:%', substring(message,8,16))-1) as 'LogName',
    substring(message, patindex('% SRC=%', message)+5, patindex('% %', substring(message, patindex('% SRC=%', message)+5,16))-1 ) as 'Src',
    substring(message, patindex('% DST=%', message)+5, patindex('% %', substring(message, patindex('% DST=%', message)+5,16))-1 ) as 'Dst',
    substring(message, patindex('% DPT=%', message)+5, patindex('% %', substring(message, patindex('% DPT=%', message)+5,16))-1 ) as 'DPort',
    substring(message, patindex('% SPT=%', message)+5, patindex('% %', substring(message, patindex('% SPT=%', message)+5,16))-1 ) as 'SPort',
    substring(message, patindex('% PROTO=%', message)+7, patindex('% %', substring(message, patindex('% PROTO=%', message)+7,16))-1 ) as 'Prtcl'
from syslog where patindex('klogd: PF%',message) > 0
go

I hope this helps!

Regards
Chris