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 AWS REDSHIFT TABLE COUNT PLUGIN PYTHON

November 22, 2016November 25, 2016 adminLeave a comment

I needed a quick plugin to warn me if one of our AWS REDSHIFT instances had a table count above 6000 and alert critical if above 7000. I decided to write a python plugin for nagios to do the chore. You can see the source code and the example of executing it below on the nagios host.

WARN = 6000
CRIT = 7000

1 2	WARN = 6000 CRIT = 7000

[root@devnagios ~]# /usr/bin/python /usr/local/nagios/libexec/check_rs_table_count.py -d awsdevdb \
-H aws-server.redshift.amazonaws.com -u nagios -p password -w 6000 -c 7000
OK!: table count: 694

[root@devnagios ~]# /usr/bin/python /usr/local/nagios/libexec/check_rs_table_count.py -d awsdevdb \

-H aws-server.redshift.amazonaws.com -u nagios -p password -w 6000 -c 7000

OK!: table count: 694

[root@devnagios ~]# /usr/bin/python /usr/local/nagios/libexec/check_rs_table_count.py -d awsdevdb \
-H aws-server.redshift.amazonaws.com -u nagios -p password -w 100 -c 200
CRIT!: table count: 694

[root@devnagios ~]# /usr/bin/python /usr/local/nagios/libexec/check_rs_table_count.py -d awsdevdb \

-H aws-server.redshift.amazonaws.com -u nagios -p password -w 100 -c 200

CRIT!: table count: 694

#!/usr/bin/python
""" MODULE IMPORTS """
import psycopg2
import optparse
import sys

""" 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 -H REMOTE_HOST -u USER -p DB_PASSWORD -w 6000 -c 7000"
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('-w', action="store", default="-2", help="Warning threshold int", type="int")
parser.add_option('-c', action="store", default="-2", help="critical threshold int", 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' port='5439'" \
    % (options.d, options.u, options.H, options.p))
except psycopg2.DatabaseError, e:
    print('CRIT: Error %s' % e)
    sys.exit(nag_ret_dict['NagCrit'])

""" LOCAL SCOPE FUNCTIONS """
def query_rs():
    try:
        cur = conn.cursor()
        cur.execute("""select count(*) from pg_tables;""")
        result = cur.fetchone()
        cast_result = int(result[0])
        warn_thresh = options.w
        crit_thresh = options.c
        if cast_result >= crit_thresh:
            print('CRIT: table count: %d') % result
            sys.exit(nag_ret_dict['NagCrit'])
        elif cast_result >= warn_thresh:
            print ('WARN: table count: %d') % result
            sys.exit(nag_ret_dict['NagWarn'])
        else:
            print('OK!: tables count: %d') % result
    except psycopg2.DatabaseError, e:
        return ('CRIT: Error %s' % e)
        sys.exit(nag_ret_dict['NagCrit'])
    cur.close()
    conn.close()

""" MAIN FUNCTION """
def main():
        query_rs()

if __name__ == "__main__":
    main()

#!/usr/bin/python

""" MODULE IMPORTS """

import psycopg2

import optparse

import sys

""" 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 -H REMOTE_HOST -u USER -p DB_PASSWORD -w 6000 -c 7000"

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('-w', action="store", default="-2", help="Warning threshold int", type="int")

parser.add_option('-c', action="store", default="-2", help="critical threshold int", 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' port='5439'" \

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

except psycopg2.DatabaseError, e:

print('CRIT: Error %s' % e)

sys.exit(nag_ret_dict['NagCrit'])

""" LOCAL SCOPE FUNCTIONS """

def query_rs():

try:

cur = conn.cursor()

cur.execute("""select count(*) from pg_tables;""")

result = cur.fetchone()

cast_result = int(result[0])

warn_thresh = options.w

crit_thresh = options.c

if cast_result >= crit_thresh:

print('CRIT: table count: %d') % result

sys.exit(nag_ret_dict['NagCrit'])

elif cast_result >= warn_thresh:

print ('WARN: table count: %d') % result

sys.exit(nag_ret_dict['NagWarn'])

else:

print('OK!: tables count: %d') % result

except psycopg2.DatabaseError, e:

return ('CRIT: Error %s' % e)

sys.exit(nag_ret_dict['NagCrit'])

cur.close()

conn.close()

""" MAIN FUNCTION """

def main():

query_rs()

if __name__ == "__main__":

main()

Python Backup WORDPRESS Site / DATABASE and HTML

May 7, 2016May 7, 2016 adminLeave a comment

I have this blog hosted on a LINODE dedicated LINUX server. It’s about 10 dollars a month for a 1 core system with about 250GB of disk space and 1GB of RAM, this server runs the common LAMP stack, I needed a quick and dirty script to backup MYSQL database and the PHP code contained in the /var/www/html folder. I wanted the script to compress the contents of both and move them into a directory with the correct date. See the comments below outlining the code and the action of running it.

# Linux server kernel version details.
[22:47:51] root@jasonralph:~/py_backup_site_full # uname -a
Linux jasonralph.jasonralph.org 4.5.0-x86_64-linode65 #2 SMP Mon Mar 14 18:01:58 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

# Linux distro information
[22:52:15] root@jasonralph:~/py_backup_site_full # cat /etc/centos-release
CentOS release 6.7 (Final)

# Free output
[22:59:58] root@jasonralph:~/py_backup_site_full # free -m
             total       used       free     shared    buffers     cached
Mem:           991        796        195          0         62        329
-/+ buffers/cache:        403        587
Swap:          511          0        511

# Here you can see the python binary location on the filesystem.
[22:46:36] root@jasonralph:~/py_backup_site_full # which python
/usr/local/bin/python

# Now you can see the python version I am using. 
[22:46:50] root@jasonralph:~/py_backup_site_full # python --version
Python 2.7.6

# Long listing of the backup folder I have setup. 
[22:47:48] root@jasonralph:~/py_backup_site_full # ls -l
total 4
-rwxr-xr-x 1 root root 1560 May  7 22:46 wordpress_backup.py

# Linux server kernel version details.

[22:47:51] root@jasonralph:~/py_backup_site_full # uname -a

Linux jasonralph.jasonralph.org 4.5.0-x86_64-linode65 #2 SMP Mon Mar 14 18:01:58 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

# Linux distro information

[22:52:15] root@jasonralph:~/py_backup_site_full # cat /etc/centos-release

CentOS release 6.7 (Final)

# Free output

[22:59:58] root@jasonralph:~/py_backup_site_full # free -m

total used free shared buffers cached

Mem: 991 796 195 0 62 329

-/+ buffers/cache: 403 587

Swap: 511 0 511

# Here you can see the python binary location on the filesystem.

[22:46:36] root@jasonralph:~/py_backup_site_full # which python

/usr/local/bin/python

# Now you can see the python version I am using.

[22:46:50] root@jasonralph:~/py_backup_site_full # python --version

Python 2.7.6

# Long listing of the backup folder I have setup.

[22:47:48] root@jasonralph:~/py_backup_site_full # ls -l

total 4

-rwxr-xr-x 1 root root 1560 May 7 22:46 wordpress_backup.py

# cat of the python script to see the source code. 

[23:02:39] root@jasonralph:~/py_backup_site_full # cat wordpress_backup.py
#!/usr/local/bin/python
import optparse
import os
import datetime
import shutil
from subprocess import Popen, PIPE

date = datetime.datetime.now().strftime('%Y%m%d-%s')
f_date = datetime.datetime.now().strftime('%Y%m%d')

def backup_all_databases():
    args = ['mysqldump', '-u', 'root', '-pPASSWORD', '--all-databases']
    with open("%s.sql.gz" % f_date, 'wb') as f:
        p1 = Popen(args, stdout=PIPE)
        p2 = Popen('gzip', stdin=p1.stdout, stdout=f)
        p1.stdout.close()
        p2.wait()
        p1.wait()

def tar_html_folder():
    output_filename_1 = "%s.html_dir"  % f_date
    output_filename_2 = "%s.html_dir.zip"  % f_date
    dir_name = '/var/www/html'
    dst = "%s" % date
    shutil.make_archive(output_filename_1, 'zip', dir_name)
    shutil.move(output_filename_2, dst)

def main():
    archive_path = "%s" % date
    os.mkdir(archive_path, 0755)
    backup_all_databases()
    src_file = "%s.sql.gz" % f_date
    dst = "%s" % date
    shutil.move(src_file, dst)
    tar_html_folder()

if __name__ == "__main__":
    main()

# cat of the python script to see the source code.

[23:02:39] root@jasonralph:~/py_backup_site_full # cat wordpress_backup.py

#!/usr/local/bin/python

import optparse

import os

import datetime

import shutil

from subprocess import Popen, PIPE

date = datetime.datetime.now().strftime('%Y%m%d-%s')

f_date = datetime.datetime.now().strftime('%Y%m%d')

def backup_all_databases():

args = ['mysqldump', '-u', 'root', '-pPASSWORD', '--all-databases']

with open("%s.sql.gz" % f_date, 'wb') as f:

p1 = Popen(args, stdout=PIPE)

p2 = Popen('gzip', stdin=p1.stdout, stdout=f)

p1.stdout.close()

p2.wait()

p1.wait()

def tar_html_folder():

output_filename_1 = "%s.html_dir" % f_date

output_filename_2 = "%s.html_dir.zip" % f_date

dir_name = '/var/www/html'

dst = "%s" % date

shutil.make_archive(output_filename_1, 'zip', dir_name)

shutil.move(output_filename_2, dst)

def main():

archive_path = "%s" % date

os.mkdir(archive_path, 0755)

backup_all_databases()

src_file = "%s.sql.gz" % f_date

dst = "%s" % date

shutil.move(src_file, dst)

tar_html_folder()

if __name__ == "__main__":

main()

# Lets execute the script and check the contents of the directory it creates. 
[23:02:41] root@jasonralph:~/py_backup_site_full # python wordpress_backup.py
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

# Long listing of the directoy.
[23:03:47] root@jasonralph:~/py_backup_site_full # ls -l
total 8
drwxr-xr-x 2 root root 4096 May  7 23:03 20160507-1462676621
-rwxr-xr-x 1 root root 1059 May  7 23:02 wordpress_backup.py

# Change directory to the newly created folder. 
[23:03:50] root@jasonralph:~/py_backup_site_full # cd 20160507-1462676621/

# Long listing of the files in the newly created folder. 
[23:03:55] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls -l
total 79084
-rw-r--r-- 1 root root 80614105 May  7 23:03 20160507.html_dir.zip
-rw-r--r-- 1 root root   360709 May  7 23:03 20160507.sql.gz

# Lets execute the script and check the contents of the directory it creates.

[23:02:41] root@jasonralph:~/py_backup_site_full # python wordpress_backup.py

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

# Long listing of the directoy.

[23:03:47] root@jasonralph:~/py_backup_site_full # ls -l

total 8

drwxr-xr-x 2 root root 4096 May 7 23:03 20160507-1462676621

-rwxr-xr-x 1 root root 1059 May 7 23:02 wordpress_backup.py

# Change directory to the newly created folder.

[23:03:50] root@jasonralph:~/py_backup_site_full # cd 20160507-1462676621/

# Long listing of the files in the newly created folder.

[23:03:55] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls -l

total 79084

-rw-r--r-- 1 root root 80614105 May 7 23:03 20160507.html_dir.zip

-rw-r--r-- 1 root root 360709 May 7 23:03 20160507.sql.gz

So you can see we generated 2 files in a dated directory, I chose to use both zip and gunzip for compression algoritims. To view the contents you can run the normal linux commands to extract the files.

# unzipping the files using gunzip and zip
[23:03:55] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # unzip 20160507.html_dir.zip && gunzip 20160507.sql.gz

# Long listing of the files 
[23:08:31] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls -l
total 80660
-rw-r--r-- 1 root root 80614105 May  7 23:03 20160507.html_dir.zip
-rw-r--r-- 1 root root  1973075 May  7 23:03 20160507.sql
drwxr-xr-x 3 root root     4096 May  7 23:08 jasonralph

# Head command showing the first 10 lines of the sql file. 
[23:11:33] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # head -n 10 20160507.sql
-- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       5.1.73

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

# Long listing of the jasonralph folder in /var/www/html
[23:12:48] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls jasonralph/wordpress/
black_cat_blink.gif  image.jpg    MW_Line.mp4  rh.jpg               wp-admin              wp-config.php         wp-content   wp-links-opml.php  wp-mail.php      wp-settings.php   xmlrpc.php
delete_file.mvg      index.php    out.png      tux_linux_white.jpg  wp-blog-header.php    wp-config.php.bkup    wp-cron.php  wp-load.php        wp-pass.php      wp-signup.php
expolit.svg          license.txt  readme.html  wp-activate.php      wp-comments-post.php  wp-config-sample.php  wp-includes  wp-login.php       wp-register.php  wp-trackback.php

# unzipping the files using gunzip and zip

[23:03:55] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # unzip 20160507.html_dir.zip && gunzip 20160507.sql.gz

# Long listing of the files

[23:08:31] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls -l

total 80660

-rw-r--r-- 1 root root 80614105 May 7 23:03 20160507.html_dir.zip

-rw-r--r-- 1 root root 1973075 May 7 23:03 20160507.sql

drwxr-xr-x 3 root root 4096 May 7 23:08 jasonralph

# Head command showing the first 10 lines of the sql file.

[23:11:33] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # head -n 10 20160507.sql

-- MySQL dump 10.13 Distrib 5.1.73, for redhat-linux-gnu (x86_64)

-- Host: localhost Database:

-- ------------------------------------------------------

-- Server version 5.1.73

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

# Long listing of the jasonralph folder in /var/www/html

[23:12:48] root@jasonralph:~/py_backup_site_full/20160507-1462676621 # ls jasonralph/wordpress/

black_cat_blink.gif image.jpg MW_Line.mp4 rh.jpg wp-admin wp-config.php wp-content wp-links-opml.php wp-mail.php wp-settings.php xmlrpc.php

delete_file.mvg index.php out.png tux_linux_white.jpg wp-blog-header.php wp-config.php.bkup wp-cron.php wp-load.php wp-pass.php wp-signup.php

expolit.svg license.txt readme.html wp-activate.php wp-comments-post.php wp-config-sample.php wp-includes wp-login.php wp-register.php wp-trackback.php

So there you have it, I can tar up the entire dated directory for easy offsite backup now of my entire site jasonralph.org. Hope this helps someone, feel free to copy the source code and change at will.

Best,
Jason

Python Mysql Connector

April 27, 2016October 13, 2017 admin1 Comment

Thought I would try my hand at some SQL programming with Python, I was stuck using a Windows machine(BLAH) I wanted to setup a MySQL database and 1 table for testing. I am on a Windows machine so I installed WAMP which is a Windows Apache Mysql Php server. You can get the installer for Windows here:
http://www.wampserver.com/en/

You can get the python library / module here:

Mysql Python
Using PhpMyAdmin I setup a database and added the table.

mysql

As you can see I created a function that added data into my new database. I pass it 4 parameters, id-name-dept-salary, when you execute this script the database gets populated with the correct data. Pretty cool!!

#!/usr/bin/python
from mysql import connector
import optparse

parser = optparse.OptionParser()
parser.add_option('-i', action="store", help="Enter Job ID#", type="int")
parser.add_option('-n', action="store", help="Enter Employee Name", type="string")
parser.add_option('-d', action="store", help="Enter Department", type="string")
parser.add_option('-s', action="store", help="Enter Salary", type="int")
options, args = parser.parse_args()

def databaseAdd(id,name,dept,salary):
    con = connector.Connect(user='jason',
                            password='password',
                            database='jason',
                            host='localhost')
    c = con.cursor()
    table_name = "employee"
    insert = "INSERT INTO " + table_name + " (id,name,dept,salary) \
    VALUES (%s, %s, %s, %s)"
    data_value = (id,name,dept,salary)
    c.execute(insert, data_value)
    con.commit()
    c.close()
    con.close()

databaseAdd(options.i ,options.n,options.d,options.s)

#!/usr/bin/python

from mysql import connector

import optparse

parser = optparse.OptionParser()

parser.add_option('-i', action="store", help="Enter Job ID#", type="int")

parser.add_option('-n', action="store", help="Enter Employee Name", type="string")

parser.add_option('-d', action="store", help="Enter Department", type="string")

parser.add_option('-s', action="store", help="Enter Salary", type="int")

options, args = parser.parse_args()

def databaseAdd(id,name,dept,salary):

con = connector.Connect(user='jason',

password='password',

database='jason',

host='localhost')

c = con.cursor()

table_name = "employee"

insert = "INSERT INTO " + table_name + " (id,name,dept,salary) \

VALUES (%s, %s, %s, %s)"

data_value = (id,name,dept,salary)

c.execute(insert, data_value)

con.commit()

c.close()

con.close()

databaseAdd(options.i ,options.n,options.d,options.s)

Here is how you can see the help and call the command properly:
help command

Here is what you can see when performing a select * from the table.
database command

Here is the awesome IDE pyCharm and the results from running the code.
pycharm
Here is an example of how to query that database from the table.

from mysql import connector
import sys
import optparse


parser = optparse.OptionParser()
parser.add_option('--id', action="store", help="Enter ID number", type="int")
options, args = parser.parse_args()


try:
    cnx = connector.Connect(user='jason',
                        password='password',
                        database='jason',
                        host='localhost')
except connector.Error as err:
    print "Connection to Database Failed"
    print err
    sys.exit(1)


cursor = cnx.cursor()
query = "SELECT * from employee WHERE id='%s'" % options.id
cursor.execute(query)
for row in cursor:
    print row

from mysql import connector

import sys

import optparse

parser = optparse.OptionParser()

parser.add_option('--id', action="store", help="Enter ID number", type="int")

options, args = parser.parse_args()

try:

cnx = connector.Connect(user='jason',

password='password',

database='jason',

host='localhost')

except connector.Error as err:

print "Connection to Database Failed"

print err

sys.exit(1)

cursor = cnx.cursor()

query = "SELECT * from employee WHERE id='%s'" % options.id

cursor.execute(query)

for row in cursor:

print row

Here is the output of the fetch command using ID as a parameter:
database command

Took me a while to figure out the syntax, so hopefully this helps someone.
Jason

PYTHON – Script to download youtube videos for offline viewing

April 3, 2016April 24, 2016 adminLeave a comment

I was interested in viewing this video of a news conference (USENIX 2016) on my trip home on Metro North Train, NYC => CT. The trip is about an hour an 10 minutes from Manhattan’s Grand Central Terminal to Milford CT, express train that is. My concern was that I would have choppy internet service on the way since I recently updated my laptop and the built in Verizon Mobile card was not activated yet. I would need to use my ATT iPhone as a hotspot, which proved to be very shakey at times. A colleague of mine recommended a website for making youtube videos available for offline viewing. The name of this site was:

http://www.keepvid.com

Right off the rip I was concerned that this site was infested with malware and any other bullshit associated with a free video ripping service. I used the site and was able to create a download of the video I was interested in, however who knows how sick my Windows based machine just got. I could of contracted anything from this site.

I thought about this and said, there has to be a better way, or a python lib for this, and low and behold a search came up with PYTUBE:
https://github.com/nficano/pytube

This library had some interesting features and literally blew away the keepvid site in regards to flexibility. Here is some explaining of what this library can do. Please have a look at the examples below, I will do my best to narrate them.

Here I use PIP to install the PYTUBE lib, you can ignore the DEPRECATION: warning for my outdated python that blares at you for being such an idiot.

[root@jasonralph ~]# pip install pytube
DEPRECATION: Python 2.6 is no longer supported by the Python core team, please upgrade your Python. A future version of pip will drop support for Python 2.6
Collecting pytube
  Using cached pytube-6.1.8.tar.gz
Installing collected packages: pytube
  Running setup.py install for pytube ... done
Successfully installed pytube-6.1.8

[root@jasonralph ~]# pip install pytube

DEPRECATION: Python 2.6 is no longer supported by the Python core team, please upgrade your Python. A future version of pip will drop support for Python 2.6

Collecting pytube

Using cached pytube-6.1.8.tar.gz

Installing collected packages: pytube

Running setup.py install for pytube ... done

Successfully installed pytube-6.1.8

Next up you can see that I am setting a variable yt(this is the video you want to download). Using python’s Pretty Print Lib you can run the pprint(yt.get_videos() method to see what formats are available for download.

Please have a look at the comments in the code for a bit more details in regards to what is going on, in this example I am using the filename Pulp_Fiction.mp4 for my filename I want to be when downloaded.

[jralph@jasonralph ~]$ cat py_video_downloader.py
from pytube import YouTube
from pprint import pprint

yt = YouTube("http://www.youtube.com/watch?v=Ik-RsDGPI5Y")

pprint(yt.get_videos())

print(yt.filename)

yt.set_filename('Pulp_Fiction.mp4')

# Notice that the list is ordered by lowest resolution to highest. If you
# wanted the highest resolution available for a specific file type, you
# can simply do:
print(yt.filter('mp4')[-1])
# <Video: H.264 (.mp4) - 720p>

# You can also get all videos for a given resolution
pprint(yt.filter(resolution='720p'))


video = yt.get('mp4', '720p')

# NOTE: get() can only be used if and only if one object matches your criteria.
# for example:

pprint(yt.videos)


video.download('/home/jralph/')

[jralph@jasonralph ~]$ cat py_video_downloader.py

from pytube import YouTube

from pprint import pprint

yt = YouTube("http://www.youtube.com/watch?v=Ik-RsDGPI5Y")

pprint(yt.get_videos())

print(yt.filename)

yt.set_filename('Pulp_Fiction.mp4')

# Notice that the list is ordered by lowest resolution to highest. If you

# wanted the highest resolution available for a specific file type, you

# can simply do:

print(yt.filter('mp4')[-1])

# <Video: H.264 (.mp4) - 720p>

# You can also get all videos for a given resolution

pprint(yt.filter(resolution='720p'))

video = yt.get('mp4', '720p')

# NOTE: get() can only be used if and only if one object matches your criteria.

# for example:

pprint(yt.videos)

video.download('/home/jralph/')

Ok so here is what it looks like when you execute the program:

[23:32:59] jralph@jasonralph:~ $ python py_video_downloader.py
[<Video: MPEG-4 Visual (.3gp) - 144p - Simple>,
 <Video: MPEG-4 Visual (.3gp) - 240p - Simple>,
 <Video: Sorenson H.263 (.flv) - 240p - N/A>,
 <Video: H.264 (.mp4) - 360p - Baseline>,
 <Video: H.264 (.mp4) - 720p - High>,
 <Video: VP8 (.webm) - 360p - N/A>]
Pulp Fiction - Dancing Scene
<Video: H.264 (.mp4) - 720p - High>
[<Video: H.264 (.mp4) - 720p - High>]
/usr/lib/python2.6/site-packages/pytube/api.py:141: DeprecationWarning: videos property deprecated. Use `get_videos()` instead.
  "instead.", DeprecationWarning)
[<Video: MPEG-4 Visual (.3gp) - 144p - Simple>,
 <Video: MPEG-4 Visual (.3gp) - 240p - Simple>,
 <Video: Sorenson H.263 (.flv) - 240p - N/A>,
 <Video: H.264 (.mp4) - 360p - Baseline>,
 <Video: H.264 (.mp4) - 720p - High>,
 <Video: VP8 (.webm) - 360p - N/A>]

[23:32:59] jralph@jasonralph:~ $ python py_video_downloader.py

[<Video: MPEG-4 Visual (.3gp) - 144p - Simple>,

<Video: MPEG-4 Visual (.3gp) - 240p - Simple>,

<Video: Sorenson H.263 (.flv) - 240p - N/A>,

<Video: H.264 (.mp4) - 360p - Baseline>,

<Video: H.264 (.mp4) - 720p - High>,

<Video: VP8 (.webm) - 360p - N/A>]

Pulp Fiction - Dancing Scene

<Video: H.264 (.mp4) - 720p - High>

[<Video: H.264 (.mp4) - 720p - High>]

/usr/lib/python2.6/site-packages/pytube/api.py:141: DeprecationWarning: videos property deprecated. Use `get_videos()` instead.

"instead.", DeprecationWarning)

[<Video: MPEG-4 Visual (.3gp) - 144p - Simple>,

<Video: MPEG-4 Visual (.3gp) - 240p - Simple>,

<Video: Sorenson H.263 (.flv) - 240p - N/A>,

<Video: H.264 (.mp4) - 360p - Baseline>,

<Video: H.264 (.mp4) - 720p - High>,

<Video: VP8 (.webm) - 360p - N/A>]

As you can see we have a new filename with the video we asked for to watch without a streaming internet connection, here is a ls to show:

[23:33:02] jralph@jasonralph:~ $ ls -ltr
total 32352
drwxr-xr-x 2 root   root       4096 Mar 20 22:50 image_staging
drwxr-xr-x 2 root   root       4096 Mar 20 22:51 JR.ORG_SITE_BACKUPS
-rwxrwxr-x 1 jralph jralph      691 Apr  3 00:34 py_video_downloader.py
-rw-rw-r-- 1 jralph jralph 33113243 Apr  3 23:33 Pulp_Fiction.mp4.mp4

[23:33:02] jralph@jasonralph:~ $ ls -ltr

total 32352

drwxr-xr-x 2 root root 4096 Mar 20 22:50 image_staging

drwxr-xr-x 2 root root 4096 Mar 20 22:51 JR.ORG_SITE_BACKUPS

-rwxrwxr-x 1 jralph jralph 691 Apr 3 00:34 py_video_downloader.py

-rw-rw-r-- 1 jralph jralph 33113243 Apr 3 23:33 Pulp_Fiction.mp4.mp4

As always, I am sure there are better ways to do this and I am sure there is cleaner code. Most of this code was taken right from the authors site who is a badass, here is his link:

https://github.com/nficano/pytube

Hope you liked,
J$0N

Daily Weather Email – PYTHON – CRON – API

April 1, 2016April 28, 2016 adminLeave a comment

Jason R. Ralph

Linux All Day Everyday

Tag: python

Nagios Check Postgres Table Date Column Against now()

NAGIOS AWS REDSHIFT TABLE COUNT PLUGIN PYTHON

Python Backup WORDPRESS Site / DATABASE and HTML

Python Mysql Connector

PYTHON – Script to download youtube videos for offline viewing

Daily Weather Email – PYTHON – CRON – API