Add a time grouping query to sqlite

[gm3dmo]

unicorn.log

Top 20 unicorn current_user

select current_user, COUNT(current_user) as count_current_user from unicorn group by current_user order by count_current_user desc limit 20;

Top 20 by current_user by hour

SELECT strftime ('%Y-%m-%d %H',now) by_hour,  current_user, count(current_user)  as count_of_current_user  from unicorn group by strftime ('%H',now), current_user order by count_of_current_user desc limit 20;

Current_user by hour

SELECT strftime ('%Y-%m-%d %H',now) by_hour,  current_user, count(current_user)  as count_of_current_user  from unicorn group by strftime ('%H',now), current_user 

Top 20 unicorn users and their "routes"

select current_user, route, COUNT(current_user) as count_current_user from unicorn group by current_user, route order by count_current_user desc limit 20;

Rate limiting: Find rate limit of users and count where rate_limit_remaining is 0:

select rate_limit, current_user, count(current_user) as ct from unicorn where rate_limit_remaining = 0 group by current_user order by ct desc;

Rate limiting: rate limit applied to each user

select rate_limit, current_user, count(current_user) as ct from unicorn group by rate_limit;

Find and count user agent for a particular user "XYZ" and order by hour

SELECT strftime ('%Y-%m-%d %H',now) by_hour,  user_agent, count(user_agent)  as ct  from unicorn where current_user = 'XYZ' group by strftime ('%H',now), user_agent;

sum of user XYX by user agent:

 select sum(count_of_user_agent) as total_from_user from(SELECT strftime('%Y-%m-%d %H',now) by_hour,  auth_fingerprint, user_agent, count(user_agent) as count_of_user_agent, remote_address  from unicorn where current_user = 'XYZ' group by strftime ('%H',now), user_agent order by by_hour asc);

SELECT strftime ('%Y-%m-%d %H:%M',now) hour_min,  user_agent, count(user_agent)  as ct  from unicorn where status = 500 group by strftime ('%H:%M',now), user_agent;

.headers on
.mode columns
.width 0 0 0
SELECT strftime ('%Y-%m-%d %H:%M',now) hour_min,  user_agent, count(user_agent)  as ct  from unicorn where status = 500 group by strftime ('%H:%M',now), user_agent;

SELECT strftime ('%Y-%m-%d %H:%M',now) hour_min,  user_agent, count(user_agent)  as ct  from unicorn where status like '5%' group by strftime ('%H:%M',now), user_agent;

SELECT strftime ('%Y-%m-%d %H:%M',now) hour_min,  status, count(status)  as ct  from unicorn where status = 500 group by strftime ('%H:%M',now), status order by hour_min;

[gm3dmo]

auth.log

sqlite> .schema auth.log
sqlite> .schema auth
CREATE TABLE auth(
  "now" TEXT,
  "method" TEXT,
  "message" TEXT,
  "at" TEXT,
  "success" TEXT,
  "from" TEXT,
  "request_id" TEXT,
  "url" TEXT,
  "user_agent" TEXT,
  "login" TEXT,
  "raw_login" TEXT,
  "two_factor_type" TEXT,
  "failure_reason" TEXT,
  "failure_type" TEXT,
  "hashed_token" TEXT,
  "token_type" TEXT,
  "line_number" TEXT,
  "line_length" TEXT
);

create view PercentageOf_raw_login as select raw_login, round(100.0 * count() / (select count() from auth), 2) as Percentage from auth group by raw_login;
 select * from PercentageOf_raw_login order by Percentage desc limit 20;

Percentage of repository in auth.log

create view PercentageOf_repo as select repo, round(100.0 * count() / (select count() from auth), 2) as Percentage from auth group by repo;

at field

.timer on
.headers on
.mode columns
.width 40 20
 select at, count(at) at_count from auth group by at order by at_count desc;

Example response:

sqlite>  select at, count(at) at_count from auth group by at order by at_count desc;
at                                        at_count
----------------------------------------  --------------------
success                                   7275599
Looking for ldap mapping                  131177
Searching for user entry                  94454
Invalid ldap username                     84580
user mapped to ldap dn                    75315
failure                                   60393
Invalid ldap credentials                  25608
Membership not found                      9812
                                          6620
Created user                              28
Run Time: real 3.543 user 2.758730 sys 0.783633

Login failure type

select failure_type, count(failure_type) as failure_type_count from auth  group by failure_type order by failure_type_count desc;

Top 20 login by hour for auth.log (top 20):

SELECT strftime ('%Y-%m-%d %H',now) by_hour,  login, count(login)  as authentications_count  from auth group by login order by authentications_count desc limit 20;

Peak users by minute for auth.log

SELECT strftime ('%Y-%m-%d %H:%M',now) hour_min,  login, count(login)  as authentications_count  from auth group by strftime ('%H:%M',now), login order by hour_min;

Top 20 where auth not successful

select raw_login, at, count(at) at_not_success  from auth where at not like "suc%" group by at, raw_login order by at_not_success desc limit 20;

Message

select message, count(message) as message_count from auth  group by message order by message_count desc;

select "from", count("from") as count_of_from  from auth group by "from" order by  count_of_from desc;

SELECT strftime ('%Y-%m-%d %H',now) hour, login, count(login) as authentications_count, at from auth where at = 'failure' group by strftime ('%H',now), login order by hour;

[gm3dmo]

Exceptions

Count of class

select "class", count("class") as count_of_class  from exceptions group by class order by  count_of_class desc;

Count of user

select "user", count("user") as count_of_user  from exceptions group by user order by  count_of_user desc;

Count of controller

select "controller", count("controller") as count_of_controller  from exceptions group by controller order by  count_of_controller desc;

Count of cause

select "cause", count("cause") as count_of_cause  from exceptions group by cause order by  count_of_cause desc;

Count of exceptions by api_route

select "api_route", count("api_route") as count_of_api_route  from exceptions group by api_route order by  count_of_api_route desc;

Exceptions by hour

created_at

SELECT strftime ('%Y-%m-%d %H',created_at) by_hour,  class, count(class)  as class_exceptions_count  from exceptions group by class order by class_exceptions_count desc limit 20;

class by hour

SELECT strftime ('%Y-%m-%d %H',created_at) hour,  class, count(class)  class_exceptions_count  from exceptions  group by strftime ('%H',created_at), class;

[gm3dmo]

babeld.log

babeld group msg by hour

SELECT  strftime ('%Y-%m-%d %H',ts) hour, msg,  count(msg) as count_of_message,  from babeld  group by strftime ('%H',ts), msg;

babeld group msg by hour

.mode columns
.headers on
.width 20 25 20
SELECT  strftime ('%Y-%m-%d %H',ts) hour, user, repo,  count(repo) as count_of_repo  from babeld  where  user like '%someuser%' group by repo, strftime ('%',ts), msg order by hour;

babeld group msg by hour where log_level = ERROR

babeld msg by hour

SELECT  strftime ('%Y-%m-%d %H',ts) hour, msg,  count(msg) as count_of_message  from babeld  where log_level = 'ERROR' group by strftime ('%H',ts), msg;

select sum(hour) from (SELECT  strftime ('%Y-%m-%d %H',ts) hour, msg,  count(msg) as count_of_message  from babeld  where log_level = 'ERROR' group by strftime ('%H',ts), msg;) group by hour;

babeld number of errors per hour

select hour, sum(hour) from (SELECT  strftime ('%Y-%m-%d %H',ts) hour, msg,  count(msg) as count_of_message  from babeld  where log_level = 'ERROR' group by strftime ('%H',ts), msg) group by hour

 SELECT  strftime ('%Y-%m-%d %H',ts) hour, msg, user,  count(msg) as count_of_message  from babeld  where log_level = 'ERROR' group by strftime ('%H',ts), msg, user;

count of proto

Summary of proto

 select proto, count(proto) as count_proto from babeld group by proto order by count_proto desc

 select sum(count_of_proto) as total_proto from(SELECT strftime('%Y-%m-%d %H',ts) by_hour,  proto, count(proto) as count_of_proto, ip  from babeld  group by proto);

Top 20 Count of https users in babeld

select user, count(user) count_of_http_user from babeld where proto="http" group by user order by count_of_http_user desc limit 20;

Errors (5xx) in babeld by user

 select user, count(user) as count_user from babeld where http_status like '5%' group by user order by count_user desc;

select user, count(user) as count_of_http_user, http_status, repo from babeld where proto="http" and http_status like '5%' group by user, repo order by repo, count_of_http_user desc;