topic Re: Parsing or Processing Application logs in QlikView

Parsing or Processing Application logs

shane_spencer — Mon, 20 Aug 2012 14:50:02 GMT

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.

Re: Parsing or Processing Application logs

rwunderlich — Tue, 21 Aug 2012 06:22:57 GMT

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

-Rob

Re: Parsing or Processing Application logs

shane_spencer — Wed, 22 Aug 2012 07:39:26 GMT

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

Re: Parsing or Processing Application logs

shane_spencer — Thu, 23 Aug 2012 14:28:03 GMT

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!

Re: Parsing or Processing Application logs

marc8891 — Mon, 28 Oct 2019 20:18:37 GMT

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
;