Aleksey Lizunov, ืืืืคึผื ืคืื ืื ืงืึทืืคึผืึทืืื ืก ืฆืขื ืืขืจ ืคึฟืึทืจ ืจืืืึธืื ืกืขืจืืืืก ืืฉืึทื ืึทืื ืคืื ืืขืจ ืืืจืขืงืืึธืจืึทืืข ืคืื โโืืื ืคึฟืึธืจืืึทืฆืืข ืืขืืฉื ืึธืืึธืืืขืก ืคืื ืื MKB
ืืื ืึทื ืึธืืืขืจื ืึทืืืื ืฆื ืื ELK ืึธื ืืืืื (ElasticSearch, Logstash, Kibana), ืืืจ ืืึธื ืคืึธืจืฉืื ื ืืืืฃ ื ืืฆื ืื ClickHouse ืืึทืืึทืืืืก ืืื ืึท ืืึทืื ืงืจืึธื ืคึฟืึทืจ ืืึธืืก.
ืืื ืืขื ืึทืจืืืงื, ืืืจ ืืืึธืื ืืื ืฆื ืจืขืื ืืืขืื ืืื ืืืขืจ ืืขืจืคืึทืจืื ื ืคืื ื ืืฆื ืื ClickHouse ืืึทืืึทืืืืก ืืื ืื ืคึผืจืืืืืึทื ืขืจื ืจืขืืืืืึทืื ืคืื ืื ืคึผืืืึธื ืึธืคึผืขืจืึทืฆืืข. ืขืก ืืึธื ืืืื ืื ืืขืืืืื ืืืืื ืึทื ืื ืจืขืืืืืึทืื ืืขื ืขื ืืืคึผืจืขืกืืื.
ืืืืึทืืขืจ, ืืืจ ืืืขืื ืืึทืฉืจืืึทืื ืืื ืืขืจ ืืขืืึทื ืืื ืืื ืืืขืจ ืกืืกืืขื ืืื ืงืึทื ืคืืืืขืจื ืืื ืืืึธืก ืงืึทืืคึผืึธืื ืึทื ืฅ ืขืก ืืืฉืืืื ืคืื. ืืืขืจ ืืืฆื ืืื ืืืึธืื ืืื ืฆื ืจืขืื ืึท ืืืกื ืืืขืื ืืขื ืืึทืืึทืืืืก ืืื ืึท ืืึทื ืฅ, ืืื ืืืึธืก ืขืก ืืื ืืืขืจื ืฆื ืืึทืฆืึธืื ืืคืืขืจืงืืึทืืงืืึทื. ืื ClickHouse ืืึทืืึทืืืืก ืืื ืึท ืืืื-ืคืึธืจืฉืืขืืื ื ืึทื ืึทืืืกืืก ืงืึธืืืื ืขืจ ืืึทืืึทืืืืก ืคึฟืื ืืึทื ืืขืงืก. ืขืก ืืื ืืขื ืืฆื ืืื ืืึทื ืืขืงืก ืืึทืืื ืื ืืก, ืืืืืขืก ืขืก ืืื ืื ืืืืคึผื ืืึทืื ืกืืึธืจืืืืฉ ืคึฟืึทืจ Yandex.Metrica. ืขืคึฟื ืืงืืจ ืกืืกืืขื, ืคืจืื. ืคึฟืื ืึท ืืขืืืขืืึธืคึผืขืจ ืก ืคืื ื ืคืื ืืืื ืื ื, ืืื ืฉืืขื ืืืง ืืขืืืืืฉื ืืื ืืื ืืืคึผืืึทืืขื ืึทื ืขืก, ืืืืึทื ืขืก ืืขื ืขื ืคืึทื ืืึทืกืืืฉ ืืจืืืก ืืึทืื. ืืื ืื ืืึทื ืืฆืขืจ ืฆืืืื ื ืคืื Metrica ืืื ืืื ืืืืขืจ ืคืืขืงืกืึทืืึทื ืืื ืฉื ืขื. ืืฒึทื ืขืจืฉืื ืืึทืงืึทื ืื ืืื ืืขืจ ืืึธืืืงืขืจ ืืึทืืึทืืืืก ืืื ืืขืจ ืจืืฉื: โื ื, ืขื ืืืขื! ืืขืืืื ืคึฟืึทืจ ืื ืืขื ืืฉื! ืกืืึทืจืืื ื ืคืื ืื ืืึทื ืืึธื ืืืจืื ื ืคึผืจืึธืฆืขืก ืืื ืกืึธืฃ ืืื ืฉืืงื ืจืืงืืืขืก.
ืื ืืึทืืึทืืืืก ืืื ืึท ืืืืขืจ ื ืืืขืจืืง ืคึผืึธืืืฆืืข ืฉืืืขื. ืืคืืื ืึทื ืืืจืืฉื ืืืืขื-ืืึธืงืข ืืขืืืขืืึธืคึผืขืจ ืงืขื ืขื ืื ืกืืึทืืืจื ืืขื ืืึทืืึทืืืืก ืืื ืึท ืืืกื ืืื ืื ืืื ืึธื ืืืืื ื ืืฆื ืขืก. ืึทืืฅ ืึทืจืืขื ืงืืืจ. ืืคืืื ืืขื ืืฉื ืืืึธืก ืืขื ืขื ื ืืึท ืืื ืืื ืืงืก ืงืขื ืขื ืืขืฉืืืื ื ืฉืขืคึผื ืื ืื ืกืืึทืืืจืื ื ืืื ืืึธื ืื ืกืืืคึผืืึทืกื ืึทืคึผืขืจืืืฉืึทื ื. ืืืื ืคืจืืขืจ, ืืื ืื ืืืขืจืืขืจ Big Data, Hadoop, Google BigTable, HDFS, ืึท ืคึผืจืึธืกื ืืขืืืขืืึธืคึผืขืจ ืืื ืืขืืึทื ืง ืึทื ืขืก ืืื ืืขืืืขื ืืืขืื ืขืืืขืืข ืืขืจืึทืืืืขืก, ืคึผืขืืึทืืืืขืก, ืึทื ืขืืืขืืข ืกืืคึผืขืจืืืืึทื ื ืืขื ืขื ืคืึทืจืงื ืึทืกื ืืื ืกืขืืืื ืืก ืืื ืึทื ืืืืืงืืื ื ืคึฟืึทืจ ืื ืกืืกืืขืืขื, ืืขืืึธืื ืืื ืื ืึทืืืืขื ื ืคืื ืื ืงืืืงืืึธืืกืข ืืึทืืึทืืืืก, ืืืจ ืืึธืื ืึท ืคึผืฉืื, ืคืึทืจืฉืืืืืง ืืขืฆืืึทื ืืื ืืืึธืก ืืืจ ืงืขื ืขื ืกืึธืืืืข ืึท ืืื ืึทืืขืจ ืึทื ืึทืืืื ืึทืืึทื ืงืืื ืคืื ืืึทืกืงืก. ืขืก ื ืขืื ืืืืื ืืืื ืืึทื ืฅ ืืืจืืฉื ืืืืขื ืืึทืฉืื ืืื ืคืื ืฃ ืืื ืื ืฆื ืื ืกืืึทืืืจื. ืืึธืก ืืื, ืืืจ ืืึธืื ืึทืืึท ืึท ืืึทืืึทืืืืก ืืื, ืืืฉื, MySql, ืึธืืขืจ ืืืืื ืคึฟืึทืจ ืกืืึธืจืื ื ืืืืืึทื ื ืคืื ืจืขืงืึธืจืืก! ื ืืืืขืจ ืกืืคึผืขืจ-ืึทืจืืฉืืืืขืจ ืืื ืื SQL ืฉืคึผืจืึทื. ืขืก ืืื ืืื ืืขื ืืฉื ืืขื ืขื ืงืึธืื ืขืจ ืื ืืืขืคึผืึทื ื ืคืื ืืืืืึทื ื.
ืืืขืื ืืื ืืืขืจ ืืึธืืื ื ืกืืกืืขื
ืฆื ืืึทืืืขื ืืื ืคึฟืึธืจืืึทืฆืืข, IIS ืืึธื ืืขืงืขืก ืคืื ื ืึธืจืืึทื ืคึฟืึธืจืืึทื ืืืขื ืึทืคึผืืึทืงืืืฉืึทื ื ืืขื ืขื ืืขื ืืฆื (ืืืจ ืืขื ืขื ืืขืจืืืืึทื ืคึผืึทืจืกืื ื ืึทืคึผืืึทืงืืืฉืึทื ืืึธืืก, ืึธืืขืจ ืืขืจ ืืืืคึผื ืฆืื ืืื ืื ืคึผืืืึธื ืืื ืข ืืื ืฆื ืืึทืืืขื IIS ืืึธืืก).
ืคึฟืึทืจ ืคืึทืจืฉืืื ืกืืืืช, ืืืจ ืงืขื ื ืืฉื ืืึธืจ ืคืึทืจืืึธืื ืื ELK ืึธื ืืืืื, ืืื ืืืจ ืคืึธืจืืขืฆื ืฆื ื ืืฆื ืื LogStash ืืื Filebeat ืงืึทืืคึผืึธืื ืึทื ืฅ, ืืืึธืก ืืึธืื ืคึผืจืึธืืืขื ืืื ืืขืืื ื ืืื ืึทืจืืขื ืืึทื ืฅ ืจืืืืืึทืืื ืืื ืคึผืจืืืืงืืึทืืื.
ืืขืจ ืืขื ืขืจืึทื ืืึธืืื ื ืกืืขืืข ืืื ืืขืืืืื ืืื ืื ืคืืืืจ ืืื ืื:
ื ืฉืืจืื ืคืื ืฉืจืืืื ืืึทืื ืฆื ืื ClickHouse ืืึทืืึทืืืืก ืืื ืืขืืื (ืึทืืึธื ืคึผืขืจ ืกืขืงืื ืืข) ืื ืกืขืจืฉืึทื ืคืื ืจืขืงืึธืจืืก ืืื ืืจืืืก ืืึทืืฉืึทื. ืืึธืก ืืื, ืืฉืืขืืช, ืื ืืขืจืกื "ืคึผืจืึธืืืขืืึทืืืง" ืืืื ืืืึธืก ืืืจ ืืจืขืคื ืืืขื ืืืจ ืขืจืฉืืขืจ ืืขืจืคืึทืจืื ื ืืจืืขืื ืืื ืื ืงืืืงืืึธืืกืข ืืึทืืึทืืืืก: ืื ืกืืขืืข ืืืขืจื ืึท ืืืกื ืืขืจ ืงืึธืืคึผืืืฆืืจื.
ืืขืจ ืคึผืืืืื ืคึฟืึทืจ LogStash, ืืืึธืก ืืืืื ืื ืกืขืจืฅ ืืึทืื ืืื ClickHouse, ืืขืืึธืืคึฟื ืึท ืคึผืืึทืฅ ืืึธ. ืืขืจ ืงืึธืืคึผืึธื ืขื ื ืืื ืืืคึผืืืื ืืืืฃ ืืขืจ ืืขืืืืงืขืจ ืกืขืจืืืขืจ ืืื ืื ืืึทืืึทืืืืก ืืื. ืึทืืื, ืืื ืึทืืืขืืืื, ืขืก ืืื ื ืืฉื ืจืขืงืึทืืขื ืืื ืฆื ืืึธื ืืึธืก, ืึธืืขืจ ืคืื ืึท ืคึผืจืึทืงืืืฉ ืคืื ื ืคืื ืืืื ืื ื, ืึทืืื ื ืืฉื ืฆื ืคึผืจืึธืืืฆืืจื ืืึทืืื ืืขืจ ืกืขืจืืืขืจืก ืืฉืขืช ืขืก ืืื ืืืคึผืืืื ืืืืฃ ืืขืจ ืืขืืืืงืขืจ ืกืขืจืืืขืจ. ืืืจ ืืึธืื ื ืืฉื ืึธืืกืขืจืืืืจื ืงืืื ืคืืืืืขืจื ืึธืืขืจ ืืืื ืงืึธื ืคืืืงื ืืื ืื ืืึทืืึทืืืืก. ืืื ืึทืืืฉืึทื, ืขืก ืืึธื ืืืื ืื ืืขืืืืื ืึทื ืื ืคึผืืืืื ืืื ืึท ืจืืืจืื ืืขืงืึทื ืืืึทื ืืื ืคืึทื ืคืื ืขืจืจืึธืจืก. ืืื ืืื ืคืึทื ืคืื ืขืจืจืึธืจืก, ืื ืคึผืืืืื ืฉืจืืืื ืฆื ืืืกืง ืึท ืคึผืขืงื ืคืื ืืึทืื ืืืึธืก ืงืขื ื ืืฉื ืืืื ืื ืกืขืจืืึทื (ืื ืืขืงืข ืคึฟืึธืจืืึทื ืืื ืืึทืงืืืขื: ื ืึธื ืขืืืืื ื, ืืืจ ืงืขื ืขื ืืืืื ืึทืจืืึทื ืืืืื ืื ืงืขืจืขืงืืึทื ืคึผืขืงื ืืื ืงืืืงืืึธืืกืข-ืงืืืขื ื).
ื ืืึทื ืฅ ืจืฉืืื ืคืื ืืืืืืืืืจื ืืขื ืืฆื ืืื ืื ืกืืขืืข ืืื ืืขืจืืื ืื ืืื ืื ืืืฉ:
ืจืฉืืื ืคืื ืืืืืืืืืจื ืืขื ืืฆื
ื ืึธืืขื
ืืึทืฉืจืืึทืืื ื
ืคืึทืจืฉืคึผืจืืืืื ื ืืื ืง
NGINX
ืคืึทืจืงืขืจื ืคึผืจืึทืงืกื ืฆื ืืึทืืจืขื ืขืฆื ืึทืงืกืขืก ืืืจื ืคึผืึธืจืฅ ืืื ืึธืจืืึทื ืืืืจื ืืขืจืืืืืขื ืืฉ
ืืืฆื ื ืื ืืขืืืืื ื ืืื ืื ืกืืขืืข
FileBeat
ืึทืจืืืขืจืคืืจื ืคืื ืืขืงืข ืืึธืืก.
(ืคืึทืจืฉืคึผืจืืืืื ื ืื ืืืขื ืืึทืจ ืคึฟืึทืจ Windows 64bit).
logstash
ืงืืึธืฅ ืงืึทืืขืงืืขืจ.
ืืขื ืืฆื ืฆื ืืึทืืืขื ืืึธืืก ืคึฟืื FileBeat, ืืื ืืืื ืฆื ืืึทืืืขื ืืึธืืก ืคึฟืื ืื RabbitMQ ืจืื (ืคึฟืึทืจ ืกืขืจืืืขืจืก ืืืึธืก ืืขื ืขื ืืื ืื DMZ.)
Logstash-output-clickhouse
Loagstash ืคึผืืืืื ืคึฟืึทืจ ืืจืึทื ืกืคืขืจืื ื ืืึธืืก ืฆื ืื ClickHouse ืืึทืืึทืืืืก ืืื ืืึทืืฉืึทื
/usr/share/logstash/bin/logstash-plugin install logstash-output-clickhouse
/usr/share/logstash/bin/logstash-plugin install logstash-filter-prune
/usr/share/logstash/bin/logstash-plugin ืื ืกืืึทืืืจื logstash-filter-multiline
ืงืืืงืงืืึทืืก
ืงืืึธืฅ ืกืืึธืจืืืืฉ
ื ืืืืฅ. ืกืืึทืจืืื ื ืคืื ืืืืืืกื 2018, "ื ืึธืจืืึทื" ืจืคึผื ืืืืขื ืคึฟืึทืจ RHEL ืืจืืืก ืืื ืื ืืึทื ืืขืงืก ืจืืคึผืึทืืึทืืึธืจื, ืึทืืื ืืืจ ืงืขื ืขื ืคึผืจืืืืจื ืฆื ื ืืฆื ืืื. ืืื ืืขืจ ืฆืืื ืคืื ืื ืกืืึทืืืจืื ื, ืืืจ ืืขืืืืื ื ืคึผืึทืงืึทืืืฉืึทื ืืขืืืื ืืืจื Altinity.
ืืจืืคืื ื
ืงืืึธืฅ ืืืืืฉืืืึทืืึทืืืืฉืึทื. ืืึทืฉืืขืืืงื ืืึทืฉืืึธืจืื
Redhat & Centos (64 ืืื) - ืื ืืขืฆืืข ืืืขืจืกืืข
ืงืืืงืืึธืืกืข ืืึทืื ืืงืืจ ืคึฟืึทืจ Grafana 4.6+
ืคึผืืืืื ืคึฟืึทืจ ืืจืึทืคืึทื ืึท ืืื ืงืืืงืืึธืืกืข ืืึทืื ืืงืืจ
logstash
ืงืืึธืฅ ืจืึทืืืขืจ ืคึฟืื FileBeat ืฆื RabbitMQ ืจืื.
ื ืืืืฅ. ืฆืื ืืึทืืืืขืจื FileBeat ืืื ื ืืฉื ืจืขืืืืืึทื ืืืืึทื ืฆื RabbitMQ, ืึทืืื ืึท ืื ืืขืจืืืืืื ืืื ืง ืืื ืื ืคืึธืจืขื ืคืื Logstash ืืื ืคืืจืืื ืื
ืงืื ืืื ืืง
ืึธื ืืึธื ืจืื. ืืึธืก ืืื ืืขืจ ืงืืึธืฅ ืืึทืคืขืจ ืืื ืื DMZ
Erlang Runtime (ืคืืจืืื ืื ืคึฟืึทืจ RabbitMQ)
ืขืจืืึทื ื ืจืื ืืืืข. ืคืืจืืื ืื ืคึฟืึทืจ RabbitMQ ืฆื ืึทืจืืขืื
ืื ืกืขืจืืืขืจ ืงืึทื ืคืืืืขืจืืืฉืึทื ืืื ืื ClickHouse ืืึทืืึทืืืืก ืืื ืืขืจืืื ืื ืืื ืื ืคืืืืขื ืืข ืืืฉ:
ื ืึธืืขื
ืืืขืจื
ืืึธื
ืงืึธื ืคืืืืจืึทืืืึธื
ืืื: 40 ืืืืืืืื
ืืึทืจืึทื: ืงืกื ืืืงืกืื
ืคึผืจืึทืกืขืกืขืจ: ืงืึธืจ 2 2ืืื
ืขืก ืืื ื ืืืืืง ืฆื ืืึทืฆืึธืื ืืคืืขืจืงืืึทืืงืืื ืฆื ืื ืขืฆืืช ืคึฟืึทืจ ืึทืคึผืขืจืืืืื ื ืื ClickHouse ืืึทืืึทืืืืก ()
ืึทืืืขืืืื ืข ืกืืกืืขื ืืืืืืืืืจื
ืึทืก: Red Hat Enterprise Linux Server (Maipo)
JRE (Java 8)
ืืื ืืืจ ืงืขื ืขื ืืขื, ืืึธืก ืืื ืึท ืคึผืจืึธืกื ืืืขืจืงืกืืืืฉืึทื.
ืื ืกืืจืืงืืืจ ืคืื ืื ืืืฉ ืคึฟืึทืจ ืกืืึธืจืื ื ืืึธืืก ืืื ืืื ืืืื:
log_web.sql
CREATE TABLE log_web (
logdate Date,
logdatetime DateTime CODEC(Delta, LZ4HC),
fld_log_file_name LowCardinality( String ),
fld_server_name LowCardinality( String ),
fld_app_name LowCardinality( String ),
fld_app_module LowCardinality( String ),
fld_website_name LowCardinality( String ),
serverIP LowCardinality( String ),
method LowCardinality( String ),
uriStem String,
uriQuery String,
port UInt32,
username LowCardinality( String ),
clientIP String,
clientRealIP String,
userAgent String,
referer String,
response String,
subresponse String,
win32response String,
timetaken UInt64
, uriQuery__utm_medium String
, uriQuery__utm_source String
, uriQuery__utm_campaign String
, uriQuery__utm_term String
, uriQuery__utm_content String
, uriQuery__yclid String
, uriQuery__region String
) Engine = MergeTree()
PARTITION BY toYYYYMM(logdate)
ORDER BY (fld_app_name, fld_app_module, logdatetime)
SETTINGS index_granularity = 8192;ืืืจ ื ืืฆื ืคืขืืืงืืึทื ืคึผืึทืจืืืฉืึทื ืื ื (ืืืจื ืืืืฉ) ืืื ืืจืึทื ืืืึทืจืืื ืคืื ืืื ืืขืงืก. ืึทืืข ืคืขืืืขืจ ืคึผืจืึทืงืืึทืงืื ืฉืืืืขื ืฆื IIS ืงืืึธืฅ ืืืื ืกื ืคึฟืึทืจ ืืึธืืื ื ืืืืคึผ ืจืืงืืืขืก. ืกืขืคึผืขืจืึทืืื, ืืืจ ืืึธื ืึทื ืขืก ืืขื ืขื ืืึทืืื ืืขืจ ืคืขืืืขืจ ืคึฟืึทืจ ืกืืึธืจืื ื utm-tags (ืืื ืืขื ืขื ืคึผืึทืจืกื ืืื ืืขืจ ืืื ืข ืคืื โโโโืื ืกืขืจืืื ื ืืื ืื ืืืฉ ืคึฟืื ืื ืึธื ืคึฟืจืขื ืฉืืจืืงื ืคืขืื).
ืืืื, ืขืืืขืืข ืกืืกืืขื ืคืขืืืขืจ ืืขื ืขื ืฆืืืขืืขืื ืฆื ืื ืืืฉ ืฆื ืงืจืึธื ืืื ืคึฟืึธืจืืึทืฆืืข ืืืขืื ืกืืกืืขืืขื, ืงืึทืืคึผืึธืื ืึทื ืฅ, ืกืขืจืืืขืจืก. ืืขื ืื ืืืฉ ืืื ืื ืคึฟืึทืจ ืึท ืืึทืฉืจืืึทืืื ื ืคืื ืื ืคืขืืืขืจ. ืืื ืืืื ืืืฉ, ืืืจ ืงืจืึธื ืืึธืืก ืคึฟืึทืจ ืขืืืขืืข ืกืืกืืขืืขื.
ื ืึธืืขื
ืืึทืฉืจืืึทืืื ื
ืืืึทืฉืคึผืื
fld_app_name
ืึทืคึผืคึผืืืงืึทืืืึธื / ืกืืกืืขื ื ืึธืืขื
ืืืืืืง ืืืขืจืื:
- site1.domain.com ืคืื ืืจืืืกื ืืืง ืคึผืืึทืฅ 1
- site2.domain.com ืคืื ืืจืืืกื ืืืง ืคึผืืึทืฅ 2
- internal-site1.domain.local ืืื ืขืจืืขืืขืจ ืคึผืืึทืฅ 1
site1.domain.com
fld_app_module
ืกืืกืืขื ืืึธืืืืข
ืืืืืืง ืืืขืจืื:
- ืืืขืืกืืื - ืืืขืืกืืื
- svc - ืืืขื ืคึผืืึทืฅ ืืื ืกื
- intgr - ืื ืืขืืจืึทืืืึธื ืืืขื ืกืขืจืืืืก
- bo - ืึทืืืื (ืืึทืงืึธืคืคืืกืข)
ื ืขืฅ
fld_website_name
ืคึผืืึทืฅ ื ืึธืืขื ืืื IIS
ืขืืืขืืข ืกืืกืืขืืขื ืงืขื ืขื ืืืื ืืืคึผืืืื ืืืืฃ ืืืื ืกืขืจืืืขืจ, ืึธืืขืจ ืืคืืื ืขืืืขืืข ืื ืกืืึทื ืกืื ืคืื ืืืื ืกืืกืืขื ืืึธืืืืข
ืืืขื ืืืืคึผื
fld_server_name
ืกืขืจืืืืจืขืจ ื ืึธืืขื
web1.domain.com
fld_log_file_name
ืืืขื ืฆื ืื ืงืืึธืฅ ืืขืงืข ืืืืฃ ืื ืกืขืจืืืขืจ
C:inetpublogsLogFiles
W3SVC1u_ex190711.log
ืืึธืก ืึทืืึทืื ืืืจ ืฆื ืืคืขืงืืืืืื ืืืืขื ืืจืึทืคืก ืืื Grafana. ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืงืืง ืจืืงืืืขืก ืคืื ืื ืคืจืึธื ืืขื ื ืคืื ืึท ืืึทืืื ืืขืจ ืกืืกืืขื. ืืึธืก ืืื ืขื ืืขื ืฆื ืื ืคึผืืึทืฅ ืืึธืืืึทื ืง ืืื Yandex.Metrica.
ืืึธ ืืขื ืขื ืขืืืขืืข ืกืืึทืืืกืืืง ืืืขืื ืื ื ืืฆื ืคืื ืื ืืึทืืึทืืืืก ืคึฟืึทืจ ืฆืืืื ืืืฉืื.
ื ืืืขืจ ืคืื ืจืขืงืึธืจืืก ืฆืขืืจืืื ืึทืจืึธืคึผ ืืืจื ืกืืกืืขืืขื ืืื ืืืืขืจ ืงืึทืืคึผืึธืื ืึทื ืฅ
SELECT
fld_app_name,
fld_app_module,
count(fld_app_name) AS rows_count
FROM log_web
GROUP BY
fld_app_name,
fld_app_module
WITH TOTALS
ORDER BY
fld_app_name ASC,
rows_count DESC
โโfld_app_nameโโโโโโฌโfld_app_moduleโโฌโrows_countโโ
โ site1.domain.ru โ web โ 131441 โ
โ site2.domain.ru โ web โ 1751081 โ
โ site3.domain.ru โ web โ 106887543 โ
โ site3.domain.ru โ svc โ 44908603 โ
โ site3.domain.ru โ intgr โ 9813911 โ
โ site4.domain.ru โ web โ 772095 โ
โ site5.domain.ru โ web โ 17037221 โ
โ site5.domain.ru โ intgr โ 838559 โ
โ site5.domain.ru โ bo โ 7404 โ
โ site6.domain.ru โ web โ 595877 โ
โ site7.domain.ru โ web โ 27778858 โ
โโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโ
Totals:
โโfld_app_nameโโฌโfld_app_moduleโโฌโrows_countโโ
โ โ โ 210522593 โ
โโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโ
11 rows in set. Elapsed: 4.874 sec. Processed 210.52 million rows, 421.67 MB (43.19 million rows/s., 86.51 MB/s.)ืื ืกืืืข ืคืื โโืืึทืื ืืืืฃ ืื ืืืกืง
SELECT
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
formatReadableSize(sum(data_compressed_bytes)) AS compressed,
sum(rows) AS total_rows
FROM system.parts
WHERE table = 'log_web'
โโuncompressedโโฌโcompressedโโฌโtotal_rowsโโ
โ 54.50 GiB โ 4.86 GiB โ 211427094 โ
โโโโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโโโ
1 rows in set. Elapsed: 0.035 sec.ืืจืึทื ืคืื ืืึทืื ืงืึทืืคึผืจืขืฉืึทื ืืื ืฉืคืืืื
SELECT
name,
formatReadableSize(data_uncompressed_bytes) AS uncompressed,
formatReadableSize(data_compressed_bytes) AS compressed,
data_uncompressed_bytes / data_compressed_bytes AS compress_ratio
FROM system.columns
WHERE table = 'log_web'
โโnameโโโโโโโโโโโโโโโโโโโโฌโuncompressedโโฌโcompressedโโฌโโโโโcompress_ratioโโ
โ logdate โ 401.53 MiB โ 1.80 MiB โ 223.16665968777315 โ
โ logdatetime โ 803.06 MiB โ 35.91 MiB โ 22.363966401202305 โ
โ fld_log_file_name โ 220.66 MiB โ 2.60 MiB โ 84.99905736932571 โ
โ fld_server_name โ 201.54 MiB โ 50.63 MiB โ 3.980924816977078 โ
โ fld_app_name โ 201.17 MiB โ 969.17 KiB โ 212.55518183686877 โ
โ fld_app_module โ 201.17 MiB โ 968.60 KiB โ 212.67805817411906 โ
โ fld_website_name โ 201.54 MiB โ 1.24 MiB โ 162.7204926761546 โ
โ serverIP โ 201.54 MiB โ 50.25 MiB โ 4.010824061219731 โ
โ method โ 201.53 MiB โ 43.64 MiB โ 4.617721053304486 โ
โ uriStem โ 5.13 GiB โ 832.51 MiB โ 6.311522291936919 โ
โ uriQuery โ 2.58 GiB โ 501.06 MiB โ 5.269731450124478 โ
โ port โ 803.06 MiB โ 3.98 MiB โ 201.91673864241824 โ
โ username โ 318.08 MiB โ 26.93 MiB โ 11.812513794583598 โ
โ clientIP โ 2.35 GiB โ 82.59 MiB โ 29.132328640073343 โ
โ clientRealIP โ 2.49 GiB โ 465.05 MiB โ 5.478382297052563 โ
โ userAgent โ 18.34 GiB โ 764.08 MiB โ 24.57905114484208 โ
โ referer โ 14.71 GiB โ 1.37 GiB โ 10.736792723669906 โ
โ response โ 803.06 MiB โ 83.81 MiB โ 9.582334090987247 โ
โ subresponse โ 399.87 MiB โ 1.83 MiB โ 218.4831068635027 โ
โ win32response โ 407.86 MiB โ 7.41 MiB โ 55.050315514606815 โ
โ timetaken โ 1.57 GiB โ 402.06 MiB โ 3.9947395692010637 โ
โ uriQuery__utm_medium โ 208.17 MiB โ 12.29 MiB โ 16.936148912472955 โ
โ uriQuery__utm_source โ 215.18 MiB โ 13.00 MiB โ 16.548367623199912 โ
โ uriQuery__utm_campaign โ 381.46 MiB โ 37.94 MiB โ 10.055156353418509 โ
โ uriQuery__utm_term โ 231.82 MiB โ 10.78 MiB โ 21.502540454070672 โ
โ uriQuery__utm_content โ 441.34 MiB โ 87.60 MiB โ 5.038260760449327 โ
โ uriQuery__yclid โ 216.88 MiB โ 16.58 MiB โ 13.07721335008116 โ
โ uriQuery__region โ 204.35 MiB โ 9.49 MiB โ 21.52661903446796 โ
โโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโโดโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโ
28 rows in set. Elapsed: 0.005 sec.ืืึทืฉืจืืึทืืื ื ืคืื ืืขื ืืฆื ืงืึทืืคึผืึธืื ืึทื ืฅ
FileBeat. ืืจืึทื ืกืคืขืจืื ื ืืขืงืข ืืึธืืก
ืืขืจ ืงืึธืืคึผืึธื ืขื ื ืืจืึทืงืก ืขื ืืขืจืื ืืขื ืฆื ืงืืึธืฅ ืืขืงืขืก ืืืืฃ ืืืกืง ืืื ืคึผืึทืกืื ืื ืืื ืคึฟืึธืจืืึทืฆืืข ืฆื LogStash. ืืื ืกืืึทืืืจื ืืืืฃ ืึทืืข ืกืขืจืืืขืจืก ืืื ืงืืึธืฅ ืืขืงืขืก ืืขื ืขื ืืขืฉืจืืื (ืืืืฉืึทืืืึทืื IIS). ืึทืจืืขื ืืื ืขืง ืืึธืืข (ื"ื ืืจืึทื ืกืคืขืจืก ืืืืื ืื ืฆืืืขืืืืื ืจืขืงืึธืจืืก ืฆื ืืขืจ ืืขืงืข). ืึธืืขืจ ืกืขืคึผืขืจืึทืืื ืขืก ืงืขื ืขื ืืืื ืงืึทื ืคืืืืขืจื ืฆื ืึทืจืืืขืจืคืืจื ืืึทื ืฅ ืืขืงืขืก. ืืึธืก ืืื ื ืืฆืืง ืืืขื ืืืจ ืืึทืจืคึฟื ืฆื ืึธืคึผืืึธืืืจื ืืึทืื ืคืื ืื ืคืจืืขืจืืืงืข ืืืฉืื. ื ืึธืจ ืฉืืขืื ืื ืงืืึธืฅ ืืขืงืข ืืื ืึท ืืขืงืข ืืื ืขืก ืืืขื ืืืืขื ืขื ืขืก ืืื ืืืื ืื ืืืืขืจืืึทื ื.
ืืืขื ืื ืกืขืจืืืืก ืืื ืกืืึทืคึผื, ืื ืืึทืื ืืขื ืขื ื ืื ืืขืจ ืืจืึทื ืกืคืขืจื ืฆื ืื ืกืืึธืจืืืืฉ.
ื ืืืืฉืคึผืื ืงืึทื ืคืืืืขืจืืืฉืึทื ืงืืงื ืืื ืืึธืก:
filebeat.yml
filebeat.inputs:
- type: log
enabled: true
paths:
- C:/inetpub/logs/LogFiles/W3SVC1/*.log
exclude_files: ['.gz$','.zip$']
tail_files: true
ignore_older: 24h
fields:
fld_server_name: "site1.domain.ru"
fld_app_name: "site1.domain.ru"
fld_app_module: "web"
fld_website_name: "web-main"
- type: log
enabled: true
paths:
- C:/inetpub/logs/LogFiles/__Import/access_log-*
exclude_files: ['.gz$','.zip$']
tail_files: false
fields:
fld_server_name: "site2.domain.ru"
fld_app_name: "site2.domain.ru"
fld_app_module: "web"
fld_website_name: "web-main"
fld_logformat: "logformat__apache"
filebeat.config.modules:
path: ${path.config}/modules.d/*.yml
reload.enabled: false
reload.period: 2s
output.logstash:
hosts: ["log.domain.com:5044"]
ssl.enabled: true
ssl.certificate_authorities: ["C:/filebeat/certs/ca.pem", "C:/filebeat/certs/ca-issuing.pem"]
ssl.certificate: "C:/filebeat/certs/site1.domain.ru.cer"
ssl.key: "C:/filebeat/certs/site1.domain.ru.key"
#================================ Processors =====================================
processors:
- add_host_metadata: ~
- add_cloud_metadata: ~logstash. ืงืืึธืฅ ืงืึทืืขืงืืขืจ
ืืขืจ ืงืึธืืคึผืึธื ืขื ื ืืื ืืืืืื ื ืฆื ืืึทืงืืืขื ืงืืึธืฅ ืืืื ืกื ืคึฟืื FileBeat (ืึธืืขืจ ืืืจื ืื RabbitMQ ืจืื), ืคึผืึทืจืกืื ื ืืื ืื ืกืขืจืืื ื ืืึทืืฉืึทื ืืื ืื ืงืืืงืืึธืืกืข ืืึทืืึทืืืืก.
ืคึฟืึทืจ ืื ืกืขืจืฉืึทื ืืื ClickHouse, ืื Logstash-output-clickhouse ืคึผืืืืื ืืื ืืขื ืืฆื. ืื Logstash ืคึผืืืืื ืืื ืึท ืืขืงืึทื ืืืึทื ืคืื ืืขืื ืืืืืขืจ, ืึธืืขืจ ืืื ืึท ืจืขืืืืขืจ ืฉืึทืืืึทืื, ืขืก ืืื ืืขืกืขืจ ืฆื ืืึทืืื ืื ืืื ืกื ืืื. ืืืขื ืกืืึทืคึผื, ืึทืจืืืงืืขื ืืืขื ืืืื ืึทืงืืืืืึทืืืืืื ืืื ืื RabbitMQ ืจืื, ืึทืืื ืืืื ืื ืืึทืืื ืืื ืคึฟืึทืจ ืึท ืืึทื ื ืฆืืึทื, ืขืก ืืื ืืขืกืขืจ ืฆื ืืึทืืื Filebeats ืืืืฃ ืื ืกืขืจืืืขืจืก. ืืื ืึท ืกืืขืืข ืืื RabbitMQ ืืื ื ืืฉื ืืขื ืืฆื (ืืืืฃ ืื ืืืืข ื ืขืฅ, Filebeat ืืืืื ืกืขื ืื ืืึธืืก ืฆื Logstash), Filebeats ืึทืจืืขื ืืึทื ืฅ ืคึผืึทืกืืง ืืื ืกืืงืืืจืื, ืึทืืื ืคึฟืึทืจ ืืื ืื ืึทื ืึทืืืืืืึทืืืืืื ืคืื ืคึผืจืึธืืืงืฆืืข ืคึผืึทืกืื ืึธื ืงืึทื ืกืึทืงืืืขื ืกืึทื.
ื ืืืืฉืคึผืื ืงืึทื ืคืืืืขืจืืืฉืึทื ืงืืงื ืืื ืืึธืก:
log_web__filebeat_clickhouse.conf
input {
beats {
port => 5044
type => 'iis'
ssl => true
ssl_certificate_authorities => ["/etc/logstash/certs/ca.cer", "/etc/logstash/certs/ca-issuing.cer"]
ssl_certificate => "/etc/logstash/certs/server.cer"
ssl_key => "/etc/logstash/certs/server-pkcs8.key"
ssl_verify_mode => "peer"
add_field => {
"fld_server_name" => "%{[fields][fld_server_name]}"
"fld_app_name" => "%{[fields][fld_app_name]}"
"fld_app_module" => "%{[fields][fld_app_module]}"
"fld_website_name" => "%{[fields][fld_website_name]}"
"fld_log_file_name" => "%{source}"
"fld_logformat" => "%{[fields][fld_logformat]}"
}
}
rabbitmq {
host => "queue.domain.com"
port => 5671
user => "q-reader"
password => "password"
queue => "web_log"
heartbeat => 30
durable => true
ssl => true
#ssl_certificate_path => "/etc/logstash/certs/server.p12"
#ssl_certificate_password => "password"
add_field => {
"fld_server_name" => "%{[fields][fld_server_name]}"
"fld_app_name" => "%{[fields][fld_app_name]}"
"fld_app_module" => "%{[fields][fld_app_module]}"
"fld_website_name" => "%{[fields][fld_website_name]}"
"fld_log_file_name" => "%{source}"
"fld_logformat" => "%{[fields][fld_logformat]}"
}
}
}
filter {
if [message] =~ "^#" {
drop {}
}
if [fld_logformat] == "logformat__iis_with_xrealip" {
grok {
match => ["message", "%{TIMESTAMP_ISO8601:log_timestamp} %{IP:serverIP} %{WORD:method} %{NOTSPACE:uriStem} %{NOTSPACE:uriQuery} %{NUMBER:port} %{NOTSPACE:username} %{IPORHOST:clientIP} %{NOTSPACE:userAgent} %{NOTSPACE:referer} %{NUMBER:response} %{NUMBER:subresponse} %{NUMBER:win32response} %{NUMBER:timetaken} %{NOTSPACE:xrealIP} %{NOTSPACE:xforwarderfor}"]
}
} else {
grok {
match => ["message", "%{TIMESTAMP_ISO8601:log_timestamp} %{IP:serverIP} %{WORD:method} %{NOTSPACE:uriStem} %{NOTSPACE:uriQuery} %{NUMBER:port} %{NOTSPACE:username} %{IPORHOST:clientIP} %{NOTSPACE:userAgent} %{NOTSPACE:referer} %{NUMBER:response} %{NUMBER:subresponse} %{NUMBER:win32response} %{NUMBER:timetaken}"]
}
}
date {
match => [ "log_timestamp", "YYYY-MM-dd HH:mm:ss" ]
timezone => "Etc/UTC"
remove_field => [ "log_timestamp", "@timestamp" ]
target => [ "log_timestamp2" ]
}
ruby {
code => "tstamp = event.get('log_timestamp2').to_i
event.set('logdatetime', Time.at(tstamp).strftime('%Y-%m-%d %H:%M:%S'))
event.set('logdate', Time.at(tstamp).strftime('%Y-%m-%d'))"
}
if [bytesSent] {
ruby {
code => "event['kilobytesSent'] = event['bytesSent'].to_i / 1024.0"
}
}
if [bytesReceived] {
ruby {
code => "event['kilobytesReceived'] = event['bytesReceived'].to_i / 1024.0"
}
}
ruby {
code => "event.set('clientRealIP', event.get('clientIP'))"
}
if [xrealIP] {
ruby {
code => "event.set('clientRealIP', event.get('xrealIP'))"
}
}
if [xforwarderfor] {
ruby {
code => "event.set('clientRealIP', event.get('xforwarderfor'))"
}
}
mutate {
convert => ["bytesSent", "integer"]
convert => ["bytesReceived", "integer"]
convert => ["timetaken", "integer"]
convert => ["port", "integer"]
add_field => {
"clientHostname" => "%{clientIP}"
}
}
useragent {
source=> "useragent"
prefix=> "browser"
}
kv {
source => "uriQuery"
prefix => "uriQuery__"
allow_duplicate_values => false
field_split => "&"
include_keys => [ "utm_medium", "utm_source", "utm_campaign", "utm_term", "utm_content", "yclid", "region" ]
}
mutate {
join => { "uriQuery__utm_source" => "," }
join => { "uriQuery__utm_medium" => "," }
join => { "uriQuery__utm_campaign" => "," }
join => { "uriQuery__utm_term" => "," }
join => { "uriQuery__utm_content" => "," }
join => { "uriQuery__yclid" => "," }
join => { "uriQuery__region" => "," }
}
}
output {
#stdout {codec => rubydebug}
clickhouse {
headers => ["Authorization", "Basic abcdsfks..."]
http_hosts => ["http://127.0.0.1:8123"]
save_dir => "/etc/logstash/tmp"
table => "log_web"
request_tolerance => 1
flush_size => 10000
idle_flush_time => 1
mutations => {
"fld_log_file_name" => "fld_log_file_name"
"fld_server_name" => "fld_server_name"
"fld_app_name" => "fld_app_name"
"fld_app_module" => "fld_app_module"
"fld_website_name" => "fld_website_name"
"logdatetime" => "logdatetime"
"logdate" => "logdate"
"serverIP" => "serverIP"
"method" => "method"
"uriStem" => "uriStem"
"uriQuery" => "uriQuery"
"port" => "port"
"username" => "username"
"clientIP" => "clientIP"
"clientRealIP" => "clientRealIP"
"userAgent" => "userAgent"
"referer" => "referer"
"response" => "response"
"subresponse" => "subresponse"
"win32response" => "win32response"
"timetaken" => "timetaken"
"uriQuery__utm_medium" => "uriQuery__utm_medium"
"uriQuery__utm_source" => "uriQuery__utm_source"
"uriQuery__utm_campaign" => "uriQuery__utm_campaign"
"uriQuery__utm_term" => "uriQuery__utm_term"
"uriQuery__utm_content" => "uriQuery__utm_content"
"uriQuery__yclid" => "uriQuery__yclid"
"uriQuery__region" => "uriQuery__region"
}
}
}pipelines.yml
# This file is where you define your pipelines. You can define multiple.
# For more information on multiple pipelines, see the documentation:
# https://www.elastic.co/guide/en/logstash/current/multiple-pipelines.html
- pipeline.id: log_web__filebeat_clickhouse
path.config: "/etc/logstash/log_web__filebeat_clickhouse.conf"ืงืืืงืืึธืืกืข. ืงืืึธืฅ ืกืืึธืจืืืืฉ
ืืึธืืก ืคึฟืึทืจ ืึทืืข ืกืืกืืขืืขื ืืขื ืขื ืกืืึธืจื ืืื ืืืื ืืืฉ (ืืขื ืืื ืื ืึธื ืืืื ืคืื ืืขื ืึทืจืืืงื). ืขืก ืืื ืืืขื ืฆื ืงืจืึธื ืืื ืคึฟืึธืจืืึทืฆืืข ืืืขืื ืจืืงืืืขืก: ืึทืืข ืคึผืึทืจืึทืืขืืขืจืก ืืขื ืขื ืขื ืืขื ืคึฟืึทืจ ืคืึทืจืฉืืืขื ืข ืคึฟืึธืจืืึทืืืจืื ืืขื, ืึทืืึท ืืื IIS ืืึธืืก, ืึทืคึผืึทืืฉื ืืื ื ืืื ืงืก ืืึธืืก. ืคึฟืึทืจ ืึทืคึผืืึทืงืืืฉืึทื ืืึธืืก, ืืื ืืืึธืก, ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืขืจืจืึธืจืก, ืืื ืคึฟืึธืจืืึทืฆืืข ืึทืจืืืงืืขื, ืืืึธืจื ืื ืื ืืขื ืขื ืจืขืงืึธืจืืขื, ืึท ืืึทืืื ืืขืจ ืืืฉ ืืืขื ืืืื ืฆืืืขืฉืืขืื ืืื ืื ืฆืื ืขืืขื ืกืืจืืงืืืจ (ืืขืจืืืืึทื ืืื ืื ืคึผืืึทื ืืื ืข).
ืืืขื ืืืืืื ืื ื ืึท ืืืฉ, ืขืก ืืื ืืืืขืจ ืืืืืืืง ืฆื ืืึทืฉืืืกื ืืืืฃ ืื ืขืจืฉืืืง ืฉืืืกื (ืืืจื ืืืึธืก ืื ืืึทืื ืืืขื ืืืื ืืืืกืืขืฉืืขืื ืืขืฉืึทืก ืกืืึธืจืืืืฉ). ืืขืจ ืืจืึทื ืคืื ืืึทืื ืงืึทืืคึผืจืขืฉืึทื ืืื ืึธื ืคึฟืจืขื ืืืืงืืึทื ืึธืคืขื ืืขื ืืืืฃ ืืขื. ืืื ืืื ืืืขืจ ืืืึทืฉืคึผืื, ืืขืจ ืฉืืืกื ืืื
ืกืืจ ืืืื (fld_app_name, fld_app_module, logdatetime)
ืึทื ืืื, ืืืจื ืื ื ืึธืืขื ืคืื ืื ืกืืกืืขื, ืื ื ืึธืืขื ืคืื ืื ืกืืกืืขื ืงืึธืืคึผืึธื ืขื ื ืืื ืื ืืึธื ืคืื ืื ืืขืฉืขืขื ืืฉ. ืืืืืขืก, ืืขืจ ืืึธื ืคืื ืื ืืขืฉืขืขื ืืฉ ืืขืงืืืขื ืขืจืฉืืขืจ. ื ืึธื ืืึธืืืื ื ืขืก ืฆื ืื ืืขืฆืืข ืึธืจื, ืื ืคึฟืจืืื ืื ืืขืืืืื ืฆื ืึทืจืืขืื ืฆืืืื ืืึธื ืึทืืื ืฉื ืขื. ืืฉืึทื ืืื ื ืื ืขืจืฉืืืง ืฉืืืกื ืืืขื ืืึทืจืคื ืจืืงืจืืืืืื ื ืื ืืืฉ ืืื ืจืืืึธืืืื ื ืื ืืึทืื ืึทืืื ืึทื ClickHouse ืฉืืึทืขื-ืกืึธืจืฅ ืื ืืึทืื ืืืืฃ ืืืกืง. ืืึธืก ืืื ืึท ืฉืืืขืจ ืึธืคึผืขืจืึทืฆืืข, ืึทืืื ืขืก ืืื ืึท ืืืืข ืืขืืึทื ืง ืฆื ืืจืึทืืื ืคืื ืืืขืื ืืืึธืก ืืึธื ืืืื ืึทืจืืึทื ืืขืจืขืื ื ืืื ืื ืกืึธืจื ืฉืืืกื.
ืขืก ืืึธื ืืืื ืืืื ืืืืขืจืงื ืึทื ืื ืืึทืื ืืืคึผ ืคืื LowCardinality ืืื ืืขืืืขื ืืขืคืืขืจืขื ืืื ืื ืืขืฆืืข ืืืขืจืกืืขืก. ืืืขื ืืืจ ื ืืฆื ืขืก, ืื ืืจืืืก ืคืื ืงืึทืืคึผืจืขืกื ืืึทืื ืืื ืืจืึทืกืืืงืื ืจืืืืกื ืคึฟืึทืจ ืื ืคืขืืืขืจ ืืื ื ืืืขืจืืง ืงืึทืจืืื ืึทืืืื (ืืืืื ืืง ืึธืคึผืฆืืขืก).
ืืืขืจืกืืข 19.6 ืืื ืืขืจืืืืึทื ืืื ื ืืฆื ืืื ืืืจ ืคึผืืึทื ืืจื ืฆื ืคึผืจืืืืจื ืึทืคึผืืืืืื ื ืฆื ืื ืืขืฆืืข ืืืขืจืกืืข. ืืื ืืึธืื ืึทืืึท ืืืื ืืขืจืืขื ืคึฟืขืึดืงืืืื ืืื ืึทืืึทืคึผืืืื ืืจืึทื ืืืึทืจืืื, ืกืงืืคึผืื ื ืื ืืืกืื ืืื ืื DoubleDelta ืงืึธืืขืง, ืคึฟืึทืจ ืืืึทืฉืคึผืื.
ืืืจื ืคืขืืืงืืึทื, ืืขืฉืึทืก ืื ืกืืึทืืืจืื ื, ืื ืืึธืืื ื ืืืจืื ืืื ืืึทืฉืืืื ืฆื ืฉืคึผืืจ. ืื ืืึธืืก ืืขื ืขื ืจืึธืืืืืืื ืืื ืึทืจืืฉืืืืขื, ืึธืืขืจ ืืื ืืขืจ ืืขืืืืงืขืจ ืฆืืื ืืื ืืงืกืคึผืึทื ื ืึทืจืืืฃ ืฆื ืึท ืืืืืืืื. ืืืื ืขืก ืืื ื ืื ื ืืืืืง, ืืืจ ืงืขื ืขื ืฉืืขืื ืื ืืืืจืขื ืื ื ืืืจืื, ืื ืืจืืืก ืคืื ืืขื ืงืืึธืฅ ืืื ืืจืึทืกืืืงืื ืจืืืืกื. ืื ืืึธืืื ื ืืึทืฉืืขืืืงื ืืื ืืึทืฉืืืื ืืื ืื config.xml ืืขืงืข:
<!-- Possible levels: https://github.com/pocoproject/poco/blob/develop/Foundation/include/Poco/Logger. h#L105 -->
<level>warning</level>ืขืืืขืืข ื ืืฆืืง ืงืึทืืึทื ืื
ะะพัะบะพะปัะบั ะพัะธะณะธะฝะฐะปัะฝัะต ะฟะฐะบะตัั ัััะฐะฝะพะฒะบะธ ัะพะฑะธัะฐัััั ะฟะพ Debian, ัะพ ะดะปั ะดััะณะธั
ะฒะตััะธะน Linux ะฝะตะพะฑั
ะพะดะธะผะพ ะธัะฟะพะปัะทะพะฒะฐัั ะฟะฐะบะตัั ัะพะฑัะฐะฝะฝัะต ะบะพะผะฟะฐะฝะธะตะน Altinity.
ะะพั ะฟะพ ััะพะน ัััะปะบะต ะตััั ะธะฝััััะบัะธะธ ั ัััะปะบะฐะผะธ ะฝะฐ ะธั
ัะตะฟะพะทะธัะพัะธะน: https://www.altinity.com/blog/2017/12/18/logstash-with-clickhouse
sudo yum search clickhouse-server
sudo yum install clickhouse-server.noarch
1. ะฟัะพะฒะตัะบะฐ ััะฐัััะฐ
sudo systemctl status clickhouse-server
2. ะพััะฐะฝะพะฒะบะฐ ัะตัะฒะตัะฐ
sudo systemctl stop clickhouse-server
3. ะทะฐะฟััะบ ัะตัะฒะตัะฐ
sudo systemctl start clickhouse-server
ะะฐะฟััะบ ะดะปั ะฒัะฟะพะปะฝะตะฝะธั ะทะฐะฟัะพัะพะฒ ะฒ ะผะฝะพะณะพัััะพัะฝะพะผ ัะตะถะธะผะต (ะฒัะฟะพะปะฝะตะฝะธะต ะฟะพัะปะต ะทะฝะฐะบะฐ ";")
clickhouse-client --multiline
clickhouse-client --multiline --host 127.0.0.1 --password pa55w0rd
clickhouse-client --multiline --host 127.0.0.1 --port 9440 --secure --user default --password pa55w0rd
ะะปะฐะณะธะฝ ะบะปะธะบะปะฐัะทะฐ ะดะปั ะปะพะณััะตั ะฒ ัะปััะฐะต ะพัะธะฑะบะธ ะฒ ะพะดะฝะพะน ัััะพะบะต ัะพั
ัะฐะฝัะตั ะฒัั ะฟะฐัะบั ะฒ ัะฐะนะป /tmp/log_web_failed.json
ะะพะถะฝะพ ะฒัััะฝัั ะธัะฟัะฐะฒะธัั ััะพั ัะฐะนะป ะธ ะฟะพะฟัะพะฑะพะฒะฐัั ะทะฐะปะธัั ะตะณะพ ะฒ ะะ ะฒัััะฝัั:
clickhouse-client --host 127.0.0.1 --password password --query="INSERT INTO log_web FORMAT JSONEachRow" < /tmp/log_web_failed__fixed.json
sudo mv /etc/logstash/tmp/log_web_failed.json /etc/logstash/tmp/log_web_failed__fixed.json
sudo chown user_dev /etc/logstash/tmp/log_web_failed__fixed.json
sudo clickhouse-client --host 127.0.0.1 --password password --query="INSERT INTO log_web FORMAT JSONEachRow" < /etc/logstash/tmp/log_web_failed__fixed.json
sudo mv /etc/logstash/tmp/log_web_failed__fixed.json /etc/logstash/tmp/log_web_failed__fixed_.json
ะฒัั
ะพะด ะธะท ะบะพะผะฐะฝะดะฝะพะน ัััะพะบะธ
quit;
## ะะฐัััะพะนะบะฐ TLS
https://www.altinity.com/blog/2019/3/5/clickhouse-networking-part-2
openssl s_client -connect log.domain.com:9440 < /dev/nulllogstash. ืงืืึธืฅ ืจืึทืืืขืจ ืคึฟืื FileBeat ืฆื RabbitMQ ืจืื
ืืขืจ ืงืึธืืคึผืึธื ืขื ื ืืื ืืขื ืืฆื ืฆื ืืึทืจืฉืจืื ืืึธืืก ืคึฟืื FileBeat ืฆื ืื RabbitMQ ืจืื. ืืึธ ืืขื ืขื ืฆืืืื ืคืื ืงืื:
- ืฆืื ืืึทืืืืขืจื, FileBeat ืืื ื ืืฉื ืึท ืจืขืืืืืึทื ืคึผืืืืื ืฆื ืฉืจืืึทืื ืืืืึทื ืฆื RabbitMQ. ืืื ืึทืืึท ืคืึทื ืืงืฉืึทื ืึทืืืื, ืืืื ืืฉืคืื ืืืื ืื ืึทืจืืืกืืขืื ืืืืฃ ืืืืขืจ ืืืืืื, ืืื ื ืืฉื ืคึผืืึทื ื ืขื ืคึฟืึทืจ ืืืคึผืืึทืืขื ืืืืฉืึทื. ืขืก ืืื ืึท ืคึผืืืืื ืคึฟืึทืจ Kafka, ืึธืืขืจ ืคึฟืึทืจ ืขืืืขืืข ืกืืื ืืืจ ืงืขื ืขื ื ืืฉื ื ืืฆื ืขืก ืืื ืฉืืื.
- ืขืก ืืขื ืขื ืจืขืงืืืืจืขืืขื ืฅ ืคึฟืึทืจ ืงืึทืืขืงืืื ื ืืึธืืก ืืื ืื DMZ. ืืึทืืืจื ืืืืฃ ืืื, ืื ืืึธืืก ืืืื ืขืจืฉืืขืจ ืืืื ืืืกืืฃ ืฆื ืื ืจืื ืืื ืืึทื LogStash ืืืืขื ื ืื ืืืื ืกื ืคึฟืื ืืขืจ ืจืื ืคึฟืื ืึทืจืืืก.
ืืขืจืืืขืจ, ืขืก ืืื ืคึฟืึทืจ ืื ืคืึทื ืืื ืกืขืจืืืขืจืก ืืขื ืขื ืืืื ืืื ืื DMZ ืึทื ืืขื ืืึทืจืฃ ื ืืฆื ืึทืืึท ืึท ืืืกื ืงืึธืืคึผืืืฆืืจื ืกืืขืืข. ื ืืืืฉืคึผืื ืงืึทื ืคืืืืขืจืืืฉืึทื ืงืืงื ืืื ืืึธืก:
iis_w3c_logs__filebeat_rabbitmq.conf
input {
beats {
port => 5044
type => 'iis'
ssl => true
ssl_certificate_authorities => ["/etc/pki/tls/certs/app/ca.pem", "/etc/pki/tls/certs/app/ca-issuing.pem"]
ssl_certificate => "/etc/pki/tls/certs/app/queue.domain.com.cer"
ssl_key => "/etc/pki/tls/certs/app/queue.domain.com-pkcs8.key"
ssl_verify_mode => "peer"
}
}
output {
#stdout {codec => rubydebug}
rabbitmq {
host => "127.0.0.1"
port => 5672
exchange => "monitor.direct"
exchange_type => "direct"
key => "%{[fields][fld_app_name]}"
user => "q-writer"
password => "password"
ssl => false
}
}RabbitMQ. ืึธื ืืึธื ืจืื
ืืขืจ ืงืึธืืคึผืึธื ืขื ื ืืื ืืขื ืืฆื ืฆื ืืึทืคืขืจ ืงืืึธืฅ ืืืื ืกื ืืื ืื DMZ. ืจืขืงืึธืจืืื ื ืืื ืืืจืืืขืงืึธืื ืืืจื ืึท ืืื ืื ืคืื Filebeat โ LogStash. ืืืืขื ืขื ืืื ืืืจืืืขืงืึธืื ืคึฟืื ืึทืจืืืก ืื DMZ ืืืจื LogStash. ืืืขื ืึทืคึผืขืจืืืืื ื ืืืจื RabboitMQ, ืืืขืื 4 ืืืืื ื ืึทืจืืืงืืขื ืคึผืขืจ ืกืขืงืื ืืข ืืขื ืขื ืคึผืจืึทืกืขืกื.
ืึธื ืืึธื ืจืืืื ื ืืื ืงืึทื ืคืืืืขืจื ืืืจื ืกืืกืืขื ื ืึธืืขื, ื"ื ืืืืืจื ืืืืฃ FileBeat ืงืึทื ืคืืืืขืจืืืฉืึทื ืืึทืื. ืึทืืข ืึทืจืืืงืืขื ืืืื ืฆื ืืืื ืจืื. ืืืื ืคึฟืึทืจ ืขืืืขืืข ืกืืื ืื ืจืื ืืื ืกื ืืื ืกืืึทืคึผื, ืืึธืก ืืืขื ื ืืฉื ืคืืจื ืฆื ืื ืึธื ืืืขืจ ืคืื ืึทืจืืืงืืขื: FileBeats ืืืขื ืืึทืงืืืขื ืงืฉืจ ืขืจืจืึธืจืก ืืื ืืขืืคึผืขืจืขืจืึทืื ืืคืืขื ืืขื ืฉืืงื. ืืื LogStash ืืืึธืก ืืืืขื ื ืคืื ืื ืจืื ืืืขื ืืืื ืืึทืงืืืขื ื ืขืฅ ืขืจืจืึธืจืก ืืื ืืืึทืจืื ืคึฟืึทืจ ืื ืงืฉืจ ืฆื ืืืื ืืขืืื ื. ืืื ืืขื ืคืึทื, ืื ืืึทืื, ืคืื ืงืืจืก, ืืืขื ื ืื ืืขืจ ืืืื ืืขืฉืจืืื ืฆื ืื ืืึทืืึทืืืืก.
ืื ืคืืืืขื ืืข ืื ืกืืจืึทืงืฉืึทื ื ืืขื ืขื ืืขื ืืฆื ืฆื ืฉืึทืคึฟื ืืื ืงืึทื ืคืืืืขืจ ืงืืื:
sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin declare exchange --vhost=/ name=monitor.direct type=direct sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin declare queue --vhost=/ name=web_log durable=true
sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin --vhost="/" declare binding source="monitor.direct" destination_type="queue" destination="web_log" routing_key="site1.domain.ru"
sudo /usr/local/bin/rabbitmqadmin/rabbitmqadmin --vhost="/" declare binding source="monitor.direct" destination_type="queue" destination="web_log" routing_key="site2.domain.ru"ืืจืืคืื ื. ืืึทืฉืืึธืจืื
ืืขืจ ืงืึธืืคึผืึธื ืขื ื ืืื ืืขื ืืฆื ืฆื ืืืืืฉืืืึทืืืื ืืึธื ืืืึธืจืื ื ืืึทืื. ืืื ืืขื ืคืึทื, ืืืจ ืืึทืจืคึฟื ืฆื ืื ืกืืึทืืืจื ืื ClickHouse ืืึทืืึทืกืึธืืจืกืข ืคึฟืึทืจ Grafana 4.6+ ืคึผืืืืื. ืืืจ ืืึธืื ืฆื ืืืืืง ืขืก ืึท ืืืกื ืฆื ืคึฟืึทืจืืขืกืขืจื ืื ืขืคืขืงืืืืืงืืึทื ืคืื ืคึผืจืึทืกืขืกืื ื ืกืงื ืคืืืืขืจืก ืืืืฃ ืื ืืึทืฉืืึธืจื.
ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืืืจ ื ืืฆื ืืืขืจืืึทืืึทืื, ืืื ืืืื ืืื ืืขื ืขื ื ืืฉื ืฉืืขืื ืืื ืื ืคืืืืขืจ ืคืขืื, ืืืจ ืืืึธืื ืืื ืึทื ืขืก ืืึธื ื ืืฉื ืืืฉืขื ืขืจืืื ืึท ืฆืืฉืืึทื ื ืืื ืื WHERE ืคืื ืื ืคืึธืจืขื ( uriStem = ยป AND uriStem != ยป ). ืืื ืืขื ืคืึทื, ClickHouse ืืืขื ืืืืขื ืขื ืื uriStem ืืืึทื. ืืื ืึทืืืขืืืื, ืืืจ ืืขืคืจืืืื ืคืึทืจืฉืืืขื ืข ืึธืคึผืฆืืขืก ืืื ืืืืขื ืืฉืึทืืืึทืื ืงืขืจืขืงืืึทื ืื ืคึผืืืืื (ืื $valueIfEmpty ืืึทืงืจืึธื) ืึทืืื ืึทื ืืื ืืขื ืคืึทื ืคืื ืึท ืืืืืืง ืืืขืจื ืขืก ืงืขืจื 1, ืึธื ืืขืจืืึธื ืขื ืื ืืืึทื ืืื.
ืืื ืืืฆื ืืืจ ืงืขื ืขื ื ืืฆื ืืขื ืึธื ืคึฟืจืขื ืคึฟืึทืจ ืื ืืจืึทืคืืง
$columns(response, count(*) c) from $table where $adhoc
and $valueIfEmpty($fld_app_name, 1, fld_app_name = '$fld_app_name')
and $valueIfEmpty($fld_app_module, 1, fld_app_module = '$fld_app_module') and $valueIfEmpty($fld_server_name, 1, fld_server_name = '$fld_server_name') and $valueIfEmpty($uriStem, 1, uriStem like '%$uriStem%')
and $valueIfEmpty($clientRealIP, 1, clientRealIP = '$clientRealIP')ืืืึธืก ืืจืึทื ืืืืืฅ โโโโืฆื ืืขื SQL (ืืึธื ืึทื ืื ืืืืืืง uriStem ืคืขืืืขืจ ืืขื ืขื ืงืึธื ืืืขืจืืขื ืฆื ืืืืื 1)
SELECT
t,
groupArray((response, c)) AS groupArr
FROM (
SELECT
(intDiv(toUInt32(logdatetime), 60) * 60) * 1000 AS t, response,
count(*) AS c FROM default.log_web
WHERE (logdate >= toDate(1565061982)) AND (logdatetime >= toDateTime(1565061982)) AND 1 AND (fld_app_name = 'site1.domain.ru') AND (fld_app_module = 'web') AND 1 AND 1 AND 1
GROUP BY
t, response
ORDER BY
t ASC,
response ASC
)
GROUP BY t ORDER BY t ASCืกืึธืฃ
ืืขืจ ืืืืกืืขื ืคืื ืื ClickHouse ืืึทืืึทืืืืก ืืื ืืขืืืืจื ืึท ืืึทื ืืืึทืจืง ืืขืฉืขืขื ืืฉ ืืื ืื ืืึทืจืง. ืขืก ืืื ืืขืืืขื ืฉืืืขืจ ืฆื ืืืึทืืืฉืึทื ืึทื ืืืจ, ืืึธืจ ืคืจืื, ืืื ืึท ืจืขืืข ืืขื ืขื ืึทืจืื ืืื ืึท ืฉืืึทืจืง ืืื ืคึผืจืึทืงืืืฉ ืืขืฆืืึทื ืคึฟืึทืจ ืืจืืขืื ืืื ืืจืืืก ืืึทืื. ืคืื ืงืืจืก, ืืื ืื ืงืจืืกืื ื ืืืืขืจืคืขื ืืฉื (ืืืฉื, ืฉืึทืจืืื ื ืืื ืจืขืคึผืืึทืงืืืฉืึทื ืฆื ืงืืืคื ืกืขืจืืืขืจืก), ืื ืกืืขืืข ืืืขื ืืืขืจื ืืขืจ ืงืึธืืคึผืืืฆืืจื. ืึธืืขืจ ืืืืฃ ืขืจืฉืืขืจ ืืืคึผืจืขืกืกืืึธื ืก, ืืจืืขืื ืืื ืืขื ืืึทืืึทืืืืก ืืื ืืืืขืจ ืึธื ืืขื ืขื. ืขืก ืงืขื ืขื ืืืื ืืขืืขื ืึทื ืื ืคึผืจืึธืืืงื ืืื ืืขืืืื "ืคึฟืึทืจ ืืขื ืืฉื."
ืงืึทืืคึผืขืจื ืืื ElasticSearch, ืื ืงืึธืก ืคืื ืกืืึธืจืื ื ืืื ืคึผืจืึทืกืขืกืื ื ืืึธืืก ืืื ืขืกืืืืึทืืขื ืฆื ืืืื ืจืืืืกื ืืื ืคืื ืฃ ืฆื ืฆืขื ืืื. ืืื ืื ืืขืจืข ืืืขืจืืขืจ, ืืืื ืืืจ ืืืึธืื ืืึธืื ืฆื ืฉืืขืื ืึทืจืืืฃ ืึท ืงื ืืื ืคืื ืขืืืขืืข ืืืฉืื ืขื ืคึฟืึทืจ ืื ืงืจืึทื ื ืกืืืข ืคืื โโโโืืึทืื, ืืืขื ื ืืฆื ClickHouse, ืืืื ื ืืืขืจืืง-ืืึทืื ืืึทืฉืื ืืื ืืขื ืื ืคึฟืึทืจ ืืื ืื. ืืึธ, ืคืื ืงืืจืก, ElasticSearch ืืืื ืืื ืืืืฃ-ืืืกืง ืืึทืื ืงืึทืืคึผืจืขืฉืึทื ืืขืงืึทื ืืืึทืื ืืื ืื ืืขืจืข ืคึฟืขืึดืงืืืื ืืืึธืก ืงืขื ืขื ืืืืืืืืง ืจืขืืืฆืืจื ืื ืจืืกืึธืจืก ืงืึทื ืกืึทืืฉืึทื, ืึธืืขืจ ืงืึทืืคึผืขืจื ืืื ClickHouse, ืืึธืก ืืืขื ืืืื ืืขืจ ืืืึทืขืจ.
ืึธื ืงืืื ืกืคึผืขืฆืืขื ืึธืคึผืืืืืืึทืืืึธื ืก ืคืื ืืื ืืืขืจ ืืืื, ืืืืฃ ืคืขืืืงืืึทื ืกืขืืืื ืืก, ืืึธืืืื ื ืืึทืื ืืื ืกืึทืืขืงืืื ื ืคืื ืื ืืึทืืึทืืืืก ืึทืจืืขื ืืื ืึทื ืึทืืืืืื ื ืืืืงืืึทื. ืืืจ ืืึธื ื ืื ืืึธืื ืคืื ืืึทืื ื ืึธื (ืืืขืื 200 ืืืืืึธื ืจืขืงืึธืจืืก), ืึธืืขืจ ืืขืจ ืกืขืจืืืขืจ ืืื ืืื ืฉืืืึทื. ืืืจ ืงืขื ืขื ื ืืฆื ืืขื ืืขืฆืืึทื ืืื ืืขืจ ืฆืืงืื ืคึฟื ืคึฟืึทืจ ืื ืืขืจืข ืฆืืืขืงื ื ืื ืฉืืึทืืืช ืฆื ืกืืึธืจืื ื ืืึธืืก. ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืคึฟืึทืจ ืกืืฃ-ืฆื-ืกืืฃ ืึทื ืึทืืืืืงืก, ืืื ืื ืคืขืื ืคืื ืืืืขืจืืืื, ืืึทืฉืื ืืขืจื ืขื.
ืืื ืื ืกืืฃ, ืึท ืืืกื ืืืขืื ืื ืคึผืจืึธืก ืืื ืงืึธื ืก.
ืงืึธื ืก
- ืืึธืืืื ื ืจืขืงืึธืจืืก ืืื ืืจืืืก ืืึทืืฉืึทื. ืืืืฃ ืื ืืืื ืืึทื ื, ืืึธืก ืืื ืึท ืฉืืจืื, ืึธืืขืจ ืืืจ ื ืึธื ืืึธืื ืฆื ื ืืฆื ื ืึธื ืงืึทืืคึผืึธืื ืึทื ืฅ ืคึฟืึทืจ ืืึทืคืขืจืื ื ืจืขืงืึธืจืืก. ืืขื ืึทืจืืขื ืืื ื ืื ืฉืืขื ืืืง ืืจืื ื, ืึธืืขืจ ื ืึธื ืกืึธืืืืึทืืึทื. ืืื ืืื ืืืึธืื ืืื ืฆื ืคืึทืจืคึผืึธืฉืขืืขืจื ืื ืกืืขืืข.
- ืขืืืขืืข ืขืงืืึธืืืฉ ืคืึทื ืืงืฉืึทื ืึทืืืื ืึธืืขืจ ื ืืึท ืคึฟืขืึดืงืืืื ืึธืคื ืืจืขืื ืืื ื ืืึทืข ืืืขืจืกืืขืก. ืืึธืก ืืื ืืืึทืืข, ืจืืืืกืื ื ืื ืคืึทืจืืึทื ื ืฆื ืึทืคึผืืจืืื ืฆื ืึท ื ืืึทืข ืืืขืจืกืืข. ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืื ืงืึทืคืงืึท ืืืฉ ืืึธืืึธืจ ืืื ืึท ืืืืขืจ ื ืืฆืืง ืฉืืจืื ืืืึธืก ืึทืืึทืื ืืืจ ืฆื ืืืืขื ืขื ืืขืฉืขืขื ืืฉื ืคืื ืงืึทืคืงืึท ืืืืึทื ืึธื ืืืคึผืืึทืืขื ืื ื ืงืึธื ืกืืืขืจืก. ืึธืืขืจ ืืืื ืืฉืคืื ืืืื ืื ื ืืืขืจ ืคืื ืืฉืื ืืืืฃ ืื ืืืืืื, ืืืจ ืืขื ืขื ื ืึธื ืึธืคึผืืขืืื ื ืื ืฆื ื ืืฆื ืืขื ืืึธืืึธืจ ืืื ืคึผืจืึธืืืงืฆืืข. ืึธืืขืจ, ืืืื ืืืจ ืืึธื ื ืื ืคึผืืืฆืขืืืืง ืืืฉืขืกืืฉืขืจื ืฆื ืื ืืืึทื ืืื ื ืืฆื ืื ืืืืคึผื ืคืึทื ืืงืฉืึทื ืึทืืืื, ืขืก ืึทืจืืขื ืกืืึทืืื.
ืคึผืจืึธืก
- ืืื ื ืืฉื ืคึผืึทืืขืืขื ืึทืจืึธืคึผ.
- ื ืืืขืจืืง ืคึผืึธืืืฆืืข ืฉืืืขื.
- ืึธืคึฟื ืืงืืจ.
- ืคืจืืึท.
- ืืืึธื ืืขืืื ื (ืฉืึทืจืืื ื / ืจืขืคึผืืึทืงืืืฉืึทื ืืืืก ืคืื ืื ืงืขืกืื)
- ืึทืจืืึทื ืืขืจืขืื ื ืืื ืื ืจืขืืืกืืจืืจื ืคืื ืจืืกืืฉ ืืืืืืืืืจื ืจืขืงืึทืืขื ืืื ืืืจื ืืขืจ ืืื ืืกืืขืจืืื ืคืื ืงืึธืืื ืืงืึทืฆืืข.
- ืื ืืืึทืืืึทื ืคืื ืืึทืึทืืืขืจ ืฉืืืฆื ืคืื ืืึทื ืืขืงืก.
ืืงืืจ: www.habr.com