9 Replies Latest reply: Feb 23, 2018 7:44 PM by Jean-Baptiste DUQUESNE RSS

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

    Jean-Baptiste DUQUESNE

      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

        • Re: Issue in Load Script to parse and stock Date in a Log File
          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;

            • Re: Issue in Load Script to parse and stock Date in a Log File
              Jean-Baptiste DUQUESNE

              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);

                • Re: Issue in Load Script to parse and stock Date in a Log File
                  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);

                    • Re: Issue in Load Script to parse and stock Date in a Log File
                      Jean-Baptiste DUQUESNE

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

                       

                      Capture d’écran 2018-02-22 à 20.08.23.png

                        • Re: Issue in Load Script to parse and stock Date in a Log File
                          Sunny Talwar

                          Did you look at this guy?

                           

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

                            • Re: Issue in Load Script to parse and stock Date in a Log File
                              Jean-Baptiste DUQUESNE

                              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

                                • Re: Issue in Load Script to parse and stock Date in a Log File
                                  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

                                    • Re: Issue in Load Script to parse and stock Date in a Log File
                                      Jean-Baptiste DUQUESNE

                                      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 ? 

                                      Capture d’écran 2018-02-23 à 21.50.01.png

                                       

                                      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

                                        • Re: Issue in Load Script to parse and stock Date in a Log File
                                          Jean-Baptiste DUQUESNE

                                          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