In the earlier section, we looked at how to create a parser by defining a little language that understood what 
we were trying to parse, here we will try an create a little language that we can use to look at the data in a 
more comprehensive way.

One of the more interesting things that the Web Server provides is the access logs that tells us who are visiting 
our site, how many pages they visited, who are all referring to us, and how many (if any) pages have broken 
links (ie how many 404 we have.)

Interesting info also includes the type of UserAgents, the pages that seem to get hit the largest, and our entry 
points, (The pages that a unique ip will have accessed first,) our exit points (The page which the unique ips 
accessed last) etc.

The commands that wadm provides for logs:

> info commands \*log\*  
get-log enable-access-log set-log-prop get-access-log get-access-log-prop get-log-prop   
set-access-log-buffer-prop rotate-log get-access-log-buffer-prop disable-access-log  

While the wadm does provide a means for us to get the access log as a whole, they are not meant to be 
used directly. (ie they just dump the whole information as a chunk with out any way to filter the information to 
what is relevant for us)

But this is not a very serious handicap as long as we have the scripting ability. Infact it provides us freedom 
to define the interface that we like to to access the data which we are interested in.

Substructure: Access Log from wadm

> get-access-log --config=test agneyam  
format=%Ses->client.ip% - %Req->vars.auth-user% [%SYSDATE%] "%Req->reqpb.clf-request%"   
%Req->srvhdrs.clf-status% %Req->srvhdrs.content-length% - - [20/Oct/2006:06:27:57 -0700] "GET / HTTP/1.1" 200 355 - - [20/Oct/2006:06:27:58 -0700] "GET /favicon.ico HTTP/1.1" 200 3574 - - [20/Oct/2006:06:28:03 -0700] "POST /cgi-bin/test-cgi HTTP/1.1" 200 596 - - [20/Oct/2006:06:28:22 -0700] "GET / HTTP/1.1" 200 355 - - [20/Oct/2006:06:28:22 -0700] "GET /favicon.ico HTTP/1.1" 200 3574 - - [20/Oct/2006:06:28:24 -0700] "POST /cgi-bin/test-cgi HTTP/1.1" 200 602 - - [20/Oct/2006:06:28:48 -0700] "GET /cgi-bin/test-cgi?myvar=new HTTP/1.1" 200 573 - - [20/Oct/2006:06:29:06 -0700] "GET /whoami.html HTTP/1.1" 404 292 - - [20/Oct/2006:23:09:15 +0530] "GET / HTTP/1.1" 200 355  

(You can also modify the settings of access log by using the following CLIs)

> get-access-log-prop  --config=test  
format=%Ses->client.ip% - %Req->vars.auth-user% [%SYSDATE%] "%Req->reqpb.clf-request%"   
%Req->srvhdrs.clf-status% %Req->srvhdrs.content-length%  

Assuming your new custom format is in the variable $myformat

> set-access-log-prop --config=test format=$myformat  

Choosing a superstructure

The access log is a matrix that is arranged cronologically. The SQL (Structured Query Language) is generally 
useful in mining data out of these matrices. More so because the users will be able to transpose at least a part 
of their familiarity with SQL to our API.

While it is useful to stick close to SQL, I am changing it slightly so as to make it easier to parse. In general 
this is what I am going to do:


select {select list} from {from list} where {where list} group by {id}  

The where part and later are optional.
Each of the key words are followed by a list which specifies the list associated with it. The list will contain 
the variables that will be extracted from the logs.

Some UseCases

# Avoiding this common use case for now to make the code a little more simpler  
# select {\*} from machine1 
# The reason for choosing $xxx is that we will be able to replace them with the corresponding  
# values just by evaluating them. This will make it much simpler to construct the result.  
select {$ip,$request,$response} from machine1  
select {$ip,$request,$response} from {machine1,machine2}  
select {$ip,$request,$response} from machine1 where {$response == 404}  
select {$ip [:sum $size] } from machine1 where {$response == 404} group by {$ip}  

More assumptions and short cuts (In the interest of simplicity)

While it is easy to parse the format line from get-access-log-prop format=%Ses->client.ip% - %Req->vars.auth-user% [%SYSDATE%] "%Req->reqpb.clf-request%" %Req->srvhdrs.clf-status% %Req->srvhdrs.content-length% We wont be parsing that, Instead we will use fixed positions for now.

ie: from the above

$ip : 0  
#dummy field -  
$auth : 2  
$date : 3  
#you can always massage the data to remove the _"_ from request and you will get the $method, $uri, $http from the $request.  
$request : 5  
$response : 6  
$size : 7  

Implementing the Language

As always it is a good idea to start with a namespace.


namespace eval Logs {  
    namespace export {select}  
    proc select args {  
            puts ">$args"  

Remember what we discussed last section? The args is the variable args mechanism for tcl. 
so any tcl statement that takes args as a parameter will take any number of parameters. With 
that in mind, the proc select will be able to slurp the entire query that we will throw at it.

The namespace export marks for export the procedures that we want others to be able to 
use natively. ie by using

> namespace import Logs::select  

from global namespace, the user will be able to make use of our language with out polluting his 
namespace with our local procedurs.

The select statement.
proc select args {  
    set select {}  
    set from {}  
    set where {}  
    set group {}  
    set group_by {}  
    set lastword select  
    foreach word $args {  
        #look for our delimiters  
        switch $word {  
            from {  
                set lastword from  
            where {  
                set lastword where  
            group {  
                set lastword group  
            by {  
                set lastword group_by  
            default {  
                lappend $lastword $word  
    exec_select [join $select { }] [join $from { }] [join $where { }] [join $group_by {}]  

What we are doing here, is to take an easy way.  We are just looking for the keywords ‘from’ ‘where’ 
and ‘group’ to extract the lists following them, we use the tcl’s dynamic nature to just change the variable 
being appended to. 

You would have under stood the reason whey we went for a {$ip $response} model rather than simply 
$ip $response in the select statement,…   
          The reason is that if we had gone for $ip $response model, tcl would have evaluated them in the 
execution local context rather than wait for us to supply them. the braces {} provides us a convenient 
bubble to protect against evaluation before time.

check it out with the exec_select

proc exec_select {select from where group} {  
     puts "-------------"  
     puts "select>"  
     puts $select  
     puts "from>"  
     puts $from  
     puts "where>"  
     puts $where  
     puts "group_by>"  
     puts $group  
     puts "-------------"  
> select {$ip -> $response} from agneyam where {$response == 404}  
$ip -> $response  
$response == 404  

Using the $select list as an output formater, and $where as the condition.
Here is what the exec select would look like.

proc exec_select {select from where group} {  
    set collect 0  
    # check if we have grouping statements in select (:sum)  
    # if there are, then we use the current select statement as a template for two types of statements  
    # one to keep adding to an accumulator each variable that is grouped, and the other template to  
    # fetch all the data at the end of the loop from the accumulator and construct the result.  
    # if there are none, then we just have one template which will contain the variables that will be  
    # expanded inline at the end of each iteration.  

    #use_select and collect_select are the two templates. use_select sends data to accumulator and  
    #collect_select retrieves them.  

    if {[regexp {:[a-zA-Z]+} $select]} then {  
        set collect 1  
        regsub -nocase -all {:([a-zA-Z]+) } $select {use_\\1 [incr use_id]:$id } use_select  
        regsub -nocase -all {:([a-zA-Z]+) } $select {collect_\\1 [incr use_id]:$id } collect_select  
    } else {  
         set use_select $select  

    #process the from here.   
    #interleave-logs will collect all the logs from the list of machines supplied and splat it into a list.  
    set cmd {[interleave-logs $from]}  
    set all_logs [subst $cmd]  
    foreach line $all_logs {  
        set use_id 0  
        set id [subst_in_context $line $group]  
        if {[expr [string length $where] > 0]} {  
            if [expr $where] {  
                eval_select $line $use_select  
        } else {  
            eval_select $line $use_select  

    if $collect {  
        foreach id [array names Logs::context] {  
            set line $Logs::context($id)  
            set use_id 0  
            puts [subst_in_context $line $collect_select]  
            unset Logs::context($id)  

Interleave logs is a tiny procedure that mixes the logs from multiple machines.

proc interleave-logs {args} {  
    set fmt [Date::GetFormat]  
    Date::SetFormat {\\[DD/MMM/YYYY:T24S}  
    set logs {}  
    foreach machine $args {  
        set log [get-log $machine]  
        set logs [concat $logs $log]  
    set sorted [lsort -command compare-dates $logs]  
    Date::SetFormat $fmt  
    return $sorted  

The accumulator is pretty simple.

proc use_sum {_id val} {  
    upvar 1 ip ip  
    upvar 1 auth auth  
    upvar 1 date date  
    upvar 1 request request  
    upvar 1 response response  
    set id [subst $_id]  
    if [info exists Logs::results($id)] {  
        array set Logs::results [list $id "$Logs::results($id) $val"]  
    } else {  
        array set Logs::results [list $id "$val"]  
    return $val  
proc collect_sum {_id val} {  
    upvar 1 ip ip  
    upvar 1 auth auth  
    upvar 1 date date  
    upvar 1 request request  
    upvar 1 response response  
    upvar 1 size size   
    set id [subst $_id]  
    set lst $Logs::results($id)  
    set sum 0  
    foreach l $lst {  
        set sum [expr $sum + $l]  
    set Logs::results($id) {}  
    return $sum  

To define any new [:xxx ] procedure that you want to apply in select procedure, you just have to 
copy the previous procedures, define the use_xx and collect_xxx, and change the list processing in 
collect_xxx accourdingly (as given below.)

    set sum  
    foreach l $lst {  
        set sum [your_func $sum $l]  

As you would have noticed there is some redundancy which can be eliminated. but this form is 
easier to understand than after eliminating the common portion.

The complete db.tcl is available here.

Using it

1) Make sure that the date.tcl from the below section is in your current directory along with db.tcl

> source db.tcl  
> select {$ip, $request, $response } from agneyam, GET /favicon.ico HTTP/1.1, 200, POST /cgi-bin/test-cgi HTTP/1.1, 200, GET /favicon.ico HTTP/1.1, 200, GET / HTTP/1.1, 200, POST /cgi-bin/test-cgi HTTP/1.1, 200, GET /cgi-bin/test-cgi?myvar=new HTTP/1.1, 200, GET /whoami.html HTTP/1.1, 404, GET /mexico.html HTTP/1.1, 404, GET / HTTP/1.1, 200  
> select {$ip, $request, $response } from agneyam where {$response == 404}, GET /whoami.html HTTP/1.1, 404, GET /mexico.html HTTP/1.1, 404   
> select {$ip [:sum $size] } from agneyam group by {$ip} 355 5688 4170   
> select {$response [:sum $size] } from agneyam group by {$response}  
404 584   
200  9629   
> select {$ip [:sum 1] } from agneyam group by {$ip}             1 6 2   
> select {$response [:sum 1] } from agneyam group by {$response}  
404 2   
200 7   

required utilities:

date.tcl from here or here(external)
Download the sourcefile and save it as date.tcl. Use the below command to use it before you source the db.tcl (It is already sourced in db.tcl)

> source date.tcl  

The complete db.tcl is available here.