Issue in Load Script to parse and stock Date in a Log File

[Anonymous]

Hello,

My Log file has the following syntax :

9:22:26 (ibmratl) TIMESTAMP 2/9/2018

9:29:08 (ibmratl) IN: "DOORS"

9:31:29 (ibmratl) OUT: "DOORS"

9:32:08 (ibmratl) OUT: "DOORS"

9:36:59 (ibmratl) OUT: "DOORS"

10:58:25 (ibmratl) OUT: "DOORS"

13:44:10 (ibmratl) OUT: "DOORS" 

9:22:26 (ibmratl) TIMESTAMP 2/10/2018

9:29:08 (ibmratl) IN: "DOORS"

9:31:29 (ibmratl) OUT: "DOORS"

9:32:08 (ibmratl) OUT: "DOORS"

9:36:59 (ibmratl) OUT: "DOORS"

10:58:25 (ibmratl) OUT: "DOORS"

13:44:10 (ibmratl) OUT: "DOORS"

While the date is not changed, I'd Like to stock dynamically the last Date value in a variable or something like that to create the date filter

I Have coded in LOAD script like that :

LOAD

rowno()                                                                              as [LineNumber],
if(index(@1, ':') > 0, time(round(@1 ,1/24), 'hh:mm'))      as [Time],
replace(replace(@2, '(' , ''), ')', '')                                       as [Vendor],
if(right(@3, 1) = ':', purgechar(@3, ':'))                              as [Action],
if(right(@3, 1) = ':', @4)                                                     as [Feature],

FROM

[./lmgrd.log]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

Could you help to add the corresponding date please ?

I'd like to have an output table like that :

2/9/2018 9:29:08 (ibmratl) IN: "DOORS"

2/9/2018 9:31:29 (ibmratl) OUT: "DOORS"

2/9/2018 9:32:08 (ibmratl) OUT: "DOORS"

2/9/2018 9:36:59 (ibmratl) OUT: "DOORS"

2/9/2018 10:58:25 (ibmratl) OUT: "DOORS"

2/9/2018 13:44:10 (ibmratl) OUT: "DOORS" 

2/10/2018 9:29:08 (ibmratl) IN: "DOORS"

2/10/2018 9:31:29 (ibmratl) OUT: "DOORS"

2/10/2018 9:32:08 (ibmratl) OUT: "DOORS"

2/10/2018 9:36:59 (ibmratl) OUT: "DOORS"

2/10/2018 10:58:25 (ibmratl) OUT: "DOORS"

2/10/2018 13:44:10 (ibmratl) OUT: "DOORS"

Thank in advance.

JB

[sunny_talwar]

Something along these lines

Table:

LOAD *,

RowNo() as [LineNumber],

If(Len(Trim(TempDate)) = 0, Peek('Date'), TempDate) as Date;

LOAD If(Index(@1, ':') > 0, Time(Round(@1 ,1/24), 'hh:mm')) as [Time],

Replace(Replace(@2, '(' , ''), ')', '') as [Vendor],

If(Right(@3, 1) = ':', PurgeChar(@3, ':')) as [Action],

If(Right(@3, 1) = ':', @4) as [Feature],

If(Right(@3, 1) <> ':', @4) as [TempDate];

LOAD * INLINE [

    @1, @2, @3, @4

    9:22:26, (ibmratl), TIMESTAMP, 2/9/2018

    9:29:08, (ibmratl), IN:, DOORS

    9:31:29, (ibmratl), OUT:, DOORS

    9:32:08, (ibmratl), OUT:, DOORS

    9:36:59, (ibmratl), OUT:, DOORS

    10:58:25, (ibmratl), OUT:, DOORS

    13:44:10, (ibmratl), OUT:, DOORS

    9:22:26, (ibmratl), TIMESTAMP, 2/10/2018

    9:29:08, (ibmratl), IN:, DOORS

    9:31:29, (ibmratl), OUT:, DOORS

    9:32:08, (ibmratl), OUT:, DOORS

    9:36:59, (ibmratl), OUT:, DOORS

    10:58:25, (ibmratl), OUT:, DOORS

    13:44:10, (ibmratl), OUT:, DOORS

];

DROP Field TempDate;

[Anonymous]

Hello,

Thank you for your help.

But how to do that from the log file directly ?

FROM

[./lmgrd.log]

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

[sunny_talwar]

Should be something like this

LOAD *,

    RowNo() as [LineNumber],

    If(Len(Trim(TempDate)) = 0, Peek('Date'), TempDate) as Date;

LOAD If(Index(@1, ':') > 0, Time(Round(@1 ,1/24), 'hh:mm')) as [Time],

    Replace(Replace(@2, '(' , ''), ')', '') as [Vendor],

    If(Right(@3, 1) = ':', PurgeChar(@3, ':')) as [Action],

    If(Right(@3, 1) = ':', @4) as [Feature],

    If(Right(@3, 1) <> ':', @4) as [TempDate]

FROM [./lmgrd.log]

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

[Anonymous]

I Think there is any problem with tempDate and Date field.

[sunny_talwar]

Did you look at this guy?

If(Right(@3, 1) <> ':', @4) as [TempDate]

[Anonymous]

Yes I looked this instructions. And I changed it by the following line :

If(Index(@3, 'TIMESTAMP') > 0, @4) as [TempDate]

And now it's working good !

Thank you for your help.

I didn't know that you can use a first LOAD to run some action as you can see in scripting (Perl, Python ...)

Well, it stay another one problem.

In log file, you can see the time restart at 0:00.

But if no TIMESTAMP instructions at 0:00, it takes the last value, the "yesterday date value".

21:50:11 (ibmratl) IN: "DOORS"

22:01:01 (ibmratl) IN: "DOORS"

22:09:01 (ibmratl) OUT: "DOORS"

22:14:12 (ibmratl) OUT: "DOORS"

//new day

0:03:01 (ibmratl) IN: "DOORS"

0:06:12 (ibmratl) IN: "DOORS"

0:11:01 (ibmratl) OUT: "DOORS"

0:32:12 (ibmratl) OUT: "DOORS"

2:07:02 (ibmratl) IN: "DOORS"

2:12:13 (ibmratl) IN: "DOORS"

2:15:01 (ibmratl) OUT: "DOORS"

2:34:13 (ibmratl) OUT: "DOORS"

3:17:27 (lmgrd) TIMESTAMP 2/10/2018

3:37:31 (ibmratl) TIMESTAMP 2/10/2018

4:09:02 (ibmratl) IN: "DOORS"

4:17:02 (ibmratl) OUT: "DOORS"

So, I think to concatenate date and time to convert it to timestamp.

And I'll raise comparaison between the last timestamp value and the new one.

if newtimestamp < last value = new value

if newtimestamp > last value = keep current value

What do you think about it ?

Have you an idea how to that in the code ?

Thank you

JB

[sunny_talwar]

To create a timestamp, all you need to do is Date + Time....

So, in the context of your app, you can do this

TimeStamp(Date + Time) as TimeStamp

[Anonymous]

Yes, it works. Thank you !

I Have a last problem.

Between The line 140 and ligne 147, I have an issue.

If you compare the "current timestamp" with the "previous timestamp", it will done for the line 140 but not after.

How to stock a Max Timestamp value and compare with the field value ?

And if the field value is < MaxTimestamp = Date + 1 , else no change ? 

Here my code. If you can see optimization, don't hesitate to modify.

LOAD *,

RowNo()  as [LineNumber],
     If(Len(Trim(TempDate)) = 0
     , Peek('Date')
     , TempDate
     ) as [Date],
    
      If(Len(Trim(TempDate)) = 0
     , Timestamp(Peek('Date') + Time)
     , Timestamp(TempDate + Time)
     )   as [Date/Time],  
    
 
      If(Len(Trim(TempDate)) = 0
     , Peek('Date') + Time
     , TempDate + Time
     )   as [Timestamp]

;

LOAD

If(Index(@1, ':') > 0, Time(Round(@1 ,1/24), 'hh:mm')) as [TimeHour],
If(Index(@1, ':') > 0, @1) as [Time],
     Replace(Replace(@2, '(' , ''), ')', '') as [Vendor],
     If(Right(@3, 1) = ':', PurgeChar(@3, ':')) as [Action],
if(right(@3, 1) = ':', @4) as [Feature],
if(right(@3, 1) = ':' AND (purgechar(@3, ':') = 'IN' OR purgechar(@3, ':') = 'OUT'), subfield(@5, '@', 1)) as [User],
if(right(@3, 1) = ':' AND (purgechar(@3, ':') = 'IN' OR purgechar(@3, ':') = 'OUT'), subfield(@5, '@', -1)) as [Client],
if(index(@3, 'OUT') > 0, '1', if(index(@3, 'OUT') = 0 AND index(@3, 'IN') = 0, '0', '-1')) as [Counter],
     If(Index(@3, 'TIMESTAMP') > 0, date(date#(@4, 'MM/DD/YYYY'), 'DD/MM/YYYY')) as [TempDate]

FROM [./lmgrd.log]

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

Thank in advance,

JB

[Anonymous · Accepted solution]

Hello,

It's good. I found solution.

Here is my Code :

Thank for your help !

Tell me if you think to add some optimization in the following code :

Log:

LOAD *,

RowNo() as [LineNumber],
Timestamp(Date + Time) as [Date/Time]
;

LOAD *,
If([Old_Timestamp] < Peek([Old_Timestamp])
, date(date#([Old_Date], 'DD/MM/YYYY') + 1)
, If(Len(Trim(Peek('Date'))) = 0
, [Old_Date]
, Peek('Date'))) as [Date]
;

LOAD *,

     If(Len(Trim(TempDate)) = 0
     , Peek('Date')
     , TempDate
     ) as [Old_Date],
    
      If(Len(Trim(TempDate)) = 0
     , Timestamp(Peek('Date') + Time)
     , Timestamp(TempDate + Time)
     )   as [Old_Date/Time],  
    
      If(Len(Trim(TempDate)) = 0
     , Peek('Date') + Time
     , TempDate + Time
     )  as [Old_Timestamp]
;

LOAD

If(Index(@1, ':') > 0, Time(Round(@1 ,1/24), 'hh:mm')) as [TimeHour],
If(Index(@1, ':') > 0, @1) as [Time],
     PurgeChar(@2, '(,)') as [Vendor],
     If(right(@3, 1) = ':', PurgeChar(@3, ':')) as [Action],
If(right(@3, 1) = ':', @4) as [Feature],
If(right(@3, 1) = ':' AND (Purgechar(@3, ':') = 'IN' OR Purgechar(@3, ':') = 'OUT'), subfield(@5, '@', 1)) as [User],
If(right(@3, 1) = ':' AND (Purgechar(@3, ':') = 'IN' OR Purgechar(@3, ':') = 'OUT'), subfield(@5, '@', -1)) as [Client],
If(index(@3, 'OUT') > 0, '1', '0') as [Counter],
    
     If(Index(@3, 'TIMESTAMP') > 0
     , date(date#(@4, 'MM/DD/YYYY'), 'DD/MM/YYYY')
     , If(Index(@3, 'FLEXnet') > 0
         , date(date#(Purgechar(@14, '(,)'), 'MM/DD/YYYY'), 'DD/MM/YYYY')
     ) ) as [TempDate]

FROM [./lmgrd.log]

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

DROP FIELD [TempDate], [Old_Date], [Old_Date], [Old_Date/Time];
 

Cheers,

JB