Parsing or Processing Application logs

[shane_spencer]

I've got these error log files, which are pretty aweful imo. Each time an error occurs a new entry is written to the log with a data stamp

Exception Time is 08:13:27.4162777
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
<br><b>A connection-level error occurred while opening the connection.</b><br>Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

What I'm trying to do is to process these to perhaps get the time when a particular error occured i.e. Deadlock and count these occurances. Does anyone know if it would be possible to do this in QlikView?

I've been using 'Log Parser 2.2' to play around with this and I can search for a text string and get a count of how often in occurs but it's a bit limitted.

[rwunderlich · Accepted solution]

This kind of log processing is possible with QV and a lot of fun. Attached is an example to get you started.

-Rob

[shane_spencer]

Thanx Rob! I wouldn't have begun to know where to start. :]

[shane_spencer]

I've now got a working solution that I've automated to reload the current months data everynight. I've identified different errors and modified the "wildmatch" command accordingly, and also filtered out the extra log text that I don't want. Thought I'd upload the result in case it's of help to anyone else.

Once again thanx!

[marc8891]

For those of you that do not want to download Rob's solution, but just want to see the load script portion here is Rob Wunderlich's (rwunderlich) code:

Directory;
RawData:
LOAD
*,
Hour(ExceptionTime) as Hour // Create an Hour field
;
LOAD
*,
RecNo() as RecId, // Assign a sequence number for sorting
if(wildmatch(LogText,'Exception Time is*') // If an Exception message
,RecNo() // then use the Recno as a new Id
,peek('ExceptionId') // else use the id from the previous row
) as ExceptionId,
if(wildmatch(LogText,'Exception Time is*') // If an Exception message
,Time(Time#(subfield(LogText, ' ', 4), 'hh:mm:ss.ffffff')) // then parse the time
,peek('ExceptionTime') // otherwise use the time from the previous row
) as ExceptionTime,
if(wildmatch(LogText,'Transaction * was deadlocked *') // If a Deadlock message
,1 // then set the Deadlock flag to 1
,0 // else set the deadlock flag to 0
) as Deadlock
;
LOAD trim(@1:n) as LogText // Load the entire record as "LogText"
FROM
[Reconciliation Exceptions - 3 8.txt]
(fix, codepage is 1252)
WHERE trim(@1:n) <> '' // Exclude blank lines
;