topic Re: Log Apache analysis with QlikView in QlikView

Log Apache analysis with QlikView

Fri, 25 Apr 2014 13:46:23 GMT

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

Re: Log Apache analysis with QlikView

Gysbert_Wassenaar — Fri, 25 Apr 2014 17:09:48 GMT

Please don't post the same question twice.

Re: Log Apache analysis with QlikView

Mon, 28 Apr 2014 07:21:49 GMT

I'm sorry I was wrong. I'm new in the community.

Re: Log Apache analysis with QlikView

Tue, 29 Apr 2014 00:07:20 GMT

I would try Fixed Record , see image

Re: Log Apache analysis with QlikView

Clever_Anjos — Tue, 29 Apr 2014 00:16:13 GMT

Use Fixed record in order to split your lines

Re: Log Apache analysis with QlikView

Tue, 29 Apr 2014 07:23:09 GMT

Thanks for the reply Cleaver now trying to do fixed record
Thanks for the support.

Carlo

Re: Log Apache analysis with QlikView

Tue, 29 Apr 2014 07:26:56 GMT

Thanks for the reply with graphic Amir. You have been very kind, now I try with fixed record.

Carlo

Re: Log Apache analysis with QlikView

Wed, 30 Apr 2014 08:13:05 GMT

Hi Amir,

I tried to record fixed but the size of the get is not the same for all requests and get cutting losing information:

To solve this problem I tried with delimited and I chose the space as a delimiter but then so I can not separate the date in day, month, year, as the format is day / month / year and does not include spaces.

Any suggestions? How do I separate the date in day, month, year, and at the same time do not get cut off the leaking information? I would need a fusion between record fixed and delimited

thanks
Carlo

Re: Log Apache analysis with QlikView

Clever_Anjos — Wed, 30 Apr 2014 12:24:54 GMT

Could you post a sample of your log?

I´ll make it for you

Re: Re: Log Apache analysis with QlikView

Wed, 30 Apr 2014 13:47:05 GMT

I am attaching the file to upload with QlikView. The tables at the end I would get are:

IP-address
day-
month-
-year
-now
user-agent
-request
payload-size
-server response

I would like to get something like the following figure, however, in addition to the tables of the day, month, year, hour

Thank you very much, I'm grateful for the response

Re: Re: Re: Log Apache analysis with QlikView

Clever_Anjos — Wed, 30 Apr 2014 14:17:02 GMT

Try this, adjusting to your needs

Re: Re: Re: Log Apache analysis with QlikView

Wed, 30 Apr 2014 15:01:11 GMT

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

Re: Re: Re: Re: Log Apache analysis with QlikView

Clever_Anjos — Wed, 30 Apr 2014 16:32:46 GMT

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

Re: Re: Re: Re: Log Apache analysis with QlikView

Fri, 02 May 2014 09:57:46 GMT

Thanks Clever, I modified your script and I got the subdivision of the string. Without your help I would not be able to get the desired result.

I enclose the final script so that it can be used in the future by other users:

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

));

thank you very much
Carlo