topic Re: Log file analysis in QlikView

Log file analysis

Fri, 25 Apr 2014 13:06:35 GMT

hi,

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 file analysis

maxgro — Fri, 25 Apr 2014 17:02:23 GMT

could you post a file with an extract of the log?

Re: Log file analysis

Gysbert_Wassenaar — Fri, 25 Apr 2014 17:08:44 GMT

See this discussion for an extraction script: Web server Log files analysis dashboard

Re: Re: Log file analysis

Mon, 28 Apr 2014 07:18:57 GMT

Here is an extract of the file. I thank you for your reply.

Re: Log file analysis

Mon, 28 Apr 2014 07:58:35 GMT

Hi Gysbert,

Thanks for the reply. I consulted the discussion even though the transformations attached I can not import because I'm using the free version of QlikView. However, it is a good starting point. Thank you! I have attached above is an excerpt of my file and could you give me some suggestions on how to set up the analysis with QlikView? I would be very grateful.

Re: Log file analysis

Gysbert_Wassenaar — Mon, 28 Apr 2014 09:47:36 GMT

You can use the code Steve Dark posted to load the data from the log file. Simply replace c:\logfiles\*.dat with the path to your log files. As for how to set up the analysis. That depends on the questions you want answers to. Perhaps other web log analysis tools like for example piwik (piwik.org/docs/piwik-tour/) can give you ideas.

Re: Log file analysis

stevedark — Mon, 28 Apr 2014 10:08:05 GMT

Thanks for the mention Gysbert, and the pointer to this post: Web server Log files analysis dashboard

I can't believe that a post I made nearly five years ago is still kicking about on the forum after a couple of major upgrades.

Another thing that is worth doing with log files is creating a QVD of each file and checking the date/time stamp of the file to decide whether you need to rebuild the QVD. I have created a blog post with example code for achieving this:

http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/

By blending the load script from the Community post and the change checking from the blog Carlo should be able to put together an efficient solution.

- Steve

Re: Log file analysis

Tue, 29 Apr 2014 07:33:32 GMT

GrazieGybsert for the answer and for the tip on the tool Piwik, but as a tool of analysis should I use to force QlikView. Now I try to make fixed record and study the documentation to make the dashboard.

greeting
Carlo

Re: Log file analysis

Tue, 29 Apr 2014 07:37:05 GMT

Thanks Steve for the reply and the suggestion on checking the timestamp. Now also check the link that I have posted.

Greeting

Carlo