topic Re: Loop through log files in QlikView

Loop through log files

rustyfishbones — Tue, 26 Sep 2017 15:30:13 GMT

Hi All,

I have a hundreds of log files and I want to loop through them and calculate the duration between the start of a transaction and the end

below is an excerpt from the file, 4 lines, the first line is the start and the last line is the end, my files contain thousands of these

"02/06/2017" "08:38:35.008" "RECV" "prTaskLUTPicked('06-02-17 08:38:32.000','6315320077','Alan','Group ID','GC.D.7','D4D10','2','1','1','1','','','')<CR><LF<LF>"

"02/06/2017" "08:38:36.781" "SEND" "0,<CR><LF><CR><LF>"

"02/06/2017" "08:38:36.808" "RECV" "prTaskLUTGetPicks('06-02-17 08:38:34.000','6315320077','Alan','Group ID','0','0','0')<CR><LF<LF>"

"02/06/2017" "08:38:37.293" "SEND" "N,0,1,D4D10,0,,D 1,,0 1 0 2 D,7,Singles,0029203,0,0,0,0,033,,,description,,,GC.D.7,013,,0,,,,0,,,0,0,0,0,0,<CR><LF><CR><LF>"

I would like to create a table that contains data from the first line and the last line, the first line will always contain prTaskLUTPicked and the last line will always start with "N,0,1

"02/06/2017" "08:38:35.008" "RECV" "02/06/2017" "08:38:37.293" "SEND" "N,0,1,D4D10,0,,D 1,,0 1 0 2 D,7,Singles,0029203,0,0,0,0,033,,,description,,,GC.D.7,013

What is the best way to create this, Use Previous function, or create a temp table

Re: Loop through log files

effinty2112 — Tue, 26 Sep 2017 16:11:00 GMT

Hi Alan,

I copied your data into a txt file (log.txt) and ran this script

Log:

Load

if(Status='Start', AutoNumber(@2),Peek(TransactionID)) as TransactionID,

LOAD @1,

@2,

if(Wildmatch(@4,'prTaskLUTPicked*'),'Start','End') as Status

FROM

log.txt

(txt, codepage is 1252, no labels, delimiter is ' ', msq)

Where Wildmatch(@4,'prTaskLUTPicked*','N,0,1*');

Durations:

Load

TransactionID,

Time(Min(@2)) as Start,

Time(Max(@2)) as End,

Interval(Max(@2)-Min(@2)) as Duration

Resident Log

Group by TransactionID;

Giving:

TransactionID	Start	End	Duration
1	08:38:35.008	08:38:37.293	00:00:02.285

Maybe you can load all your logfiles using a * wildcard, then if appropriate sort all the lines by the first time field then run something like the above script which takes out every line that is not a Start or End Time. Then it labels the rows with an autonumber to identify transactions then the Durations table is grouped on the TransactionID.

Good luck

andrew

Re: Loop through log files

rustyfishbones — Tue, 26 Sep 2017 19:26:21 GMT

Hi Andrew,

Thanks for that, I am using a for each loop, to loop through all log files in the directory and sub directories.

I got what I need working using previous, but it's not very efficient, I will try your suggestion and get back to you tomorrow

Sent from my iPhone

Re: Loop through log files

rustyfishbones — Wed, 27 Sep 2017 10:11:09 GMT

Thanks Andrew, it did not quite work the way I wanted, but it did give me some ideas, so thanks for your help.

Re: Loop through log files

marcus_sommer — Wed, 27 Sep 2017 11:15:20 GMT

Another approach could be to load at first the log-file completely and using then an outside peek() within a following load to access the first and the last record.

I could imagine that it might be slightly faster than the solution from Andrew because no where-clause is applied and no if-loop is needed to determine the first/last record. But I'm not sure that it - by hundreds of (assumingly rather small) files - made it bigger difference because the overhead for looping and accessing all the files is rather big compared to the amount of data.

- Marcus

Re: Loop through log files

rustyfishbones — Wed, 27 Sep 2017 17:47:06 GMT

Hi Marcus/Andrew,

As part of my project I need to show the log file within the Application, so I have concatenated each row from the file into a single string called logfile and it looks like the below image

What I want to do next is to color the lines if the start time "07:52:47.050" minus the End Time is greater than 1 second and if it is, color all the lines between red, otherwise leave them green

The file contains hundreds of records and I am currently displaying them in a Pivot Table and trying to color the text based on the times, can it be done, I want to do this as a color expression.

The Pivot Table contains 1 Dimension called LogFile and 1 expression but it's blank, I am hoping it could be done from the color expression. Is it possible

I hope you can help.

Regards

Alan

Re: Loop through log files

marcus_sommer — Wed, 27 Sep 2017 19:11:29 GMT

I think I would tend to create this information with a flag-field within the script - just 0 and 1 on the record-level and using this to color the rows.

- Marcus

Re: Loop through log files

qliksus — Fri, 29 Sep 2017 09:50:52 GMT

Assuming you have Startdate and Enddate as separate field along with your data field you can put the condition like below in the background Color of the expression tab

if( timestamp(enddate-Startdate) >1 and ( wildmatch(Data,'*taslklupicked*' ,'*n,0,1*' )=0 , red(),green())