mongo日志可视化

原始mongod.log格式：

2020-11-26T21:33:45.896+0800 I  NETWORK  [listener] connection accepted from 10.10.6.192:52138 #1663452 (121 connections now open)
2020-11-26T21:33:45.896+0800 I  NETWORK  [conn1663452] received client metadata from 10.10.6.192:52138 conn1663452: { driver: { name: "mongo-csharp-driver", version: "2.11.0.0" }, os: { type: "Linux", name: "Linux 5.3.0-1019-aws #21~18.04.1-Ubuntu SMP Mon May 11 12:33:03 UTC 2020", architecture: "x86_64", version: "5.3.0-1019-aws" }, platform: ".NET Core 3.1.4" }
2020-11-26T21:33:45.910+0800 I  ACCESS   [conn1663452] Successfully authenticated as principal push on push from client 10.10.6.192:52138
2020-11-26T21:33:46.325+0800 I  NETWORK  [listener] connection accepted from 10.10.3.144:50170 #1663453 (122 connections now open)
2020-11-26T21:33:46.325+0800 I  NETWORK  [conn1663453] received client metadata from 10.10.3.144:50170 conn1663453: { driver: { name: "PyMongo", version: "3.8.0" }, os: { type: "Linux", name: "Linux", architecture: "x86_64", version: "4.15.0-1054-aws" }, platform: "CPython 3.7.3.final.0" }
2020-11-26T21:33:46.326+0800 I  NETWORK  [listener] connection accepted from 10.10.3.144:50172 #1663454 (123 connections now open)
2020-11-26T21:33:46.326+0800 I  NETWORK  [conn1663454] received client metadata from 10.10.3.144:50172 conn1663454: { driver: { name: "PyMongo", version: "3.8.0" }, os: { type: "Linux", name: "Linux", architecture: "x86_64", version: "4.15.0-1054-aws" }, platform: "CPython 3.7.3.final.0" }
2020-11-26T21:33:46.339+0800 I  ACCESS   [conn1663454] Successfully authenticated as principal crawl on crawl from client 10.10.3.144:50172
2020-11-26T21:33:46.888+0800 I  NETWORK  [conn1663454] end connection 10.10.3.144:50172 (122 connections now open)
2020-11-26T21:33:46.888+0800 I  NETWORK  [conn1663453] end connection 10.10.3.144:50170 (121 connections now open

这种格式非常不便于阅读，于是各种找寻mongo日志可视化工具，发现一个mtools好工具，能够格式化mongo日志，并且可以直接输出为json格式，于是学习了一下简单的吧日志传到elk里面展示。

(1)首先写一个简单的脚本：

root@pro-cs-mongo-2-74:/fmApplication/mongo-commission/logs# cat /backup_local/dba/scripts/mongo_mfilter.sh
#!/usr/bin/env bash
#取最近1min 执行耗时超过200ms慢日志
logs="/fmApplication/mongo-commission/logs/mongod.log"
start_time=`date -d "1 min ago" "+%b %e %R"`
/usr/local/bin/mlogfilter $logs --slow 200 --from $start_time --to "+1min" --json >> /fmApplication/mongo-commission/logs/mongo.json

　然后加入crontab，每分钟执行一次，取最近一分钟的日志新产生的日志，这样mongod.log就可以实时json化为mongo.json

root@pro-cs-mongo-2-74:/fmApplication/mongo-commission/logs# crontab -l
# *    *    *    *    *
#min hour  day  month  day_of_week
#
* * * * * script -c "/backup_local/dba/scripts/mongo_mfilter.sh >>/tmp/crontab.log

　检查日志输出已经没问题，然后开始接入ELK,我这里直接用阿里云的日志服务。

　(2)安装配置阿里云的logstail，并且接入json文件，接入以后的显示效果为：

看起来还是不够友好，然后再用json函数格式化一下，取关键信息：

*|SELECT replace(substr(datetime,1,19),\'T\',\' \') as datetime,
duration,operation,thread,namespace,concat(json_format(json_array_get(split_tokens,5)),
json_format(json_array_get(split_tokens,6)),
json_format(json_array_get(split_tokens,7)),
json_format(json_array_get(split_tokens,8)),
json_format(json_array_get(split_tokens,9)),
json_format(json_array_get(split_tokens,10)),
json_format(json_array_get(split_tokens,11)),
json_format(json_array_get(split_tokens,12)),
json_format(json_array_get(split_tokens,13)),
json_format(json_array_get(split_tokens,14)),
json_format(json_array_get(split_tokens,15)),
json_format(json_array_get(split_tokens,16)),
json_format(json_array_get(split_tokens,17)),
json_format(json_array_get(split_tokens,18)),
json_format(json_array_get(split_tokens,19)),
json_format(json_array_get(split_tokens,20)),
json_format(json_array_get(split_tokens,21)),
json_format(json_array_get(split_tokens,22)),
json_format(json_array_get(split_tokens,23)),
json_format(json_array_get(split_tokens,24)),
json_format(json_array_get(split_tokens,25)),
json_format(json_array_get(split_tokens,26)),
json_format(json_array_get(split_tokens,27)),
json_format(json_array_get(split_tokens,28)),
json_format(json_array_get(split_tokens,29)),
json_format(json_array_get(split_tokens,30)),
json_format(json_array_get(split_tokens,31)),
json_format(json_array_get(split_tokens,32)),
json_format(json_array_get(split_tokens,33)),
json_format(json_array_get(split_tokens,34)),
json_format(json_array_get(split_tokens,35))) as query_sql where duration>200

这个是日志服务sql语法，最终效果为：