diff --git a/zabbix-agent-scripts/discover_db_metrics.py b/zabbix-agent-scripts/discover_db_metrics.py new file mode 100644 index 0000000..726144e --- /dev/null +++ b/zabbix-agent-scripts/discover_db_metrics.py @@ -0,0 +1,14 @@ +import pandas as pd + +import sys + +import json + +df = pd.read_json("/var/lib/zabbix/output/db_metrics.json") + +table_list = [] + +for table_name in df.table_name: + table_list.append({"{#TABLENAME}":table_name}) + +print(json.dumps(table_list)) diff --git a/zabbix-agent-scripts/get_db_metrics.py b/zabbix-agent-scripts/get_db_metrics.py index 82a985b..f880658 100644 --- a/zabbix-agent-scripts/get_db_metrics.py +++ b/zabbix-agent-scripts/get_db_metrics.py @@ -16,7 +16,7 @@ nc_db = "nextcloud_db" -tables_to_monitor = "'oc_filecache','oc_comments','oc_authtoken','oc_circles_event','oc_share'" +#tables_to_monitor = "'oc_filecache','oc_comments','oc_authtoken','oc_circles_event','oc_share'" ## @@ -33,16 +33,17 @@ """ SELECT TABLE_NAME AS table_name, TABLE_ROWS AS nr_rows, - ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 ) AS size_kb + (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS size_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s' - ORDER BY size_kb DESC + ORDER BY size_mb DESC + LIMIT 5 """ % nc_db ) df = pd.DataFrame( cur.fetchall(), - columns=["table_name","nr_rows","size_kb"] + columns=["table_name","nr_rows","size_mb"] ) -df.query("table_name in (%s)" % tables_to_monitor).to_json( "/var/lib/zabbix/output/db_metrics.json", +df.to_json( "/var/lib/zabbix/output/db_metrics.json", orient='columns') diff --git a/zabbix-templates/nextcloud-by-rcasystems.json b/zabbix-templates/nextcloud-by-rcasystems.json index 1c3c270..3b936bf 100644 --- a/zabbix-templates/nextcloud-by-rcasystems.json +++ b/zabbix-templates/nextcloud-by-rcasystems.json @@ -90,118 +90,12 @@ "description": "User Agent - Safari browser - Last 24h" }, { - "uuid": "ded1a989412f41d7bbe37ec7c3e4cfc1", - "name": "Number of rows in oc_authtoken", - "key": "db_metric[oc_authtoken,nr_rows]", - "delay": "1h", - "description": "Number of rows in oc_authtoken" - }, - { - "uuid": "102d3ad883a2402286383a727a7c1fb8", - "name": "Size of oc_authtoken", - "key": "db_metric[oc_authtoken,size_kb]", - "delay": "1h", - "units": "bytes", - "description": "Size of oc_authtoken", - "preprocessing": [ - { - "type": "MULTIPLIER", - "parameters": [ - "1024" - ] - } - ] - }, - { - "uuid": "191cbeaa492242f2bfc45f77ae2dea79", - "name": "Number of rows in oc_circles_event", - "key": "db_metric[oc_circles_event,nr_rows]", - "delay": "1h", - "description": "Number of rows in oc_circles_event" - }, - { - "uuid": "2a77521231c9402fae51bdd4881d5f70", - "name": "Size of oc_circles_event", - "key": "db_metric[oc_circles_event,size_kb]", - "delay": "1h", - "units": "bytes", - "description": "Size of oc_circles_event", - "preprocessing": [ - { - "type": "MULTIPLIER", - "parameters": [ - "1024" - ] - } - ] - }, - { - "uuid": "4659d54d88f346aa9b81d4030c871341", - "name": "Number of rows in oc_comments", - "key": "db_metric[oc_comments,nr_rows]", - "delay": "1h", - "description": "Number of rows in oc_comments" - }, - { - "uuid": "631a34f6ee6b473b86058a61999dff34", - "name": "Size of oc_comments", - "key": "db_metric[oc_comments,size_kb]", - "delay": "1h", - "units": "bytes", - "description": "Size of oc_comments", - "preprocessing": [ - { - "type": "MULTIPLIER", - "parameters": [ - "1024" - ] - } - ] - }, - { - "uuid": "afd0e83623dd48608c5ece7b5e080977", - "name": "Number of rows in oc_filecache", - "key": "db_metric[oc_filecache,nr_rows]", - "delay": "1h" - }, - { - "uuid": "bb3cece762ef49c392571dfc60c63521", - "name": "Size of oc_filecache", - "key": "db_metric[oc_filecache,size_kb]", - "delay": "1h", - "units": "bytes", - "description": "Size of oc_filecache", - "preprocessing": [ - { - "type": "MULTIPLIER", - "parameters": [ - "1024" - ] - } - ] - }, - { - "uuid": "e469a3761190441c8b91f8317b16db72", - "name": "Number of rows in oc_share", - "key": "db_metric[oc_share,nr_rows]", - "delay": "1h", - "description": "Number of rows in oc_share" - }, - { - "uuid": "175935c88011467890419e45996404e2", - "name": "Size of oc_share", - "key": "db_metric[oc_share,size_kb]", - "delay": "1h", - "units": "bytes", - "description": "Size of oc_share", - "preprocessing": [ - { - "type": "MULTIPLIER", - "parameters": [ - "1024" - ] - } - ] + "uuid": "b7dac9facd27414da6710117d61ba6af", + "name": "Database Full Version", + "key": "db_full_version", + "delay": "1d", + "trends": "0", + "value_type": "TEXT" }, { "uuid": "8bfc17be6a414c5ca715e46ebca038f8", @@ -309,14 +203,23 @@ }, { "uuid": "30b554fb5c2845439e3486b6a0927c1a", - "name": "Database Size", + "name": "Database Size (reported by Nextcloud)", "key": "nc_metric[ocs.data.server.database.size]", - "delay": "24h", - "description": "Database Size (in bytes)" + "delay": "1h", + "value_type": "FLOAT", + "description": "Database Size (in bytes)", + "preprocessing": [ + { + "type": "MULTIPLIER", + "parameters": [ + "9.5367431640625E-7" + ] + } + ] }, { "uuid": "209893b4f7764f45a26afcf055dbd5cd", - "name": "Database", + "name": "Database (reported by Nextcloud)", "key": "nc_metric[ocs.data.server.database.type]", "delay": "1d", "trends": "0", @@ -325,7 +228,7 @@ }, { "uuid": "f8b1b78f1ed7472ba73e5bdb031577a2", - "name": "Database Version", + "name": "Database Version (reported by Nextcloud)", "key": "nc_metric[ocs.data.server.database.version]", "delay": "1d", "trends": "0", @@ -358,12 +261,66 @@ "description": "Nextcloud data directory storage size (in Gigabytes)" } ], + "discovery_rules": [ + { + "uuid": "3f2820db83ff4b2fb87013d3d6fcd0be", + "name": "Largest database tables", + "key": "db_metric_discovery", + "delay": "1h", + "item_prototypes": [ + { + "uuid": "1e53988836eb465eb220402a6721df4d", + "name": "Number of rows {#TABLENAME}", + "key": "db_metric[{#TABLENAME},nr_rows]", + "delay": "1h" + }, + { + "uuid": "6d82cf7849154cb3859001cfa86fff9c", + "name": "Size of {#TABLENAME}", + "key": "db_metric[{#TABLENAME},size_mb]", + "delay": "1h", + "value_type": "FLOAT" + } + ], + "graph_prototypes": [ + { + "uuid": "bc89e54de2e143d19b5623c40cbb666f", + "name": "Number of rows in {#TABLENAME}", + "graph_items": [ + { + "color": "1A7C11", + "calc_fnc": "ALL", + "item": { + "host": "Nextcloud by RCA Systems", + "key": "db_metric[{#TABLENAME},nr_rows]" + } + } + ] + }, + { + "uuid": "1eb51c1032034c50b2b887ebc31f660e", + "name": "Size of {#TABLENAME} in MB", + "graph_items": [ + { + "color": "1A7C11", + "calc_fnc": "ALL", + "item": { + "host": "Nextcloud by RCA Systems", + "key": "db_metric[{#TABLENAME},size_mb]" + } + } + ] + } + ] + } + ], "dashboards": [ { "uuid": "e7808bdb7dc64e23a3d5e8edd8ac4e59", "name": "Nextcloud Dashboard", "pages": [ { + "name": "Application", "widgets": [ { "type": "item", @@ -489,22 +446,6 @@ ] }, { - "type": "graph", - "y": "23", - "width": "12", - "height": "5", - "fields": [ - { - "type": "GRAPH", - "name": "graphid", - "value": { - "host": "Nextcloud by RCA Systems", - "name": "Size of Largest Database Tables" - } - } - ] - }, - { "type": "item", "name": "Total Number of Files", "x": "4", @@ -703,23 +644,6 @@ }, { "type": "graph", - "x": "12", - "y": "18", - "width": "12", - "height": "5", - "fields": [ - { - "type": "GRAPH", - "name": "graphid", - "value": { - "host": "Nextcloud by RCA Systems", - "name": "Number of Rows of Largest Database Tables" - } - } - ] - }, - { - "type": "graph", "x": "16", "y": "8", "width": "4", @@ -794,6 +718,118 @@ ] } ] + }, + { + "name": "Database", + "widgets": [ + { + "type": "item", + "name": "Database", + "width": "4", + "height": "3", + "fields": [ + { + "type": "ITEM", + "name": "itemid", + "value": { + "host": "Nextcloud by RCA Systems", + "key": "db_full_version" + } + }, + { + "type": "INTEGER", + "name": "show", + "value": "4" + } + ] + }, + { + "type": "graphprototype", + "name": "Number of rows in {#TABLENAME}", + "y": "4", + "width": "24", + "height": "5", + "fields": [ + { + "type": "INTEGER", + "name": "columns", + "value": "5" + }, + { + "type": "GRAPH_PROTOTYPE", + "name": "graphid", + "value": { + "host": "Nextcloud by RCA Systems", + "name": "Number of rows in {#TABLENAME}" + } + } + ] + }, + { + "type": "graphprototype", + "name": "Size of {#TABLENAME}", + "y": "10", + "width": "24", + "height": "5", + "fields": [ + { + "type": "INTEGER", + "name": "columns", + "value": "5" + }, + { + "type": "GRAPH_PROTOTYPE", + "name": "graphid", + "value": { + "host": "Nextcloud by RCA Systems", + "name": "Size of {#TABLENAME} in MB" + } + } + ] + }, + { + "type": "item", + "name": "Database Size", + "x": "4", + "width": "4", + "height": "3", + "fields": [ + { + "type": "INTEGER", + "name": "adv_conf", + "value": "1" + }, + { + "type": "INTEGER", + "name": "decimal_places", + "value": "0" + }, + { + "type": "ITEM", + "name": "itemid", + "value": { + "host": "Nextcloud by RCA Systems", + "key": "nc_metric[ocs.data.server.database.size]" + } + }, + { + "type": "INTEGER", + "name": "show", + "value": "2" + }, + { + "type": "INTEGER", + "name": "show", + "value": "4" + }, + { + "type": "STRING", + "name": "units", + "value": "MB" + } + ] + } + ] } ] } @@ -904,56 +940,6 @@ ] }, { - "uuid": "5808ab16e1b445b0b3910e13f1be6e84", - "name": "Number of Rows of Largest Database Tables", - "graph_items": [ - { - "color": "1A7C11", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_authtoken,nr_rows]" - } - }, - { - "sortorder": "1", - "color": "274482", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_circles_event,nr_rows]" - } - }, - { - "sortorder": "2", - "color": "F63100", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_comments,nr_rows]" - } - }, - { - "sortorder": "3", - "color": "2774A4", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_filecache,nr_rows]" - } - }, - { - "sortorder": "4", - "color": "A54F10", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_share,nr_rows]" - } - } - ] - }, - { "uuid": "ad1a59aeb6464aca86c7d7249752dcc1", "name": "Number of Shares", "graph_items": [ @@ -1164,56 +1150,6 @@ ] }, { - "uuid": "06ae4a6f7de141538d3432a9c1a60935", - "name": "Size of Largest Database Tables", - "graph_items": [ - { - "color": "1A7C11", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_authtoken,size_kb]" - } - }, - { - "sortorder": "1", - "color": "274482", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_circles_event,size_kb]" - } - }, - { - "sortorder": "2", - "color": "F63100", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_comments,size_kb]" - } - }, - { - "sortorder": "3", - "color": "2774A4", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_filecache,size_kb]" - } - }, - { - "sortorder": "4", - "color": "A54F10", - "calc_fnc": "ALL", - "item": { - "host": "Nextcloud by RCA Systems", - "key": "db_metric[oc_share,size_kb]" - } - } - ] - }, - { "uuid": "c95d504d0c43436698723d96f5c698d1", "name": "Total Storage in Data Directory", "graph_items": [ diff --git a/zabbix_agentd.conf.d/lxd.conf b/zabbix_agentd.conf.d/lxd.conf deleted file mode 100644 index d827126..0000000 --- a/zabbix_agentd.conf.d/lxd.conf +++ /dev/null @@ -1,4 +0,0 @@ -UserParameter=ct.memory.size[*],free -b | awk '$ 1 == "Mem:" {total=$ 2; used=($ 3+$ 5); pused=(($ 3+$ 5)*100/$ 2); free=$ 4; pfree=($ 4*100/$ 2); shared=$ 5; buffers=$ 6; cached=$ 6; available=$ 7; pavailable=($ 7*100/$ 2); if("$1" == "") {printf("%.0f", total )} else {printf("%.0f", $1 "" )} }' -UserParameter=ct.swap.size[*],free -b | awk '$ 1 == "Swap:" {total=$ 2; used=$ 3; free=$ 4; pfree=($ 4*100/$ 2); pused=($ 3*100/$ 2); if("$1" == "") {printf("%.0f", free )} else {printf("%.0f", $1 "" )} }' -UserParameter=ct.cpu.load[*],uptime | awk -F'[, ]+' '{avg1=$(NF-2); avg5=$(NF-1); avg15=$(NF)}{print $2/'$(nproc)'}' -UserParameter=ct.uptime,cut -d"." -f1 /proc/uptime diff --git a/zabbix_agentd.conf.d/mariadb.conf b/zabbix_agentd.conf.d/mariadb.conf deleted file mode 100644 index f071633..0000000 --- a/zabbix_agentd.conf.d/mariadb.conf +++ /dev/null @@ -1,7 +0,0 @@ -UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping -UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status" -UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version -UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases" -UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'" -UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status" -UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status" diff --git a/zabbix_agentd.conf.d/nextcloud.conf b/zabbix_agentd.conf.d/nextcloud.conf deleted file mode 100644 index 2a2c9ab..0000000 --- a/zabbix_agentd.conf.d/nextcloud.conf +++ /dev/null @@ -1,4 +0,0 @@ -UserParameter=nc_metric[*],/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/read_nc_metrics.py $1 -UserParameter=storage_metric[*],/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/read_storage_metrics.py $1 -UserParameter=db_metric[*],/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/read_db_metrics.py $1 $2 -UserParameter=apache2_user_agent_metric[*],/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/read_apache2_user_agent_metrics.py $1 diff --git a/zabbix_agentd.conf.d/zabbix-agent-lxd.conf b/zabbix_agentd.conf.d/zabbix-agent-lxd.conf new file mode 100644 index 0000000..d827126 --- /dev/null +++ b/zabbix_agentd.conf.d/zabbix-agent-lxd.conf @@ -0,0 +1,4 @@ +UserParameter=ct.memory.size[*],free -b | awk '$ 1 == "Mem:" {total=$ 2; used=($ 3+$ 5); pused=(($ 3+$ 5)*100/$ 2); free=$ 4; pfree=($ 4*100/$ 2); shared=$ 5; buffers=$ 6; cached=$ 6; available=$ 7; pavailable=($ 7*100/$ 2); if("$1" == "") {printf("%.0f", total )} else {printf("%.0f", $1 "" )} }' +UserParameter=ct.swap.size[*],free -b | awk '$ 1 == "Swap:" {total=$ 2; used=$ 3; free=$ 4; pfree=($ 4*100/$ 2); pused=($ 3*100/$ 2); if("$1" == "") {printf("%.0f", free )} else {printf("%.0f", $1 "" )} }' +UserParameter=ct.cpu.load[*],uptime | awk -F'[, ]+' '{avg1=$(NF-2); avg5=$(NF-1); avg15=$(NF)}{print $2/'$(nproc)'}' +UserParameter=ct.uptime,cut -d"." -f1 /proc/uptime diff --git a/zabbix_agentd.conf.d/zabbix-agent-mariadb.conf b/zabbix_agentd.conf.d/zabbix-agent-mariadb.conf new file mode 100644 index 0000000..f071633 --- /dev/null +++ b/zabbix_agentd.conf.d/zabbix-agent-mariadb.conf @@ -0,0 +1,7 @@ +UserParameter=mysql.ping[*], mysqladmin -h"$1" -P"$2" ping +UserParameter=mysql.get_status_variables[*], mysql -h"$1" -P"$2" -sNX -e "show global status" +UserParameter=mysql.version[*], mysqladmin -s -h"$1" -P"$2" version +UserParameter=mysql.db.discovery[*], mysql -h"$1" -P"$2" -sN -e "show databases" +UserParameter=mysql.dbsize[*], mysql -h"$1" -P"$2" -sN -e "SELECT COALESCE(SUM(DATA_LENGTH + INDEX_LENGTH),0) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$3'" +UserParameter=mysql.replication.discovery[*], mysql -h"$1" -P"$2" -sNX -e "show slave status" +UserParameter=mysql.slave_status[*], mysql -h"$1" -P"$2" -sNX -e "show slave status" diff --git a/zabbix_agentd.conf.d/zabbix-agent-nextcloud.conf b/zabbix_agentd.conf.d/zabbix-agent-nextcloud.conf new file mode 100644 index 0000000..520d4f1 --- /dev/null +++ b/zabbix_agentd.conf.d/zabbix-agent-nextcloud.conf @@ -0,0 +1,5 @@ +UserParameter=nc_metric[*],/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/read_nc_metrics.py $1 +UserParameter=storage_metric[*],/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/read_storage_metrics.py $1 +UserParameter=db_metric[*],/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/read_db_metrics.py $1 $2 +UserParameter=db_metric_discovery,/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/discover_db_metrics.py +UserParameter=apache2_user_agent_metric[*],/opt/pyvenv/ncmonitor/bin/python /var/lib/zabbix/scripts/read_apache2_user_agent_metrics.py $1