help needed for parsing the error log file

Question

help needed for parsing the error log file

phani kumar gotety

Ten Centuries

Points: 1213
More actions
September 27, 2005 at 9:04 am

#111788

Hi,
I have the following vbscript to parse the SQL server error log file
-----------------------------------------------------------
Const ForReading = 1
Dim fso, tf
dim chk
dim s
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set tf = fso.CreateTextFile("c:\code\testfile.txt", True)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("\\path\Program Files\Microsoft SQL Server\MSSQL\LOG\errorlog", _
    ForReading)
Do While objTextFile.AtEndOfStream <> True
strLinetoParse = objTextFile.ReadLine
dtmEventDate = Mid(strLinetoParse, 1, 22) - to get the date part of event
strEventDescription = Mid(strLinetoParse, 23) - to get rest of the line
chk = mid(strLinetoParse, 30,9) - to get first five chars of the description
if chk = "start" then
tf.WriteLine VbCrLf
else
tf.WriteLine( dtmEventDate & "    " & strEventDescription & VbCrLf)
end if
Loop
------------------------------------------------------------------
I am just trying to eliminate those lines which says "startin database xyz" but my script is just dumping the whole error log into the new text file. I am not sure why my IF...ELSE condition is not working.
I am sorry if I look dumb here as I am a novice in scripting.
Any help would be greatly appriciated.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 1

sorry I am not a vb person, but do you know that you could do exec master..xp_readerrorlog to read your current log

you could even specify if you want to read any of your archive log by putting the archive number after the stored procedure name.

mom

phani kumar gotety Ten Centuries Points: 1213 More actions · Answer 2

Thanks for the reply MOM.

My team administers around 40 DB servers of which we check their error logs manually every day. I am trying to find a way to filter the current errorlog to get only the errors (if any). This I want to create a scheduled job so that I get all errors from error logs first thing in the morning which will reduce my burder checking every server's log manually for errors.

philcart SSC-Forever Points: 47794 More actions · Answer 3

You can execute xp_readerrorlog for each of the remote servers. If you can use linked servers then you can use OPENQUERY. EG:

SELECT Qry.* FROM 
OPENQUERY(<<LINKED SERVER>>, 'SET FMTONLY OFF EXEC master..sp_readerrorlog') AS Qry

The SET FMTONLY OFF is important because it bypasses the initial call for meta-data which fails when executing stored procedures.

From this query you can insert the results into a local table then sort and filter as necessary.

However, if you're stuck on using a VBScript, firstly download the Scripting help file from

http://www.microsoft.com/downloads/details.aspx?familyid=01592C48-207D-4BE1-8A76-1C4099D7BBB9&displaylang=en

This help file has all the information for JScript, VBScript, FileSystemObject, etc... and is a valuable resource.

Try this script.

Option Explicit

Const ForReading = 1
Const SearchText = "<< text to look for >>"

Dim fso, tf, objTextFile
Dim strLinetoParse, dtmEventDate, strEventDescription

Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("<< output file >>", True)
Set objTextFile = fso.OpenTextFile("<< error log path >>", ForReading)

Do While objTextFile.AtEndOfStream <> True
 strLinetoParse = objTextFile.ReadLine
 dtmEventDate = Mid(strLinetoParse, 1, 22) 
 strEventDescription = Mid(strLinetoParse, 23)

 if InStr(1, strLinetoParse, SearchText) > 0 then 
  tf.WriteLine( dtmEventDate & "    " & strEventDescription & VbCrLf) 
 end if

Loop

Set objTextFile = Nothing
Set tf = Nothing
Set fso = Nothing

--------------------
Colt 45 - the original point and click interface

Kelli Routsong Right there with Babe Points: 778 More actions · Answer 4

From your post:

chk = mid(strLinetoParse, 30,9) - to get first five chars of the description

Arguments for the mid function are (string, start, length). It looks to me like you are capturing NINE characters in the string instead of the five characters you are comparing to (if chk = "start" then).

Then again, I'm not much of a programmer myself.

Good luck!

phani kumar gotety Ten Centuries Points: 1213 More actions · Answer 5

Kelli, you are right. I was dumb in that line

Thanks Philcart... your suggestion really helped me now I am able to get what I wanted. phew... sort of releif

Thanks all

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

I manage 22+ servers ... I scan the errorlogs every 30 minutes for activity and email it to myself. I've found it useful to 'filter out' certain things like:

'%Database backed up:%'

'%Log backed up:%'

'%found 0 errors and repaired 0 errors.%'

There may be other strings but these exce[ptions work for me. If the servers are working well, then I get no email !

By the way I use a stored procedure that includes xp_readerrorlog and a cdosys smtp mail proc.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

philcart SSC-Forever Points: 47794 More actions · Answer 7

Rudy, so you actually run the procedure on the remote server itself?

I've been thinking about doing something similar and using a DTS package to amalgamate the data on a central monitoring server.

I've hacked together a ASP.Net based app that allows me to view stuff like job status and set maintenance parameters. Viewing the error log is the next cab off the rank.

--------------------
Colt 45 - the original point and click interface

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

Yes Phil, remote execution for quite a few things. I have a number of sp's that are installed on initial server build. error log scanning, transaction log truncation at a threshhold (master, msdb, tempdb), space gathering for user and system databases and for disk drives. All of these procedures are executed via an MSX server. My MSX server also servers as a central reporting repositiry for historical database space utilization as well. If you want to know more send me a private message.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."