Hi Folks, I am trying to use Loki and Grafana to make a MySql qeury execution time visualization. I’m not able to properly configure Promtail to parse JSON logs. The log structure is a JSON list that has objects inside which looks smth like this:
[
{
"THREAD_ID": 129265,
"EVENT_ID": 2555,
"END_EVENT_ID": 2577,
"EVENT_NAME": "statement/sql/select",
"SOURCE": "init_net_server_extension.cc:102",
"TIMER_START": 674551125162346000,
"TIMER_END": 674551125629310000,
"TIMER_WAIT": 466964000,
"LOCK_TIME": 0,
"SQL_TEXT": "SELECT\n\t\t TABLE_SCHEMA,\n\t\t TABLE_NAME,\n\t\t
TABLE_TYPE,\n\t\t ifnull(ENGINE, 'NONE') as ENGINE,\n\t\t ifnull(VERS
ION, '0') as VERSION,\n\t\t ifnull(ROW_FORMAT, 'NONE') as
ROW_FORMAT,\n\t\t ifnull(TABLE_ROWS, '0') as TABLE_ROWS,\n\t\t
ifnull(DATA_LENGTH, '0') as DATA_LENGTH,\n\t\t ifnull(INDEX_LENGTH,
'0') as INDEX_LENGTH,\n\t\t ifnull(DATA_FREE, '0') as DATA_FREE,\n\t\t
ifnull(CREATE_OPTIONS, 'NONE') as CREATE_OPTIONS\n\t\t FROM
information_schema.tables\n\t\t WHERE TABLE_SCHEMA =
'test_file_service'",
"DIGEST": "d878e3005775daaa0064fcc5f54e9669d246eabe67e37b5a6ae8fe8fc2a281f5",
"DIGEST_TEXT": "SELECT `TABLE_SCHEMA` , TABLE_NAME , `TABLE_TYPE` , `ifnull` (
ENGINE , ? ) AS ENGINE , `ifnull` ( `VERSION` , ? ) AS `VERSION` , `$
fnull` ( ROW_FORMAT , ? ) AS ROW_FORMAT , `ifnull` ( `TABLE_ROWS`
, ? ) AS `TABLE_ROWS` , `ifnull` ( `DATA_LENGTH` , ? ) AS
`DATA_LENGTH` , `ifnull` ( `
INDEX_LENGTH` , ? ) AS `INDEX_LENGTH` , `ifnull` ( `DATA_FREE` , ? )
AS `DATA_FREE` , `ifnull` ( `CREATE_OPTIONS` , ? ) AS `
CREATE_OPTIONS` FROM `information_schema` . `tables` WHERE
`TABLE_SCHEMA` = ?",
"CURRENT_SCHEMA": null,
"OBJECT_TYPE": null,
"OBJECT_SCHEMA": null,
"OBJECT_NAME": null,
"OBJECT_INSTANCE_BEGIN": null,
"MYSQL_ERRNO": 0,
"RETURNED_SQLSTATE": null,
"MESSAGE_TEXT": null,
"EXECUTION_ENGINE": "PRIMARY",
"start_time": "2023-11-01 17:36:52.125163",
"timer_wait_ms": "0.466964",
"timer_wait_s": "0.000467",
"lock_time_s": "0",
"wait_human": "466.96 us",
"timestamp_rnd": "1698860212",
"timestamp": "1698860212.125163"
},
{...},
{...}
]
I have all the logs files stored on a VM which hosts Grafana, loki, and Promtail as docker-containers in a docker-compose file, this is my promtail-local-config.yaml configurations, which i’m struggling to figure out:
server:
http_listen_port: 9080
grpc_listen_port: 0
positions:
filename: /tmp/positions.yaml
clients:
- url: http://loki:3100/loki/api/v1/push
scrape_configs:
- job_name: system
pipeline_stages:
- json:
expressions:
timer_wait_s: timer_wait_s
timer_wait_ms: timer_wait_ms
wait_human: wait_human
timestamp: timestamp
- timestamp:
source: time
format: RFC3339Nano
static_configs:
- targets:
- localhost
labels:
job: varlogs
__path__: /var/log/slow_log_query/*.log
This is my docker-compose file:
---
version: "3"
networks:
prom:
services:
loki:
image: grafana/loki:2.9.0
ports:
- "3100:3100"
command: -config.file=/etc/loki/local-config.yaml
networks:
- prom
promtail:
image: grafana/promtail:2.9.0
volumes:
- /var/log/slow_log_query:/var/log/slow_log_query
- ./promtail-local-config.yaml:/etc/promtail/config.yml:ro
command: -config.file=/etc/promtail/config.yml
networks:
- prom
grafana:
environment:
- GF_PATHS_PROVISIONING=/etc/grafana/provisioning
- GF_AUTH_ANONYMOUS_ENABLED=true
- GF_AUTH_ANONYMOUS_ORG_ROLE=Admin
entrypoint:
- sh
- -euc
- |
mkdir -p /etc/grafana/provisioning/datasources
cat <<EOF > /etc/grafana/provisioning/datasources/ds.yaml
apiVersion: 1
datasources:
- name: Loki
type: loki
access: proxy
orgId: 1
url: http://loki:3100
basicAuth: false
isDefault: true
version: 1
editable: false
EOF
/run.sh
image: grafana/grafana:latest
ports:
- "3000:3000"
networks:
- prom
the containers are working just fine with the configured volumes and ports and everything. But when I access the explore page on Grafana and choose Loki as the data source I can’t find any labels or anything to interact with.
Any idea how to deal with such json logs ?