Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
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);
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);
I Think there is any problem with tempDate and Date field.
Did you look at this guy?
If(Right(@3, 1) <> ':', @4) as [TempDate]
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
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
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
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