asigatchov’s gists

asigatchov / load_access_log.pl

Last active November 4, 2016 14:40

clickhouse parse acces log

	#!/usr/bin/perl

	use strict;
	use warnings;

	use Date::Parse;
	use DateTime;

	my $file = $ARGV[0];
	my $hostname = $ARGV[1];

asigatchov / access_log.sql

Created November 4, 2016 14:50

clickhouse access log table

	CREATE TABLE access_log (
	event_date Date,
	hostname String,
	schema String,
	domain String,
	ip String,
	datetime DateTime,
	method String,
	path String,
	code UInt16,

asigatchov / query_error_page.sql

Created November 4, 2016 19:27

error_page

	SELECT
	toStartOfHour(datetime) AS hours,
	code,
	count() AS error_count,
	bar(error_count, 0, 500) AS error
	FROM access_log
	WHERE (event_date = '2016-11-01') AND (code > 399) AND (code < 599)
	GROUP BY
	hours,
	code

asigatchov / slow_page.sql

Created November 4, 2016 19:29

Поиск медленных страниц по acces.log через clickhouse

	SELECT
	ip,
	avg(resp_time) AS avg_time,
	domain,
	path
	FROM access_log
	WHERE event_date = '2016-11-01'
	GROUP BY
	path,
	ip,

asigatchov / slow_page.sql

Created November 4, 2016 19:29

Поиск медленных страниц по acces.log через clickhouse

	SELECT
	ip,
	avg(resp_time) AS avg_time,
	domain,
	path
	FROM access_log
	WHERE event_date = '2016-11-01'
	GROUP BY
	path,
	ip,

asigatchov / access.log

Created November 4, 2016 19:41

Nginx access.log

https://mysite.ru 5.255.253.63 - - [04/Nov/2016:16:31:28 +0300] "GET /pechene_s_m?page=563 HTTP/1.1" 200 25351 "-" "Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots)" "-" "-" 0.213

asigatchov / slow_ip.sql

Created November 4, 2016 20:24

Поиск медленных ip клиетнов

	SELECT
	floor(avg(resp_time), 2) AS avg_time,
	ip
	FROM access_log
	WHERE event_date = '2016-11-01'
	GROUP BY ip
	ORDER BY avg_time DESC
	LIMIT 10

asigatchov / docker-compose-efk-16gb.yml

Last active January 3, 2017 04:34

	version: '2'
	services:
	elasticsearch:
	image: docker.elastic.co/elasticsearch/elasticsearch:5.1.1
	container_name: elasticsearch
	environment:
	- bootstrap.memory_lock=true
	- "ES_JAVA_OPTS=-Xms7g -Xmx7g"
	- xpack.security.enabled=false
	- http.host=0.0.0.0

asigatchov / install_elastic_kibana_docker.sh

Last active January 3, 2017 04:35

efk_docker_install.sh

	apt-get install apt-transport-https ca-certificates && apt-key adv --keyserver hkp://ha.pool.sks-keyservers.net:80 --recv-keys 58118E89F3A912897C070ADBF76221572C52609D
	echo "deb https://apt.dockerproject.org/repo ubuntu-trusty main" > /etc/apt/sources.list.d/docke.list
	apt-get update && apt-get install docker-engine -y
	curl -L "https://github.com/docker/compose/releases/download/1.9.0/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
	chmod +x /usr/local/bin/docker-compose
	mkdir /opt/efk/
	curl "https://gist.githubusercontent.com/asigatchov/417ac1dad9b57ae2ad25d62e87604f28/raw/7cbb959d3b459f0fc51ae019f4fc42ff5fcf69a6/docker-compose-efk-16gb.yml" > /opt/efk/docker-compose.yml
	dd if=/dev/zero of=/swapfile bs=256M count=12
	chown root:root /swapfile
	chmod 0600 /swapfile

asigatchov / fluentd_source.conf

Created December 28, 2016 19:04

fluentd source

	<source>
	@type tail
	format /(?<schema>https?)://(?<domain>[^ ]) (?<remote>[^ ]) (?<host>[^ ]) (?<user>[^ ]) \[(?<time>[^\]])\] "(?<method>\S+)(?: +(?<path>[^\"]?)(?: +\S)?)?" (?<code>[^ ]) (?<size>[^ ])(?: "(?<referer>[^\"])" "(?<agent>[^\"])") (?<message>.) (?<resptime>\d+\.\d+)$/
	time_format %d/%b/%Y:%H:%M:%S %z
	types remote:string,host:string,user:string,time:time,method:string,path:string,code:integer,referer:string,agent:string,message:string,size:integer,resptime:float

	path /data/access.log
	pos_file /tmp/access_tail_pos.log
	tag es.demo.access
	read_lines_limit 1000

Alexander asigatchov