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=UNKNOWN User=user Group=TGROUP2 Client_public_addr=165.217.90.165 Server_public_addr=57.13.107.18 Assigned_client_addr=192.168.0.3
1 5955
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Erich Trowbridge |
last post by:
Has anybody seen this tool? It is awesome. check out
http://vw.vermeer.org/ . It's a php front end for large-scale syslog
deployments. It makes managing syslog in large networks a snap.
The idea...
|
by: Micha³ Wo¼niak |
last post by:
Hi again
The Question of Logs, part II. :)
What are the advantages/disadvantages of using syslog() to do the logging
rather than writing the logs with fopen(), fwrite() and fclose()? What...
|
by: nate |
last post by:
Hello,
Does anyone know where I can find an ASP server side script written in
JavaScript to parse text fields from a form method='POST' using
enctype='multipart/form-data'? I'd also like it to...
|
by: Gregory Starck |
last post by:
Hello,
I'm having what seems to be a deadlock in syslog with the code (wich
comes from a project I'm working on, but I've reduced the test case to
its minimum) I put at the end of this messsage....
|
by: hoopsho |
last post by:
Hi Everyone,
I am trying to write a program that does a few things very fast
and with efficient use of memory...
a) I need to parse a space-delimited file that is really large,
upwards fo a...
|
by: Guy Noir |
last post by:
Hello.
I am needing some advice. I need to build a syslog server that writes
to a sql backend.
I am concerned about the server not being able to handle multiple
requests. For example on an...
|
by: RMC |
last post by:
Hello,
I'm looking for a way to parse/format a memo field within a report.
The Access 2000 database (application) has an equipment table that
holds a memo field. Within the report, the memo...
|
by: gs |
last post by:
let say I have to deal with various date format and I am give format string
from one of the following
dd/mm/yyyy
mm/dd/yyyy
dd/mmm/yyyy
mmm/dd/yyyy
dd/mm/yy
mm/dd/yy
dd/mmm/yy
mmm/dd/yy
|
by: Lincoln Yeoh |
last post by:
Hi,
I've just started to learn python (I've been using perl for some years).
How do I redirect ALL stderr stuff to syslog, even stderr from
external programs that don't explicitly change their...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |