Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have to analyze a log file of a portal and would like to use as a tool ClikView. The file has the extension. Log
The format of the log is to Apache and I should extract the information contained in it.
A sample of the file is as follows:
61432c789518088e2c708f122d260f9178b87059 - - [04/Mar/2014:06:25:04 +0100] "GET /page/Stig_Boqvist HTTP/1.1" 200 4561 "-" "Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)"
445fd14a49ff5469ed4ee51f5a2b6a7f5efea22b - - [04/Mar/2014:06:25:05 +0100] "GET /data/Waldkirchen.jrdf HTTP/1.1" 200 8612 "-" "Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)"
949d8c5d984bdbb6ca32364d80a4e2469e8a8e25 - - [04/Mar/2014:06:25:06 +0100] "GET /page/Homer_Dickenson HTTP/1.1" 200 4533 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
....
The information I want to know are:
- Content displayed more
- User agent used to make requests etc.
Basically I should be doing the business intelligence analysis on this log file. Can you help me or give me some suggestions?You can read the contents of the file and parse it directly? Taking for example the dashboard?
Thank you in advance for your reply
greetings
Carlo
Try this, adjusting to your needs
Thank you so much Clever, but I can not open your transformation because I have the free version of QlikView. Can you tell me briefly how you could do to divide the table Date??
Thank you and excuse the disorder
Carlo
I´ve used this script
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';
SET DayNames='seg;ter;qua;qui;sex;sáb;dom';
LOAD @1 as IP,
date#(lower(mid(@4,2,11)),'DD/MMM/YYYY') as Date,
mid(@4,14,11) as Hour,
@5,
@6 as Richiesta,
@7 as Risposta,
@8 as Payload,
@9,
@10 as UserAgent
FROM
[provalog.txt]
(txt, codepage is 1252, no labels, delimiter is spaces, msq);
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
LOAD @1 as IP,
Date#(Lower(Mid(@2,2,2)),'DD/MM/YYYY') as Giorno,
Date#(Lower(Mid(@2,5,3)),'DD/MM/YYYY') as Mese,
Date#(Lower(Mid(@2,9,4)),'DD/MM/YYYY') as Anno,
Mid(@2,14,2)as Ora,
Mid(@2,17,2)as Minuti,
Mid(@2,20,11)as Secondi,
@3 as Richiesta,
@4 as Risposta_SErver,
@5 as Payload,
@6 as User_Agent
FROM
(txt, codepage is 1252, no labels, delimiter is spaces, msq, filters(
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 6))
));