Application - Postgresql fails to parse the data

Tomas Andriekus added a comment - 2020/10/09 05:55 PM - edited I have had some time and rewrote the original Perl script to Python3 with a mind that I can add my own functionality. However, I don't understand how to calculate the data from PG which keeps increasing from the beginning. I understand that Ivan subtracted the numbers every 5 minutes, but the question remains where the state was kept? Sharing my script attempt: #!/usr/bin/env python3 ##: Date: 2020 - 10 - 09 - 2020 - 10 - 12 ##: Author: Tomas Andriekus @ <t.andriekus at gmail dot com> ##: Requirements: > Python3. 6 & psycopg module ##: Description: PostgreSQL stats script rewritten from Ivan Dimitrov Perl script. Big Thanks to him // ## Originally dedicated for Observium - Network management and monitoring tool // ## Should work with Postgres > 9 and above. // ## https: //docs.observium.org/apps/#postgresql //   import psycopg2   # Please *SECURE your credentials* db_host = '127.0.0.1' db_user = 'postgres' db_pass = 'whatsthes3cr3t' db_name = 'mydb'   PG_ACTIVITY = '' ' SELECT datname, usename, client_addr, query FROM pg_stat_activity WHERE client_addr is not NULL; '' ' NUM_COMMITS = '' ' SELECT SUM(xact_commit) as xact_commit, SUM(xact_rollback) as xact_rollback, SUM(blks_read) as blks_read, SUM(blks_hit) as blks_hit, SUM(tup_returned) as tup_returned, SUM(tup_fetched) as tup_fetched, SUM(tup_inserted) as tup_inserted, SUM(tup_updated) as tup_updated, SUM(tup_deleted) as tup_deleted FROM pg_stat_database; '' '   version = [] pg_commits = {} pg_connection_count = { 'count' : 0 } pg_datnames = [] pg_usenames = [] pg_client_addrs = [] pg_idle = [] pg_select = [] pg_insert = [] pg_update = [] pg_delete = [] pg_other = []     def analyze_pg_version(pg_ver): if int (pg_ver) < 100000 : return str(pg_ver)[: 1 ] + '.' + str(pg_ver)[ 1 : 2 ] elif int (pg_ver) > 100000 and int (pg_ver) < 1000000 : return str(pg_ver)[: 2 ] + '.' + str(pg_ver)[ 2 : 3 ] else : # This should never happen. return ( "???" )     def execute_pg_statement(cursor, statement): cursor.execute(statement) data = cursor.fetchall() return data     def aggregate_connections_data(data): count = len(data)   if str(count) != '0' or count != None: cc = 0 for i in data: seen_datnames = set(pg_datnames) seen_usenames = set(pg_usenames) seen_client_addrs = set(pg_client_addrs)   if i[ 0 ] not in seen_datnames and i[ 0 ] != None and i[ 0 ] != '' : pg_datnames.append(i [ 0 ]) if i[ 1 ] not in seen_usenames and i[ 1 ] != None and i[ 0 ] != '' : pg_usenames.append(i[ 1 ]) if i[ 2 ] != None: cc += 1 pg_connection_count[ 'count' ] = cc   if i[ 2 ] not in seen_client_addrs and i[ 2 ] != None: pg_client_addrs.append(i[ 2 ])   if i[ 3 ] != None or i[ 3 ] != '' : if 'SELECT' or 'select' in i[ 3 ]: pg_select.append(i[ 3 ]) elif 'INSERT' or 'insert' in i[ 3 ]: pg_insert.append(i[ 3 ]) elif 'UPDATE' or 'update' in i[ 3 ]: pg_update.append(i[ 3 ]) elif 'DELETE' or 'delete' in i[ 3 ]: pg_delete.append(i[ 3 ]) elif '<IDLE>' in i[ 3 ]: pg_idle.append(i[ 3 ]) else : pg_other.append(i[ 3 ])     def aggregate_commits(data): # This enum correlates with - NUM_COMMITS query; pg_commits[ 'xact_commit' ] = (data[ 0 ]) pg_commits[ 'xact_rollback' ] = (data[ 1 ]) pg_commits[ 'blks_read' ] = (data[ 2 ]) pg_commits[ 'blks_hit' ] = (data[ 3 ]) pg_commits[ 'tup_returned' ] = (data[ 4 ]) pg_commits[ 'tup_fetched' ] = (data[ 5 ]) pg_commits[ 'tup_inserted' ] = (data[ 6 ]) pg_commits[ 'tup_updated' ] = (data[ 7 ]) pg_commits[ 'tup_deleted' ] = (data[ 8 ])     def fetch(): try : conn = psycopg2.connect(host=db_host, user=db_user, password=db_pass, database=db_name)   version.append(analyze_pg_version(conn.server_version))   cursor = conn.cursor()   # Execute SQL queries; pg_activity_result = execute_pg_statement(cursor, PG_ACTIVITY) aggregate_connections_data(pg_activity_result)   pg_num_activity_result = execute_pg_statement(cursor, NUM_COMMITS) aggregate_commits(pg_num_activity_result[ 0 ])   except (Exception, psycopg2.DatabaseError) as error: print(error)   finally : conn.close()     if __name__ == '__main__' : fetch()   print(f "<<<app-postgresql>>>" ) print(f "version:{version[0]}" ) print(f "cCount:{pg_connection_count.get('count')}" ) print(f "tDbs:{len(pg_datnames)}" ) print(f "tUsr:{len(pg_usenames)}" ) print(f "tHst:{len(pg_client_addrs)}" ) print(f "idle:{len(pg_idle)}" ) print(f "select:{len(pg_select)}" ) print(f "update:{len(pg_update)}" ) print(f "delete:{len(pg_delete)}" ) print(f "other:{len(pg_other)}" ) print(f "xact_commit:{pg_commits.get('xact_commit')}" ) print(f "xact_rollback:{pg_commits.get('xact_rollback')}" ) print(f "blks_read:{pg_commits.get('blks_read')}" ) print(f "blks_hit:{pg_commits.get('blks_hit')}" ) print(f "tup_returned:{pg_commits.get('tup_returned')}" ) print(f "tup_fetched:{pg_commits.get('tup_fetched')}" ) print(f "tup_inserted:{pg_commits.get('tup_inserted')}" ) print(f "tup_updated:{pg_commits.get('tup_updated')}" ) print(f "tup_deleted:{pg_commits.get('tup_deleted')}" ) Thanks!

Tomas Andriekus added a comment - 2020/10/09 10:56 AM Specifying version manually - does not help.   /usr/lib/observium_agent/local/postgresql.pl <<<app-postgresql>>> version: 12 cCount: 0 tDbs: 0 tUsr: 0 tHst: 0 idle: 0 select: 0 update: 0 delete: 0 other: 0 Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 161 . xact_commit: Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 162 . xact_rollback: Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 163 . blks_read: Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 164 . blks_hit: Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 166 . tup_returned: Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 167 . tup_fetched: Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 168 . tup_inserted: Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 169 . tup_updated: Use of uninitialized value in concatenation (.) or string at /usr/lib/observium_agent/local/postgresql.pl line 170 . tup_deleted:

Observium Bot added a comment - 2020/10/09 10:52 AM Please make and attach additional information about the device: full snmp dump from device: snmpwalk -v2c -c <community> --hexOutputLength=0 -ObentxU <hostname> .1 > myagent.snmpwalk snmpwalk -v2c -c <community> --hexOutputLength=0 -ObentxU <hostname> .1.3.6.1.4.1 >> myagent.snmpwalk If device not support SNMP version 2c, replace -v2c with -v1. If you have problems with discovery or poller processes, please do and attach these debugs: ./discovery.php -d -h <device> ./poller.php -d -h <device> additionally attach device and/or vendor specific MIB files Note, this comment is added automatically.