Apache log files
logQL has two primary benefits over all other solutions:
- Lightweight, easy to install and get started.
- Employs a simple query language that makes it very flexible.
What’s more? logQL Basic is Free!! You can do everything we describe in thi page using the basic version.
logQL requires Java 1.5 or above, you can download this for free from www.java.com.
To install logQL, just download the zip file and extract it.
To launch logQL, locate logQL.jar in the install directory and double click.
Before we start writing queries we have to open the file and point to the related meta.
Goto File > Open > Custom
Select the apache log file
Then we need to select the meta. This is what tells logQL about the structure of the data in
the log file. logQL comes packaged with a basic version
under the logQL install directory, go to the docs directory and select meta.xml
The combo box shows the different Meta in the file. In this case, the file has data in
apache-combined format and we select this.
You can always configure this file to match the data in your log files. Please refer to Help
> Meta Files to know more.
You will see a screen like the image below:
The dialog box shows all the columns you can query on and their data types.
We can start off with a simple query.
In the text field below the menu bar type
This will execute the query and the results will show up in the area below.
Now, lets see how we can progressively dig deep into this data. Execute
select path, count(*)
You will notice that it’s reporting on a lot of image files. to filter this out, we add a
“where” clause. The where clause filters the output based on the conditions provided. We add
the condition path like “%.html” This will only show results where path ends with .html
select path, count(*) where path like "%.html"
Here we notice photoindex.html has the most number of hits. We can modify the query to see
the top referrers to this page:
select path, referer, count(*) where path = "/photoindex.html"
You will notice in the query that we’ve added referrer and modified the condition to filter
only for photoindex.html
This has returned us a lot of internal links. We can filter out the internal links by adding
another condition to the where clause.
Lets remove path column, it’s redundant.
select referer, count(*) where path = "/photoindex.html" and referer notlike "%diskviz.com%"
This is giving us all the pages that have links to photoindex.html and the number of clicks
from each of them.
what if we only want the domain? We can accomplish that by using the string tokenizer
function strtok(<column>, <token>, <position>)
select strtok(referer,"/",3), count(*) where path = "/photoindex.html" and referer notlike "%diskviz.com%"
We can Export this to excel for further analysis using the File > Save As > CSV option. Or
we can save it as HTML to share it.
logQL also supports charts to easily analyze larger datasets.
To analyze subset, it only charts on the columns that are selected.
To know more about writing queries, watch the 7min video basic tutorial
Download logQL from here.