Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks Andrew, it did not quite work the way I wanted, but it did give me some ideas, so thanks for your help.
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
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
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
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())