Nagios Python Plugin Check If File Is Stale

July 27, 2017 adminLeave a comment

Wrote this simple plugin to check if a log file was stale on a server using nagios and nrpe. This plugin checks multiple files with the app. naming convention.

#!/usr/bin/env python

__author__ = "Jason Ralph"

""" IMPORTS """
import datetime
import time
import re
import sys
import os
import optparse
import os.path
import glob

""" NAGIOS API RETURN CODES """
nag_ret_dict = {'NagOk': 0,
                'NagWarn': 1,
                'NagCrit': 2,
                'NagUnkown': 3}

def main():
    for file in glob.glob('/var/log/httpd/app.*'):
        now = datetime.datetime.now()
        then = datetime.datetime.fromtimestamp(os.path.getmtime(file))
        tdelta = now - then
        if not file:
            print("OK: Waiting for file")
            sys.exit(nag_ret_dict['NagOk'])
        elif tdelta > datetime.timedelta(days=1):
            print("CRIT: %s: Is greater then %s") % (file, tdelta)
            sys.exit(nag_ret_dict['NagCrit'])
        else:
            print("OK: NOW: %s - THEN: %s - DELTA: %s" ) % (now, then, tdelta)
            sys.exit(nag_ret_dict['NagOk'])

if __name__ == "__main__":
    main()

#!/usr/bin/env python

__author__ = "Jason Ralph"

""" IMPORTS """

import datetime

import time

import re

import sys

import os

import optparse

import os.path

import glob

""" NAGIOS API RETURN CODES """

nag_ret_dict = {'NagOk': 0,

'NagWarn': 1,

'NagCrit': 2,

'NagUnkown': 3}

def main():

for file in glob.glob('/var/log/httpd/app.*'):

now = datetime.datetime.now()

then = datetime.datetime.fromtimestamp(os.path.getmtime(file))

tdelta = now - then

if not file:

print("OK: Waiting for file")

sys.exit(nag_ret_dict['NagOk'])

elif tdelta > datetime.timedelta(days=1):

print("CRIT: %s: Is greater then %s") % (file, tdelta)

sys.exit(nag_ret_dict['NagCrit'])

else:

print("OK: NOW: %s - THEN: %s - DELTA: %s" ) % (now, then, tdelta)

sys.exit(nag_ret_dict['NagOk'])

if __name__ == "__main__":

main()

POSTGRES – Top 100 Tables In Tablespace

May 30, 2017May 30, 2017 adminLeave a comment

I had a situation where I needed to find the top 100 largest tables in a certain tablespace on a postgres 9 database, in my case we archive tables into an archive1 tablespace. This query will find all the largest relations in the archive1 tablespace. Its important to swap out ‘archive1’ with whatever tablespace you are trying to list.

SELECT N.nspname || '.' || C.relname AS "relation",
    CASE WHEN reltype = 0
        THEN pg_size_pretty(pg_total_relation_size(C.oid)) || ' (index)'
        ELSE pg_size_pretty(pg_total_relation_size(C.oid)) || ' (' ||  pg_size_pretty(pg_relation_size(C.oid)) || ' data)'
    END AS "size (data)",
    COALESCE(T.tablespace, I.tablespace, '') AS "tablespace"
FROM pg_class C
LEFT JOIN pg_namespace N ON  (N.oid = C.relnamespace)
LEFT JOIN pg_tables T ON (T.tablename = C.relname)
LEFT JOIN pg_indexes I ON (I.indexname = C.relname)
LEFT JOIN pg_tablespace TS ON TS.spcname = T.tablespace
LEFT JOIN pg_tablespace XS ON XS.spcname = I.tablespace
WHERE nspname NOT IN ('pg_catalog','pg_toast','information_schema') 
AND COALESCE(T.tablespace, I.tablespace, '') = 'archive1'
ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 100;
                          relation                          |         size (data)          | tablespace
------------------------------------------------------------+------------------------------+------------
 public.table1                                              | 172 GB (148 GB data)         | archive1
 public.table2                                              | 171 GB (147 GB data)         | archive1
 public.table3                                              | 32 GB (32 GB data)           | archive1

--SNIP--

SELECT N.nspname || '.' || C.relname AS "relation",

CASE WHEN reltype = 0

THEN pg_size_pretty(pg_total_relation_size(C.oid)) || ' (index)'

ELSE pg_size_pretty(pg_total_relation_size(C.oid)) || ' (' || pg_size_pretty(pg_relation_size(C.oid)) || ' data)'

END AS "size (data)",

COALESCE(T.tablespace, I.tablespace, '') AS "tablespace"

FROM pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

LEFT JOIN pg_tables T ON (T.tablename = C.relname)

LEFT JOIN pg_indexes I ON (I.indexname = C.relname)

LEFT JOIN pg_tablespace TS ON TS.spcname = T.tablespace

LEFT JOIN pg_tablespace XS ON XS.spcname = I.tablespace

WHERE nspname NOT IN ('pg_catalog','pg_toast','information_schema')

AND COALESCE(T.tablespace, I.tablespace, '') = 'archive1'

ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 100;

relation | size (data) | tablespace

------------------------------------------------------------+------------------------------+------------

public.table1 | 172 GB (148 GB data) | archive1

public.table2 | 171 GB (147 GB data) | archive1

public.table3 | 32 GB (32 GB data) | archive1

--SNIP--

Hope this helps you out, took some time to get it to work.

Nagios Check Postgres Table Date Column Against now()

May 15, 2017May 30, 2017 adminLeave a comment

I had a situation where a daily sync of a table from one database to another was failing. This table was updated daily so the query should return something like this when it was synced correctly:

jasondb=# select date, count(*) from table group by date order by date desc limit 1;
    date    | count
------------+--------
 2017-05-15 | 486706
(1 row)

jasondb=# select date, count(*) from table group by date order by date desc limit 1;

date | count

------------+--------

2017-05-15 | 486706

(1 row)

I use Nagios very heavily and I setup a custom plugin to check the query’s date against today’s date, this should warn or critical based on user supplied arguments. Here is what a failure looks like when running from the nagios servers command line. This worked well at alerting me when the sync failed, this was integrated into the nagios subsystem and emails and slack alerts are generated as expected.

[11:54:03] [nagios@nagios ~] $ /usr/local/nagios/libexec/check_pg_table_DATE.py -u nagios -p secretpass -H hostproddb -d proddb -w 1 -c 2
DATETIME NEGATIVE DELTA: [-4 days, 0:00:00]
DATETIME NOW: [2017-05-15 00:00:00]
DATETIME NOW OF QUERY: [2017-05-11 00:00:00]
DATETIME CRIT VALUE: [2 days, 0:00:00]
DATETIME WARN VALUE: [1 day, 0:00:00]
NAGIOS CRIT: [-4 days, 0:00:00] is greater or equal to [2 days, 0:00:00]

[11:54:03] [nagios@nagios ~] $ /usr/local/nagios/libexec/check_pg_table_DATE.py -u nagios -p secretpass -H hostproddb -d proddb -w 1 -c 2

DATETIME NEGATIVE DELTA: [-4 days, 0:00:00]

DATETIME NOW: [2017-05-15 00:00:00]

DATETIME NOW OF QUERY: [2017-05-11 00:00:00]

DATETIME CRIT VALUE: [2 days, 0:00:00]

DATETIME WARN VALUE: [1 day, 0:00:00]

NAGIOS CRIT: [-4 days, 0:00:00] is greater or equal to [2 days, 0:00:00]

#!/usr/bin/python

""" MODULE IMPORTS """
""" THIS SCRIPT REQUIRES PSYCOPG2 - INSTALL WITH - 'sudo yum install python-psycopg2' """
import psycopg2
import string
import datetime
import time
import optparse
import sys
import os
import getpass

""" NAGIOS API RETURN CODES """
nag_ret_dict = {'NagOk': 0,
                'NagWarn': 1,
                'NagCrit': 2,
                'NagUnknown': 3}

""" COMMAND LINE OPTION PARSING """
usage = "USAGE: python %prog -d DATABASE -u DB_USER -H REMOTE_HOST -p DB_PASSWORD -w -2 -c -3"
parser = optparse.OptionParser(usage=usage)
parser.add_option('-d', action="store", default="postgres", help="Database selection", type="string")
parser.add_option('-u', action="store", default="postgres", help="DB User", type="string")
parser.add_option('-H', action="store", default="localhost", help="DB Host system", type="string")
parser.add_option('-p', action="store", default="", help="DB Password", type="string")
parser.add_option('-c', action="store", default="-3", help="Critical Date Thresh -nn", type="int")
parser.add_option('-w', action="store", default="-2", help="Warning Date Thresh -nn", type="int")
options, args = parser.parse_args()

""" ARG COUNT CHECK """
if len(sys.argv[1:]) == 0:
    parser.print_help()
    sys.exit(nag_ret_dict['NagCrit'])

""" TEST DATABASE CONNECTION """
try:
    conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'" \
    % (options.d, options.u, options.H, options.p))
except psycopg2.DatabaseError, e:
    print e
    sys.exit(nag_ret_dict['NagCrit'])

""" LOCAL SCOPE FUNCTIONS """
def query_pg_for_date():
    try:
        cur = conn.cursor()
        cur.execute("""select date, count(*) From table group by date order by date desc limit 1;""")
        row = cur.fetchone()
        date_s = row[0]
        cur.close()
        conn.close()
        return date_s.strftime("%Y%m%d")
    except psycopg2.DatabaseError, e:
        print ( 'Error %s' % e )
        parser.print_help()
        sys.exit(nag_ret_dict['nagiCrit'])

def get_date_now():
    date_now = time.strftime("%Y%m%d")
    return date_now.split()[0]

""" MAIN FUNCTION """
def main():
    queryDate  = datetime.datetime.strptime(str(query_pg_for_date()), "%Y%m%d")
    actualDate = datetime.datetime.strptime(str(get_date_now()), "%Y%m%d")
    deltaCrit = datetime.timedelta(days=options.c)
    deltaWarn = datetime.timedelta(days=options.w)
    diff = queryDate - actualDate
    print("DATETIME NEGATIVE DELTA: [%s]") % diff
    print("DATETIME NOW: [%s]")            % actualDate
    print("DATETIME NOW OF QUERY: [%s]")   % queryDate
    print("DATETIME CRIT VALUE: [%s]") % deltaCrit
    print("DATETIME WARN VALUE: [%s]")  % deltaWarn

    """ DEFINE LOGIC """
    if diff <= deltaCrit:
        print("NAGIOS CRIT: [%s] is greater or equal to [%s]") % (diff, deltaCrit)
        sys.exit(nag_ret_dict['NagCrit'])
    elif diff <= deltaWarn:
        print("NAGIOS WARN: [%s] is greater than or equal to [%s]") % (diff, deltaWarn)
        sys.exit(nag_ret_dict['NagWarn'])
    else:
        print("NAGIOS OK: [%s] is less than or equal to [%s]") % (diff, deltaCrit)
        sys.exit(nag_ret_dict['NagOk'])

if __name__ == "__main__":
    main()

#!/usr/bin/python

""" MODULE IMPORTS """

""" THIS SCRIPT REQUIRES PSYCOPG2 - INSTALL WITH - 'sudo yum install python-psycopg2' """

import psycopg2

import string

import datetime

import time

import optparse

import sys

import os

import getpass

""" NAGIOS API RETURN CODES """

nag_ret_dict = {'NagOk': 0,

'NagWarn': 1,

'NagCrit': 2,

'NagUnknown': 3}

""" COMMAND LINE OPTION PARSING """

usage = "USAGE: python %prog -d DATABASE -u DB_USER -H REMOTE_HOST -p DB_PASSWORD -w -2 -c -3"

parser = optparse.OptionParser(usage=usage)

parser.add_option('-d', action="store", default="postgres", help="Database selection", type="string")

parser.add_option('-u', action="store", default="postgres", help="DB User", type="string")

parser.add_option('-H', action="store", default="localhost", help="DB Host system", type="string")

parser.add_option('-p', action="store", default="", help="DB Password", type="string")

parser.add_option('-c', action="store", default="-3", help="Critical Date Thresh -nn", type="int")

parser.add_option('-w', action="store", default="-2", help="Warning Date Thresh -nn", type="int")

options, args = parser.parse_args()

""" ARG COUNT CHECK """

if len(sys.argv[1:]) == 0:

parser.print_help()

sys.exit(nag_ret_dict['NagCrit'])

""" TEST DATABASE CONNECTION """

try:

conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'" \

% (options.d, options.u, options.H, options.p))

except psycopg2.DatabaseError, e:

print e

sys.exit(nag_ret_dict['NagCrit'])

""" LOCAL SCOPE FUNCTIONS """

def query_pg_for_date():

try:

cur = conn.cursor()

cur.execute("""select date, count(*) From table group by date order by date desc limit 1;""")

row = cur.fetchone()

date_s = row[0]

cur.close()

conn.close()

return date_s.strftime("%Y%m%d")

except psycopg2.DatabaseError, e:

print ( 'Error %s' % e )

parser.print_help()

sys.exit(nag_ret_dict['nagiCrit'])

def get_date_now():

date_now = time.strftime("%Y%m%d")

return date_now.split()[0]

""" MAIN FUNCTION """

def main():

queryDate = datetime.datetime.strptime(str(query_pg_for_date()), "%Y%m%d")

actualDate = datetime.datetime.strptime(str(get_date_now()), "%Y%m%d")

deltaCrit = datetime.timedelta(days=options.c)

deltaWarn = datetime.timedelta(days=options.w)

diff = queryDate - actualDate

print("DATETIME NEGATIVE DELTA: [%s]") % diff

print("DATETIME NOW: [%s]") % actualDate

print("DATETIME NOW OF QUERY: [%s]") % queryDate

print("DATETIME CRIT VALUE: [%s]") % deltaCrit

print("DATETIME WARN VALUE: [%s]") % deltaWarn

""" DEFINE LOGIC """

if diff <= deltaCrit:

print("NAGIOS CRIT: [%s] is greater or equal to [%s]") % (diff, deltaCrit)

sys.exit(nag_ret_dict['NagCrit'])

elif diff <= deltaWarn:

print("NAGIOS WARN: [%s] is greater than or equal to [%s]") % (diff, deltaWarn)

sys.exit(nag_ret_dict['NagWarn'])

else:

print("NAGIOS OK: [%s] is less than or equal to [%s]") % (diff, deltaCrit)

sys.exit(nag_ret_dict['NagOk'])

if __name__ == "__main__":

main()

Nagios Python Plugin Check Lock File Exists

April 23, 2016November 22, 2016 adminLeave a comment

#!/usr/bin/python

import os.path
import time
import datetime
import sys
import optparse

"""Check the status of application lock file to be sure it is not stale, warn at critical at 18000 seconds warn at 10800"""
parser = optparse.OptionParser()
parser.add_option('-w', action="store", default="10800", help="seconds to warn", type="int")
parser.add_option('-c', action="store", default="18000", help="seconds to crit", type="int")
options, args = parser.parse_args()

nagOk = 0
nagWarn = 1
nagCrit = 2

try:
    statInfo = os.stat('/opt/application/.sync-lock')
except OSError:
    print("WAITING FOR LOCK FILE")
    sys.exit(nagOk)

now = int(time.time())
statInfoSlice = statInfo[8]
timeDiff = now - statInfoSlice
#print("NOW: %s") % now
#print("FILEAGE: %s") %  statInfoSlice
#print("TIMEDIFF: %s") % timeDiff

if timeDiff > options.c:
    print("CRIT: lock file is older than 5 hours")
    sys.exit(nagCrit)
elif timeDiff > options.w:
    print("WARN: lock file is older than 3 hours")
    sys.exit(nagWarn)
else:
    print("OK: lock file is under 3 hours")
    sys.exit(nagOk)

#!/usr/bin/python

import os.path

import time

import datetime

import sys

import optparse

"""Check the status of application lock file to be sure it is not stale, warn at critical at 18000 seconds warn at 10800"""

parser = optparse.OptionParser()

parser.add_option('-w', action="store", default="10800", help="seconds to warn", type="int")

parser.add_option('-c', action="store", default="18000", help="seconds to crit", type="int")

options, args = parser.parse_args()

nagOk = 0

nagWarn = 1

nagCrit = 2

try:

statInfo = os.stat('/opt/application/.sync-lock')

except OSError:

print("WAITING FOR LOCK FILE")

sys.exit(nagOk)

now = int(time.time())

statInfoSlice = statInfo[8]

timeDiff = now - statInfoSlice

#print("NOW: %s") % now

#print("FILEAGE: %s") % statInfoSlice

#print("TIMEDIFF: %s") % timeDiff

if timeDiff > options.c:

print("CRIT: lock file is older than 5 hours")

sys.exit(nagCrit)

elif timeDiff > options.w:

print("WARN: lock file is older than 3 hours")

sys.exit(nagWarn)

else:

print("OK: lock file is under 3 hours")

sys.exit(nagOk)

Code Highlighting Test

February 15, 2016February 15, 2016 adminLeave a comment

Jason R. Ralph

Linux All Day Everyday

Category: General Code

Nagios Python Plugin Check If File Is Stale

POSTGRES – Top 100 Tables In Tablespace

Nagios Check Postgres Table Date Column Against now()

Nagios Python Plugin Check Lock File Exists

Code Highlighting Test