topic AW:Web server Log files analysis dashboard in QlikView

Web server Log files analysis dashboard

ipauldur — Tue, 21 Jul 2009 15:31:23 GMT

Hi,

Can anyone send me a dashboard of web server log file analysis.

regards,

Durai.

AW:Web server Log files analysis dashboard

Tue, 21 Jul 2009 16:04:11 GMT

http://demo.qlik.com/downloads/qvw/QlikViewServerPerformance.qvw

AW:Web server Log files analysis dashboard

ipauldur — Tue, 21 Jul 2009 16:15:33 GMT

Hi,

Thanks,

But i want apache webserver log file analysis dashboard.

AW:Re: AW:Web server Log files analysis dashboard

Tue, 21 Jul 2009 16:19:12 GMT

How about creating your own app? It's not that difficult. 🙂

I don't know any existing demos for Apache...

Web server Log files analysis dashboard

stevedark — Tue, 21 Jul 2009 20:02:34 GMT

Hi Durai,

Obviously there are lots of options for configuring your Apache logs, so your logs might not match what I have here. You may want to post an extract of your logs to let us know what you are trying to pull in.

This code will pull in logs in a reasonably standard format. You may need to tweak it to work with your own:


ApacheLog:
LOAD
 *,
 if(index('.jpg.ico.gif.png',Extension) > 0, 'Yes', 'No') as IsImage;
LOAD
 *,
 if(index(Page, '.', -1) > 0, mid(Page, index(Page, '.', -1)), '') as Extension;
LOAD
 *,
 if(index(URL, '?') > 0, mid(URL,1, index(URL, '?') -1), URL) as Page,
 if(index(URL, '?') > 0, mid(URL,index(URL, '?')+1), '') as Params;
LOAD
 *,
 daystart(DateTime) as Date,
 mid(RawRequest, 1, index(RawRequest, ' ', 1)) as Method,
 mid(RawRequest, index(RawRequest, ' ', 1),
 index(RawRequest, ' ', 2) - index(RawRequest,
 ' ', 1)) as URL,
 mid(RawRequest, index(RawRequest, ' ', 2)) as Protocol,
 if(index(Referrer, '/', 3) > 0,
 mid(Referrer, index(Referrer, '/', 2) + 1,
 (index(Referrer, '/', 3) - index(Referrer, '/', 2)) - 1), '') as ReferrerSite;
LOAD
 @1 as IPAddress,
 date(date#(mid(@4, 2, 21), 'DD/MMM/YYY:hh:mm:ss'), 'DD MMM YYYY hh:mm:ss') as DateTime,
 replace(@5, ']', '') as TimeZone,
 @6 as RawRequest,
 @7 as Response,
 @8 as Bytes,
 @9 as Referrer,
 @10 as Browser
FROM
c:\logfiles\*.dat
(txt, codepage is 1252, no labels, delimiter is spaces, msq);

That should certainly give you a good start. Post back how you get on with it.

Cheers,
Steve

Web server Log files analysis dashboard

ipauldur — Tue, 21 Jul 2009 20:29:35 GMT

Dear Steve,

Great, Thanks.

Really ,i was struggling to load that log file.

Now it is working fantastic.

Can i get sample dashboard using these log file?

Thanks,

Durai.

Web server Log files analysis dashboard

stevedark — Tue, 21 Jul 2009 21:28:59 GMT

Hi Durai,

No worries. I haven't done a dashboard as such with an Apache log file, but you can have the .qvw as it stands. It just shows the data on a single screen. Adding charts and the like should be pretty straightforward from there.

I've spotted a bug in the code I posted above, where it is extracting the Page field, it should read:

if(index(URL, '?') > 0, mid(URL,1, index(URL, '?') -1), URL) as Page,

In the code as it is posted above the '-1' is in the wrong place, causing it to not report pages which have parameters.

Cheers,
Steve

Web server Log files analysis dashboard

ipauldur — Sun, 26 Jul 2009 15:44:28 GMT

Dear Steve,

Thanks for your support.

I am facing a new issue, Actually , i have the log file size is around 1.5GB.

If i am trying to load using the script which is sent by you, then getting error "Out of virtual memory" after fetching 22 lakhs records.

Even if iam writing where condition for particular period(eg:for 1 month data) then also receiving the error.

My pc has 4GB Ram. Is there any other way to do the reload.

Thanks,

Durai.

Web server Log files analysis dashboard

rwunderlich — Mon, 27 Jul 2009 06:33:49 GMT

Timestamps will use a lot of storage because of the number of unique values. Try to parse date and time into two seperate fields. Instead of:

date(date#(mid(@4, 2, 21), 'DD/MMM/YYY:hh:mm:ss'), 'DD MMM YYYY hh:mm:ss') as DateTime

use:
date(date#(mid(@4, 2, 12), 'DD/MMM/YYYY'), 'DD MMM YYYY') as Date
time(time#(mid(@4, 17 8), 'hh:mm:ss'), 'hh:mm:ss') as Time

I may not have the offsets right, but hopefully you get the idea.

Also, do you need the RawRequest field? If you have a lot of query strings (?) you can have many unique values, taking a tremendous amount of storage. Perhaps you can just extract the page name?

-Rob