Web server Log files analysis dashboard

[ipauldur]

Hi,

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

regards,

Durai.

[#2]

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

[ipauldur]

Hi,

Thanks,

But i want apache webserver log file analysis dashboard.

[#4]

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

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

[stevedark]

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

[ipauldur]

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.

[stevedark]

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

[ipauldur]

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.

[rwunderlich]

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