Breaking up data within a field

[NateBOIT]

I am using a program to monitor event logs, which in turn saves the events to MySQL. However, the entire Windows Event log is placed into one field. I would like to somehow break that up to only display certain non-consecutive parts of the string. For example:

Logon Failure:
Reason:	Account logon time restriction violation
	User Name:	joebob
	Domain:		DOMAIN
	Logon Type:	10
	Logon Process:	User32  
	Authentication Package:	Negotiate
	Workstation Name:	JOEBOB_COMP
	Caller User Name:	JOEBOB_COMP
	Caller Domain:	DOMAIN
	Caller Logon ID:	(0x0,0x3E7)
	Caller Process ID:	5324
	Transited Services:	-
	Source Network Address:	192.168.1.14
	Source Port:	4639

All of that is placed into one field and formatted by carriage return line feeds. Is there any program out there that would break that up and only display specific sections? Such as only the reason, user name,and workstation? Also, not all events have the same "sections" such as User Name, Domain, etc.

And if there is not a program or way to do it with queries, then how would i go about coding something to break it into an array, or break each section into it's own seperate field by using the carriage returns? From there i want to generate reports that include only sections of the event log.

Thanks

 

[TonyGroves]

If what you want is to keep the entire string in the one field, but display selected parts of it, that can certainly be done with pure SQL, but it's not simple; it would be much easier to do it using program code.

Here is a sample of how to do it with SQL. Suppose you just want to get the user name:
[tt]
SELECT
TRIM(
SUBSTRING_INDEX(
SUBSTRING_INDEX(fldname,' User Name:',-1),
'\r',1
)
)
username
FROM tblname
[/tt]

If you're going to be doing queries like this on a regular basis, then you might want to consider if storing the whole string in one field is the best solution. What you could probably do is to have two tables; the first would contain a header record for each event, and the second table would contain a record for each attribute of each event. Achieving this would obviously require program code, but at least reading the data would be made much more simple and flexible.

 

[KarveR]

You could use perl to parse the event log for the data you want, and then load the selected parts to mysql, google activeperl (for windows) or perl is (normally) standard on any *nix.

With the expert help offered here in the perl forum, you could accomplish what you need.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[NateBOIT]

So you are saying parse the data before it ever goes into the table? Or take it from the table, parse it, then put it back into the table? I don't think i could do the first because of the program that i use to put the event logs into the database in the first place.

But i will look in to perl and see if that will meet my needs. Will other languages not do the same thing?

 

[TonyGroves]

There are several languages you could use. Perl is an excellent choice (ActivePerl, free from

http://www.activestate.com),

but some people recommend Python (also from activestate.com) for beginners. I believe Windows has native support for Basic, but it's a horrible language.

 

[feherke]

Hi

Depends on how you want to solve it. If the script has not to connect itself to the database server, then could be solved with [tt]awk[/tt] too. Is much simple then [tt]perl[/tt] but has a Windows port which does not need installation.

mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green] -s -e 'select [green][i]field[/i][/green] from [green][i]table[/i][/green]' | awk -f [green][i]script[/i][/green] | mysql -D [green][i]database[/i][/green] -u [green][i]user[/i][/green]

http://cm.bell-labs.com/cm/cs/who/bwk/awk95.exe

So in the script you split the bla-bla and compose an [tt]insert[/tt] command from each. I you choose this, further help in forum271.

Feherke.

http://rootshell.be/~feherke/