{"id":539,"date":"2017-05-15T11:58:56","date_gmt":"2017-05-15T15:58:56","guid":{"rendered":"http:\/\/jasonralph.org\/?p=539"},"modified":"2017-05-30T16:29:22","modified_gmt":"2017-05-30T20:29:22","slug":"nagios-check-table-date-column-against-now","status":"publish","type":"post","link":"https:\/\/jasonralph.org\/?p=539","title":{"rendered":"Nagios Check Postgres Table Date Column Against now()"},"content":{"rendered":"<p>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:<\/p>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\njasondb=# select date, count(*) from table group by date order by date desc limit 1;\r\n    date    | count\r\n------------+--------\r\n 2017-05-15 | 486706\r\n(1 row)\r\n<\/pre>\n<p>I use Nagios very heavily and I setup a custom plugin to check the query&#8217;s date against today&#8217;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.  <\/p>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\n[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\r\nDATETIME NEGATIVE DELTA: [-4 days, 0:00:00]\r\nDATETIME NOW: [2017-05-15 00:00:00]\r\nDATETIME NOW OF QUERY: [2017-05-11 00:00:00]\r\nDATETIME CRIT VALUE: [2 days, 0:00:00]\r\nDATETIME WARN VALUE: [1 day, 0:00:00]\r\nNAGIOS CRIT: [-4 days, 0:00:00] is greater or equal to [2 days, 0:00:00]\r\n<\/pre>\n<pre class=\"theme:solarized-dark lang:default decode:true \" >\r\n#!\/usr\/bin\/python\r\n\r\n\"\"\" MODULE IMPORTS \"\"\"\r\n\"\"\" THIS SCRIPT REQUIRES PSYCOPG2 - INSTALL WITH - 'sudo yum install python-psycopg2' \"\"\"\r\nimport psycopg2\r\nimport string\r\nimport datetime\r\nimport time\r\nimport optparse\r\nimport sys\r\nimport os\r\nimport getpass\r\n\r\n\"\"\" NAGIOS API RETURN CODES \"\"\"\r\nnag_ret_dict = {'NagOk': 0,\r\n                'NagWarn': 1,\r\n                'NagCrit': 2,\r\n                'NagUnknown': 3}\r\n\r\n\"\"\" COMMAND LINE OPTION PARSING \"\"\"\r\nusage = \"USAGE: python %prog -d DATABASE -u DB_USER -H REMOTE_HOST -p DB_PASSWORD -w -2 -c -3\"\r\nparser = optparse.OptionParser(usage=usage)\r\nparser.add_option('-d', action=\"store\", default=\"postgres\", help=\"Database selection\", type=\"string\")\r\nparser.add_option('-u', action=\"store\", default=\"postgres\", help=\"DB User\", type=\"string\")\r\nparser.add_option('-H', action=\"store\", default=\"localhost\", help=\"DB Host system\", type=\"string\")\r\nparser.add_option('-p', action=\"store\", default=\"\", help=\"DB Password\", type=\"string\")\r\nparser.add_option('-c', action=\"store\", default=\"-3\", help=\"Critical Date Thresh -nn\", type=\"int\")\r\nparser.add_option('-w', action=\"store\", default=\"-2\", help=\"Warning Date Thresh -nn\", type=\"int\")\r\noptions, args = parser.parse_args()\r\n\r\n\"\"\" ARG COUNT CHECK \"\"\"\r\nif len(sys.argv[1:]) == 0:\r\n    parser.print_help()\r\n    sys.exit(nag_ret_dict['NagCrit'])\r\n\r\n\"\"\" TEST DATABASE CONNECTION \"\"\"\r\ntry:\r\n    conn = psycopg2.connect(\"dbname='%s' user='%s' host='%s' password='%s'\" \\\r\n    % (options.d, options.u, options.H, options.p))\r\nexcept psycopg2.DatabaseError, e:\r\n    print e\r\n    sys.exit(nag_ret_dict['NagCrit'])\r\n\r\n\"\"\" LOCAL SCOPE FUNCTIONS \"\"\"\r\ndef query_pg_for_date():\r\n    try:\r\n        cur = conn.cursor()\r\n        cur.execute(\"\"\"select date, count(*) From table group by date order by date desc limit 1;\"\"\")\r\n        row = cur.fetchone()\r\n        date_s = row[0]\r\n        cur.close()\r\n        conn.close()\r\n        return date_s.strftime(\"%Y%m%d\")\r\n    except psycopg2.DatabaseError, e:\r\n        print ( 'Error %s' % e )\r\n        parser.print_help()\r\n        sys.exit(nag_ret_dict['nagiCrit'])\r\n\r\ndef get_date_now():\r\n    date_now = time.strftime(\"%Y%m%d\")\r\n    return date_now.split()[0]\r\n\r\n\"\"\" MAIN FUNCTION \"\"\"\r\ndef main():\r\n    queryDate  = datetime.datetime.strptime(str(query_pg_for_date()), \"%Y%m%d\")\r\n    actualDate = datetime.datetime.strptime(str(get_date_now()), \"%Y%m%d\")\r\n    deltaCrit = datetime.timedelta(days=options.c)\r\n    deltaWarn = datetime.timedelta(days=options.w)\r\n    diff = queryDate - actualDate\r\n    print(\"DATETIME NEGATIVE DELTA: [%s]\") % diff\r\n    print(\"DATETIME NOW: [%s]\")            % actualDate\r\n    print(\"DATETIME NOW OF QUERY: [%s]\")   % queryDate\r\n    print(\"DATETIME CRIT VALUE: [%s]\") % deltaCrit\r\n    print(\"DATETIME WARN VALUE: [%s]\")  % deltaWarn\r\n\r\n    \"\"\" DEFINE LOGIC \"\"\"\r\n    if diff <= deltaCrit:\r\n        print(\"NAGIOS CRIT: [%s] is greater or equal to [%s]\") % (diff, deltaCrit)\r\n        sys.exit(nag_ret_dict['NagCrit'])\r\n    elif diff <= deltaWarn:\r\n        print(\"NAGIOS WARN: [%s] is greater than or equal to [%s]\") % (diff, deltaWarn)\r\n        sys.exit(nag_ret_dict['NagWarn'])\r\n    else:\r\n        print(\"NAGIOS OK: [%s] is less than or equal to [%s]\") % (diff, deltaCrit)\r\n        sys.exit(nag_ret_dict['NagOk'])\r\n\r\nif __name__ == \"__main__\":\r\n    main()\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8211; [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,4],"tags":[15,51,24,52],"class_list":["post-539","post","type-post","status-publish","format-standard","hentry","category-general-code","category-python","tag-nagios","tag-postgres","tag-python-2","tag-sql"],"_links":{"self":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/539","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=539"}],"version-history":[{"count":9,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/539\/revisions"}],"predecessor-version":[{"id":550,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/539\/revisions\/550"}],"wp:attachment":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}