Custom Analysis of Cookie Log

.gitignore

data/
rawdata/

README.md

Custom Log file processing

Please see the Makefile for processing steps. You also need logfiles in the right format.

The custom log format used to generate the log files for an Apache server is detailed below. It is designed to only log requests for certain URLs, but put the cookies into the log files to be able to perform further analysis like the one in this git repo.

SetEnvIf Request_URI "^/201[3-9]/[0-9][0-9]/" content
SetEnvIf Request_URI "^/test" content
SetEnvIf Request_URI "^/[^/]+test" content
SetEnvIf Request_URI "^/$" content
LogFormat "%h;%{%Y-%m-%d %T}t;\"%r\";\"%{Cookie}i\";\"%{User-agent}i\";%D" cookielog
CustomLog /var/log/apache2/versicherungsmonitor.de-cookie.log cookielog env=content

Makefile

SHELL := /bin/bash


all : data/users_with_failed_logins.xlsx data/sessions_per_day.xlsx data/failed_success_by_os.xlsx


get-logfiles: 
	rsync -av vm-hetzner:/var/log/apache2/versicherungsmonitor.de-cookie.log\* rawdata/

%.xlsx : %.csv
	ssconvert $< $@


data/alldata.gz: rawdata/ 
	$(shell (for A in $$(ls -1 --sort=time --reverse rawdata/vers*) ; do zcat -f $$A ; done) | gzip -c >data/alldata.gz)
	touch -r data/alldata.gz rawdata

data/logindata.gz: data/alldata.gz 
	zcat data/alldata.gz | grep --binary-files=text 99/login | gzip -c > data/logindata.gz

data/login.csv.gz: data/logindata.gz
	zcat data/logindata.gz | extract.py ';(?P<date>[^;]+);' 'PHPSESSID=(?P<session>[^";]+)' 'wordpress_logged_in_[^=]+=(?P<user>.*?)%7C' 'login\.(?P<status>[a-z]+)' '(Mozilla|Opera)/[^ ]+ \((?P<os>[^\)]+)' | gzip -c >data/login.csv.gz

data/logins_per_day.gz: data/login.csv.gz	
	q -H -d, -O "select count(*) as logins, substr(date,0,11) as day, max(user) as user from data/login.csv.gz where status='success' group by day,user order by logins,user" | gzip -c > data/logins_per_day.gz

data/logins_per_session.gz: data/login.csv.gz
	q -O -H -d, "select count(*) as logins,session,max(user) as user, min(date) as start, date(max(date))-date(min(date)) as duration from data/login.csv.gz where status='success' group by session order by logins desc" | gzip -c - > data/logins_per_session.gz

data/session-success-failure.gz: data/login.csv.gz
	q -O -d, -H 'select count(*) as lines,  group_concat(status) as status, min(date) as started, max(date) as ended, (strftime("%s",max(date))-strftime("%s",min(date)))/60 as duration, max(user) as user from data/login.csv.gz group by session order by started asc' | gzip -c - >data/session-success-failure.gz

data/users_with_failed_logins.csv: data/session-success-failure.gz
	q -O -d, -H 'select count(*) as sessions,  max(user) as user, min(started) as started, max(ended) as ended  from data/session-success-failure.gz where status like "%failure%" and user>"" group by user having sessions>4 order by sessions desc' | sed 's_%40_@_' >data/users_with_failed_logins.csv

data/sessions_per_day.csv: data/session-success-failure.gz
	q -O -d, -H 'select count(*) as sessions,  max(lower(user)) as user, strftime("%Y-%m-%d",started) as day from data/session-success-failure.gz where user>"" and status not like "%failure%" group by day,user order by sessions desc' | sed 's_%40_@_' >data/sessions_per_day.csv

data/failed_success_by_os.csv: data/login.csv.gz
	q -O -d, -H 'select instr("failure",status)>0 as failed, count(*) as sessions,substr(os,1,10) as os_short from data/login.csv.gz where os_short>""  group by os_short,failed order by os_short,failed desc' >data/failed_success_by_os.csv

request-makefile

SHELL:=/bin/bash
USER:=sss@gmx.info
PASSWORD:=test

all :
	$(info Be more specific.)


logins: login-via-wpsg login-via-login login-via-plugin
	$(info Done.)

login-via-wpsg:
	curl -D- 'http://versicherungsmonitor.de/profil/' -H 'Pragma: no-cache' -H 'Origin: http://versicherungsmonitor.de' -H 'Accept-Encoding: gzip, deflate' -H 'Accept-Language: en,en-US;q=0.8,de;q=0.6,es;q=0.4' -H 'Upgrade-Insecure-Requests: 1' -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36' -H 'Content-Type: application/x-www-form-urlencoded' -H 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8' -H 'Cache-Control: no-cache' -H 'Referer: http://versicherungsmonitor.de/' -H 'Cookie: PHPSESSID=t88mneseqvqth6l3q5soii6a31; __utmt=1; __utma=75961362.1547621932.1485449673.1485449673.1485449673.1; __utmb=75961362.1.10.1485449673; __utmc=75961362; __utmz=75961362.1485449673.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); wpsg_widget_login_attempt=true' -H 'Connection: keep-alive' --data 'wpsg%5Bmod_kundenverwaltung%5D%5Bemail%5D=sss%40gmx.info&wpsg%5Bmod_kundenverwaltung%5D%5Bpassword%5D=test&wpsg_mod_kundenverwaltung_login=Anmelden&wpsg_referer=%2F' --compressed


login-via-login:
	curl -D- 'http://versicherungsmonitor.de/wp-login.php' -H 'Pragma: no-cache' -H 'Origin: http://versicherungsmonitor.de' -H 'Accept-Encoding: gzip, deflate' -H 'Accept-Language: en,en-US;q=0.8,de;q=0.6,es;q=0.4' -H 'Upgrade-Insecure-Requests: 1' -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36' -H 'Content-Type: application/x-www-form-urlencoded' -H 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8' -H 'Cache-Control: no-cache' -H 'Referer: http://versicherungsmonitor.de/wp-login.php' -H 'Cookie: PHPSESSID=t88mneseqvqth6l3q5soii6a31; __utmt=1; __utma=75961362.1547621932.1485449673.1485449673.1485449673.1; __utmb=75961362.3.10.1485449673; __utmc=75961362; __utmz=75961362.1485449673.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); wordpress_test_cookie=WP+Cookie+check' -H 'Connection: keep-alive' --data 'log=sss%40gmx.info&pwd=test&rememberme=forever&wp-submit=Anmelden&redirect_to=http%3A%2F%2Fversicherungsmonitor.de%2Fwp-admin%2F&testcookie=1' --compressed


login-via-plugin:
	curl -D- 'http://versicherungsmonitor.de/testseite-login/' -H 'Pragma: no-cache' -H 'Origin: http://versicherungsmonitor.de' -H 'Accept-Encoding: gzip, deflate' -H 'Accept-Language: en,en-US;q=0.8,de;q=0.6,es;q=0.4' -H 'Upgrade-Insecure-Requests: 1' -H 'User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.87 Safari/537.36' -H 'Content-Type: application/x-www-form-urlencoded' -H 'Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8' -H 'Cache-Control: no-cache' -H 'Referer: http://versicherungsmonitor.de/testseite-login/' -H 'Cookie: PHPSESSID=t88mneseqvqth6l3q5soii6a31; __utmt=1; wordpress_test_cookie=WP+Cookie+check; __utma=75961362.1547621932.1485449673.1485449673.1485449673.1; __utmb=75961362.6.10.1485449673; __utmc=75961362; __utmz=75961362.1485449673.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)' -H 'Connection: keep-alive' --data 'option=afo_user_login&redirect=http%3A%2F%2Fversicherungsmonitor.de%2Ftestseite-login%2F&user_username=sss%40gmx.info&user_password=test&login=Login' --compressed