Python /PostgresqSQL 9.4 – Server Performance Data Capture V.2

In this second version, the scripts which capture data for the servers are using classes. In addition the the script that captures dynamic data from the remote servers captures additional data. In specific it captures RAM, HD usage in raw numbers and not only “humanized” format. The humanized fields were not taken out. As a result the appropriate tables had to be modified, which means the schema has changed as well. Also, the database and the scripts are now being installed via puppet. The Puppet manifests are far from polished. They need more work but they do work.

These scripts gather static and dynamic information from servers and insert that data into a PostgresSQL database. The static information is information that unless a major upgrade takes place it never changes. They Dynamic data is performance data of the servers. The purpose of the static data is to be able to query for dynamic information which being inserted to the database every X amount of minutes via cron.

These scripts work only on Linux/Unix based machines.

The PUPPET modules used to install the Database and the scripts are located here

The static information for the remote servers are as follows :

hostname
iface
ipaddress
OS
OSRel
OSKern
total_M
brand
Hz
cores
arch

The dynamic information for the remote servers are as follows :

hostname
iface
ipaddress
total_ram_hum
used_ram_hum
total_ram_raw
used_ram_raw
used_ram_perc
total_HD_hum
used_HD_hum
total_HD_raw
used_HD_raw
used_HD_perc
cpu_use_perc
swap_used_hum
swap_total_hum
swap_perc
swap_used_raw
swap_total_raw

The static.py script will need to be run only one time on the remote servers or when a major upgrade occurs that might change configuration regarding RAM, Partitions, IP, Operating System (even an upgrade), CPU, NIC replacement.

The dynamic.py script will be run on the remote servers via cron. It is the script that captures information which is being constantly changed, like memory, storage, swap usage. All this data is sent for insertion to the remote database. The script executes it self via cron and then sent to be inserted into a PostgreSQL database.

In both scripts data is entered into a dictionary and then a connection to the database is created in order to insert the data.

The Static.py script

#!/usr/bin/python
import psutil
import os
import math
import platform
import subprocess
import socket
import psycopg2
import netifaces as ni
import humanize
from cpuinfo import cpuinfo


class Static():
    def __init__(self):
        #NIC INFO
        self.hostname   = socket.gethostname()
        self.iface      = ni.interfaces()[1]
        self.ipaddress  = ni.ifaddresses(self.iface)[2][0]['addr']
        #---OS INFO
        #For Linux (RH-Debian) Operating Systems
        self.distro  = platform.dist()[0]
        self.release = platform.dist()[1]
        self.kernel  = platform.release()
        #For Windows Operating Systems
        self.osinfo_2_os    = platform.uname()[0]
        self.osinfo_2_ver   = platform.uname()[2]
        self.osinfo_2_rel   = platform.uname()[3]
        #----RAM INFO
        raw_totalM = psutil.virtual_memory().total
        self.total_M    = humanize.naturalsize(raw_totalM)
        #----CPU INFO
        self.info       = cpuinfo.get_cpu_info()
        self.brand      = self.info['brand']
        self.Hz         = self.info['hz_advertised']
        self.cores        = self.info['count']
        self.arch       = self.info['bits']

    def get_OS_make(self):
       if platform.system()  =="Linux":
           return self.distro, self.release, self.kernel
       elif platform.system()     =="Windows":
           return self.osinfo_2_os, self.osinfo_2_ver, self.osinfo_2_rel

info = Static()



hostname  = info.hostname
iface     = info.iface
ipaddress = info.ipaddress
OS        = info.get_OS_make()[0]
OSRel     = info.get_OS_make()[1]
OSKern    = info.get_OS_make()[2]
total_M   = info.total_M
brand     = info.brand
Hz        = info.Hz
cores     = info.cores
arch      = info.arch




#Create the Database PostgreSQL 9.4 connection.
conn = psycopg2.connect("host='172.31.98.161' dbname='servers' user='seeker'")
cur = conn.cursor() #Create the cursor
#Create a Dictionary to pass the value of each function.
server_info = {'hostname': hostname, 'iface':iface, 'ipaddress': ipaddress, 'OS': OS, 'OSRel': OSRel, 'OSKern': OSKern, 'total_M': total_M, 'brand': brand, 'Hz':Hz, 'cores': cores, 'arch': arch}
cur.execute("INSERT INTO servers(hostname, iface, ipaddress, OS, OSRel, OSKern, total_M, brand, Hz, cores, arch) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (hostname, iface, ipaddress, OS, OSRel, OSKern, total_M, brand, Hz, cores, arch))
#If this is not present the changes will not get commited.
conn.commit()

The Dynamic.py srcipt

#THIS FILE IS MANAGED BY PUPPET
#!/usr/bin/python
import psutil
import os
import math
import platform
import subprocess
import socket
import psycopg2
import netifaces as ni
import humanize
from cpuinfo import cpuinfo

class Dynamic():
    def __init__(self):
        #NIC INFO
        self.hostname   = socket.gethostname()
        self.iface      = ni.interfaces()[1]
        self.ipaddress  = ni.ifaddresses(self.iface)[2][0]['addr']
        #RAM USAGE INFO-------------------------------------------------------------------
        self.total_ram_hum       = humanize.naturalsize(psutil.virtual_memory().total)
        self.used_ram_hum        = humanize.naturalsize(psutil.virtual_memory().used)
        #---------Raw info
        self.total_ram_raw       = (psutil.virtual_memory().total)
        self.used_ram_raw        = (psutil.virtual_memory().used)
        self.used_ram_perc       = psutil.virtual_memory().percent
        #HD USAGE INFO-------------------------------------------------------------------
        self.total_HD_hum        = humanize.naturalsize(psutil.disk_usage('/').total)
        self.used_HD_hum         = humanize.naturalsize(psutil.disk_usage('/').used)
        #---------Raw info
        self.total_HD_raw        =(psutil.disk_usage('/').total)
        self.used_HD_raw         =(psutil.disk_usage('/').used)
        self.used_HD_perc        = psutil.disk_usage('/').percent
        #CPU USAGE INFO-------------------------------------------------------------------
        self.cpu_use_perc        = psutil.cpu_percent()
        #SWAP USAGE INFO-------------------------------------------------------------------
        self.swap_used_hum           = humanize.naturalsize(psutil.swap_memory().used)
        self.swap_total_hum          = humanize.naturalsize(psutil.swap_memory().total)
        self.swap_perc               = psutil.swap_memory()[3]
        #---------Raw info
        self.swap_used_raw           = (psutil.swap_memory().used)
        self.swap_total_raw          = (psutil.swap_memory().total)
    def export_to_csv(self):
        print self.hostname
info = Dynamic()

hostname            = info.hostname
iface               = info.iface
ipaddress           = info.ipaddress
total_ram_hum       = info.total_ram_hum
used_ram_hum        = info.used_ram_hum
total_ram_raw       = info.total_ram_raw
used_ram_raw        = info.used_ram_raw
used_ram_perc       = info.used_ram_perc
total_HD_hum        = info.total_HD_hum
used_HD_hum         = info.used_HD_hum
total_HD_raw        = info.total_HD_raw
used_HD_raw         = info.used_HD_raw
used_HD_perc        = info.used_HD_perc
cpu_use_perc        = info.cpu_use_perc
swap_used_hum       = info.swap_used_hum
swap_total_hum      = info.swap_total_hum
swap_perc           = info.swap_perc
swap_used_raw       = info.swap_used_raw
swap_total_raw      = info.swap_total_raw


conn = psycopg2.connect("host='172.31.98.161' dbname='servers' user='seeker'")
cur = conn.cursor() #Create the cursor
#Create a Dictionary to pass the value of each function.
server_info = {'hostname':hostname, 'iface': iface,'ipaddress': ipaddress, 'total_ram_hum': total_ram_hum, 'used_ram_hum': used_ram_hum, 'total_ram_raw': total_ram_raw, 'used_ram_raw':used_ram_raw,'used_ram_perc': used_ram_perc, 'HD_hum': total_HD_hum, 'used_HD_hum': used_HD_hum, 'total_HD_raw': total_HD_raw, 'used_HD_raw':used_HD_raw, 'used_HD_perc': used_HD_perc, 'cpu_use_perc': cpu_use_perc,'swap_used_hum':swap_used_hum, 'swap_total_hum': swap_total_hum, 'swap_perc': swap_perc, 'swap_used_raw': swap_used_raw, 'swap_total_raw': swap_total_raw}
cur.execute("INSERT INTO SERVER_PERF(hostname, iface, ipaddress, total_ram_hum, used_ram_hum, total_ram_raw, used_ram_raw, used_ram_perc, total_HD_hum, used_HD_hum, total_HD_raw,used_HD_raw,used_HD_perc, cpu_use_perc,swap_used_hum, swap_total_hum, swap_perc, swap_used_raw, swap_total_raw) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (hostname, iface, ipaddress, total_ram_hum, used_ram_hum, total_ram_raw, used_ram_raw,used_ram_perc, total_HD_hum, used_HD_hum, total_HD_raw, used_HD_raw, used_HD_perc, cpu_use_perc, swap_used_hum, swap_total_hum, swap_perc, swap_used_raw, swap_total_raw))
#If this is not present the changes will not get commited.
conn.commit()

The new Database schema is as follows:

pg_dump -U seeker -d servers -s -h 172.31.98.161 > servers_db_schema

--
CREATE DATABASE SERVERS;
CREATE ROLE seeker WITH PASSWORD 'Password!';
ALTER DATABASE SERVERS OWNER TO seeker;
ALTER ROLE seeker WITH LOGIN;
GRANT ALL PRIVILEGES ON DATABASE SERVERS to seeker;
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: server_perf; Type: TABLE; Schema: public; Owner: seeker; Tablespace:
--

CREATE TABLE server_perf (
hostname text NOT NULL,
iface text,
ipaddress inet NOT NULL,
total_ram_hum text,
used_ram_hum text,
total_ram_raw numeric(30,2),
used_ram_raw numeric(30,2),
used_ram_perc text,
total_hd_hum text,
used_hd_hum text,
total_hd_raw numeric(30,2),
used_hd_raw numeric(30,2),
used_hd_perc text,
cpu_use_perc text,
swap_used_hum text,
swap_total_hum text,
swap_perc text,
swap_used_raw numeric(30,2),
swap_total_raw numeric(30,2),
time_captured timestamp without time zone DEFAULT now()
);

ALTER TABLE server_perf OWNER TO seeker;

--
-- Name: servers; Type: TABLE; Schema: public; Owner: seeker; Tablespace:
--

CREATE TABLE servers (
hostname text NOT NULL,
iface text,
ipaddress inet NOT NULL,
os text,
osrel text,
oskern text,
total_m text,
brand text,
hz text,
cores numeric(4,1),
arch text
);

ALTER TABLE servers OWNER TO seeker;

--
-- Name: pk_hostname; Type: CONSTRAINT; Schema: public; Owner: seeker; Tablespace:
--

ALTER TABLE ONLY servers
ADD CONSTRAINT pk_hostname PRIMARY KEY (hostname);

--
-- Name: server_perf_hostname_fkey; Type: FK CONSTRAINT; Schema: public; Owner: seeker
--

ALTER TABLE ONLY server_perf
ADD CONSTRAINT server_perf_hostname_fkey FOREIGN KEY (hostname) REFERENCES servers(hostname);

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--
Share Button

Python – Compare modification dates between files and find time difference

The script searches in a specific location for files and it gets in POSIX the time of modification of all files. It also creates a temp file (and write text in it) within the same directory (which gets deleted once the script exits). This temp file is used to get today’s modification date. Then all POSIX dates are converted into human readable format and a comparison is done between the temp file and the files we are examining. If the files we are examining are three months or older, then …. you can enter what ever custom action you want.

#!/usr/bin/python
#The script searches in a specific location for files and it gets in POSIX the time of modification of all files.
#It also creates a temp file (and write text in it)  within the same directory (which gets deleted once the script exits)
#This temp file is used to get today's modification date. Then all POSIX dates are converted into human readable format
#and a comparison is done between the temp file and the files we are examining. If the files we are examining are
#three months or older, then .... you can enter what ever custom action you want.

import os.path
import tempfile
import datetime
import dateutil.relativedelta
import dateutil

#Path of where we are looking for the files
drc = '/root/Music'

#We create a temp file so we can get today's date. File is deleted after script is done executing
tempF = tempfile.NamedTemporaryFile(dir=drc)#, delete=False)
tempF.write('something')#We put in some text in so we are sure to get a time modified.
fileT =  os.stat(tempF.name)[8] #We are getting the mtime aka time of modification
fileT_human = datetime.datetime.fromtimestamp(fileT)#We are converting it to human readeable

for dirpath, dirname, filename in os.walk(drc):  #we are going huntinf for...
        for fname in filename:                   #...files
                path = os.path.join(dirpath, fname) #full path of files
                mtime = os.stat(path)[8] #We are getting time of modification of all the files
                mtime_human  = datetime.datetime.fromtimestamp(mtime) #We humanize the date

                #We are comparing each file with the fileT_human in order to get the difference in date when it comes to modification time
                diff = dateutil.relativedelta.relativedelta (fileT_human, mtime_human)

                #Just a sample output. You can use years, months, days to do the comparison between files.
                #print "%d years, %d months, %d days, %d hours, %d minutes and %d seconds" % (diff.years, diff.months, diff.days, diff.hours, diff.minutes, diff.seconds)
                months = diff.months
                if  months > 3:
                        print "Deleting file ", path
                        #Put whatever action you want here.
                        os.remove(path) #it will delete files only
                else:
                        exit;
Share Button

Python – Centos 6 – PostgresSQL9.4 – Check if PostgresSQL is installed. If not, install it and setup a Database

Script check if postgresql is installed, if it is not it will go ahead and do the appropriate checks to see if the Postgresql YUM RPM packages installed and the proceed to install PostgresSQL. It then does the post configuration steps. It can also create a database with a user assigned to it.

#!/usr/bin/python
# -*- coding: utf-8 -*-
import psutil
import os
import subprocess
import psycopg2


def install_PostgresSQL():
    try:

        if subprocess.call(["rpm", "-q", "pgdg-centos94"]) == 0:
            print "POSTGRES-SQL IS ALREADY INSTALLED"

        elif subprocess.call(["rpm", "-q", "pgdg-centos94"]) == 1:
            subprocess.call(["yum", "localinstall", "-y", "http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm"])
            subprocess.call(["yum", "install", "-y", "postgresql94-contrib", "postgresql94-server"])
            print "#################################################################"
            post_install_PostgreSQL()

    except Exception as e:
        print e
        print "Install failed. Exiting with Error"


def post_install_PostgreSQL():
    try:
        path = "/etc/sysconfig/pgsql/"
        fname = 'postgresql-9.4'
        ##os.chdir(path)
        full_path = os.path.join(path, fname)
        file  = open(full_path, "w")
        file.write('GDATA=/home/postgres94/pgsql/data94\nPGLOG=/home/postgres94/pgsql/pgstartup94.log')
        file.close()
        subprocess.call(["/usr/pgsql-9.4/bin/postgresql94-setup", "initdb"])
        subprocess.call(["systemctl", "enable", "postgresql-9.4"])
        subprocess.call(["systemctl", "start", "postgresql-9.4"])
        create_Database()

    except Exception as e:
        print e
        print "Error in Post Install steps"



def create_Database():
    try:
        conn = psycopg2.connect("host='10.0.0.41' dbname='postgres' user='postgres'")
        cur = conn.cursor() #Create the cursor
        #cur.execute("""SELECT * from servers""")
        conn.autocommit = True
        cur.execute("""CREATE DATABASE test""")
        cur.execute("""CREATE ROLE george WITH PASSWORD 'YourPasswordHere'""")
        cur.execute("""GRANT ALL PRIVILEGES ON DATABASE test to george""")
        #If this is not present the changes will not get commited.
        conn.commit()
        #for row in cur: #Enable the following two lines to view query results
        #    print rows
    except Exception as e:
        print e



check_PostgresSQL()
create_Database()
Share Button

Python – Change one specific extension type of files in a directory that contains multiple types of extensions

import os
import re

src_drct='/home/gmastrokostas/tmp'

for files in os.listdir(src_drct):

    if files.endswith('.txt'):
        oldF = os.path.join(src_drct, files)
        #midF = re.split(r'\.', files)#This works too.
        midF = os.path.splitext(files) #It creates a list and splits the name of the file from the extension.
        newF = oldF.replace('.txt', '.py')#Simple replacement.
        out = os.rename(oldF, newF)

Share Button

Python – Searching for text in multiple files within a directory OR one file in a directory

This script searches the yum directory for all the repo files and searches for the URLs each repo goes to.

Search for text in multiple files

src_dict = ("/etc/yum.repos.d/") #Specify base directory
pattern = re.compile ('http\S+') #CPatter to search for

for yum_files in os.listdir(src_dict): # obtain list of files in directory
    files = os.path.join(src_dict, yum_files) #join the full path with the names of the files.
    strng = open(files) #We need to open the files
    for lines in strng.readlines(): #We then need to read the files
        if re.search(pattern, lines): #If we find the pattern we are looking for
            print re.split(r'=', lines)[1] #We split using as a delimeter the = sign.
            #INSERT WHATEVER CODE YOU WANT

Search for text in one file in a directory

drc = '/home/gmastrokostas/PycharmProjects/learning'
f = open( os.path.join(drc, file) )
for lines in f.readlines():
    searpatt = re.search(pattern, lines)
    if re.search(pattern,lines):
        print (searpatt.group())
Share Button