
-- give the highest $number $1 for sensor $sensor

select $1,count(*) from apache_access_log_1
where sensor_id = $sensor
group by $1
order by count(*) desc;
limit $number

$1 = remote_username
$1 = status
$1 = user_agent
$1 = url

-- give the top of urls which did not send any data across (redirects and/or problems)

select url,count(*) from apache_access_log_1 
where sensor_id = $sensor and bytes_sent = 0 
group by url 
order by count(*) desc;

-- give $number most recent logs for sensor $sensor

select message from apache_access_log_1
where sensor_id = $sensor
order by time desc
limit $number

--- give min max and avg delay between log generation and log put in db for $sensor
select min(received_time - time),max(received_time - time),avg(received_time - time) from events
where sensor_id = $sensor

-- look at all processed logfiles for $sensor
select * from logfiles
where sensor_id = $sensor

-- most and least recent statistics ... to see how long it was since the last
-- log was found and processed
select min(now()-read_time),max(now()-read_time),
       min(now()-sensor_time),max(now()-sensor_time) from logfiles
where sensor_id = $sensor

-- look at all "last" sequence_ids in a row of logfiles.  These might indicate
-- a problem - wrapper restart (or they might also just be the last logfile)
select * from logfiles as l
where
      sequence_id = (select max(sequence_id) from logfiles as l2
                     where l.sensor_id = l2.sensor_id
                       and l.wrapper_id = l2.wrapper_id
                       and l.generation_id = l2.generation_id
                    )

-- look at holes in sequences of logfiles (this returns all logfiles
-- which are last in a sequence of logfiles
-- if the sequence is 1 2 4 5 6 7, this returns 2 and 7
select * from logfiles as l1 where not exists (select sequence_id from logfiles as l2 where l2.sequence_id = l1.sequence_id+1 and l1.agent_id = l2.agent_id and l1.wrapper_id = l2.wrapper_id and l1.generation_id = l2.generation_id);

