In [ ]:

    

import csv
import getpass
import json
import os
import re

import pexpect
import requests

from types import SimpleNamespace
from ipywidgets import IntProgress
from IPython.display import display

ngw_api_user = 'dummy'
ngw_api_pass = getpass.getpass('NGW password: ')

ssh_user = 'root'
ssh_pass = getpass.getpass('SSH password: ')


    

In [ ]:

    

r = requests.get('http://ngwcluster-api.nextgis.com/api/instance/', auth=(ngw_api_user, ngw_api_pass)).json()


    

In [ ]:

    

progress = IntProgress(min=0, max=len(r.keys()))


    

In [ ]:

    

def get_configs(domains, ssh_host='app-102', ssh_user=ssh_user, ssh_pass=ssh_pass):
    prompt = '%s@%s' % (ssh_user, ssh_host)
    cmd = 'ssh -A -t %s@95.213.223.210 ssh %s'
    child = pexpect.spawn(cmd % (ssh_user, ssh_host),env = {'TERM': 'dumb'})
    child.expect('password:')
    child.sendline(ssh_pass)
    child.expect(prompt)
    
    for domain in domains:
        # extract name of config file
        grep_pattern = domain
        cmd = 'grep -w %s /ngw/vassal/*'
        child.sendline(cmd % (grep_pattern,))
        child.expect(prompt)
        output = child.before.decode('utf-8')
        m = re.search("vassal/(.*\.ini)", output)
        ini = m.group(1)

        # extract db credentials
        grep_pattern = 'database.user\|database.name\|database.password'
        cmd = 'grep \'%s\' /ngw/config/%s'
        child.sendline(cmd % (grep_pattern, ini))
        child.expect(prompt)
        output = child.before.decode('utf-8')
        m = re.search('database.name = (.*)\r\ndatabase.user = (.*)\r\ndatabase.password = (.*)\r', output)  
        config = SimpleNamespace(domain=domain,
                                 db_name=m.group(1),
                                 db_user=m.group(2),
                                 db_password=m.group(3))
        
        # extract relation filepath
        output = child.before.decode('utf-8')
        cmd = 'psql -h pgsql-103 -U %s -d %s -t -c "select pg_relation_filepath(\'public.resource\');"'
        child.sendline(cmd % (config.db_user, config.db_name))
        child.expect('Password')
        child.sendline(config.db_password)
        child.expect(prompt)
        output = child.before.decode('utf-8')
        m = re.search('\r\n (.*)\r', output)
        config.filepath = m.group(1)

        # update progressbar
        progress.value += 1
        
        yield config


    

In [ ]:

    

def get_mtimes(filepaths, ssh_host='pgsql-103', ssh_user=ssh_user, ssh_pass=ssh_pass):
    prompt = '%s@%s' % (ssh_user, ssh_host)
    cmd = 'ssh -A -t %s@95.213.223.210 ssh %s'
    child = pexpect.spawn(cmd % (ssh_user, ssh_host),env = {'TERM': 'dumb'})
    child.expect('password:')
    child.sendline(ssh_pass)
    child.expect(prompt)
    
    for path in filepaths:
        cmd = 'date -r %s +"%%d.%%m.%%Y"'
        child.sendline(cmd % (os.path.join('/var/lib/postgresql/9.3/main', path)))
        child.expect(prompt)
        output = child.before.decode('utf-8')
        m = re.search('\r\n(.*)\r', output)
        mtime = m.group(1)

        yield mtime


    

In [ ]:

    

# this cell can takes up to 2 hrs

display(progress)

dlist = []
ilist = []

gmap = {}

for guid in r.keys():
    domains = r[guid]['options']['domains']
    if domains is not None:
        domain = domains[0]
        gmap[domain] = guid
        dlist.append(domain) 

configs = get_configs(dlist)
for config in configs:
    ilist.append(dict(guid=gmap[config.domain],
                      domain=config.domain,
                      db_name=config.db_name,
                      db_user=config.db_user,
                      db_password=config.db_password,
                      filepath=config.filepath))

with open('/tmp/ngw_instances.json', 'w') as outfile:
    json.dump(ilist, outfile)


    

In [ ]:

    

with open('/tmp/ngw_instances.json') as infile:
    instances = json.load(infile)

fplist = [instance['filepath'] for instance in instances]
mtimes = get_mtimes(fplist)

with open('/tmp/ngw_mtimes.csv', 'w') as outcsv:
    writer = csv.writer(outcsv)
    writer.writerow(['guid', 'domain', 'mtime'])
    writer.writerows((instance['guid'], instance['domain'], mtime)
                     for (instance, mtime) in zip(instances, mtimes))