Index: /branches/amp_bug_fixes_base_3_6_0/scripts/check_adc_ssl.py =================================================================== --- /branches/amp_bug_fixes_base_3_6_0/scripts/check_adc_ssl.py (revision 2371) +++ /branches/amp_bug_fixes_base_3_6_0/scripts/check_adc_ssl.py (working copy) @@ -1,11 +1,10 @@ #!/usr/bin/python -import psycopg2 -import os -import sys import json -import datetime -import pycurl +import sys from StringIO import StringIO + +import psycopg2 +import pycurl import requests sys.path.append("/ca/webui/htdocs/new/src") @@ -59,12 +58,13 @@ restapi_password = options[6] proto = options[7] cookie = options[8] - authorization = 'Basic %s' % (('%s:%s' % (restapi_username, restapi_password)).encode('base64').replace('\n', '')) + authorization = 'Basic %s' % ( + ('%s:%s' % (restapi_username, restapi_password)).encode('base64').replace('\n', '')) curl = pycurl.Curl() curl.body = StringIO() curl.setopt(pycurl.VERBOSE, 0) # DEBUG MODE curl.setopt(pycurl.CONNECTTIMEOUT, 5) - curl.setopt(pycurl.HTTPHEADER, ['Accept: application/json', 'Authorization: %s' % authorization, 'Expect: ']) + curl.setopt(pycurl.HTTPHEADER, ['Accept: application/json', 'Authorization: %s' % authorization, 'Expect: ']) if method == 'POST': curl.setopt(pycurl.POST, 1) curl.setopt(pycurl.POSTFIELDS, params) @@ -104,15 +104,16 @@ return result -def insertCertEvent(time_range, agent_host, server_id, product, event_name, event_type): +def insert_certificate_event(time_range, agent_host, server_id, product, event_name, event_type): response = requests.post('http://127.0.0.1:8086/query?db=composer&epoch=ms', data={ 'q': "SELECT sum(quantity) FROM event_detection WHERE time >= now()-%s AND agent_host='%s' AND server_id='%s' AND product='%s' AND event_name='%s'" % - (time_range, agent_host, server_id, product, event_name) + (time_range, agent_host, server_id, product, event_name) }) if response.status_code == 200: if 'series' not in response.json()["results"][0]: - requests.post('http://127.0.0.1:8086/write?db=composer&epoch=ms', data="event_detection,agent_host=%s,server_id=%s,product=%s,event_name=%s,event_type=%s quantity=1i" % - (agent_host, server_id, product, event_name, event_type)) + requests.post('http://127.0.0.1:8086/write?db=composer&epoch=ms', + data="event_detection,agent_host=%s,server_id=%s,product=%s,event_name=%s,event_type=%s quantity=1i" % + (agent_host, server_id, product, event_name, event_type)) conn = psycopg2.connect(database="cm", @@ -122,17 +123,30 @@ port="5432") cur = conn.cursor() -cur.execute('''CREATE TABLE IF NOT EXISTS adc_vs_ssl_info - (device_name varchar(32) NOT NULL, - vs_name varchar(512) NOT NULL, - vhost_name varchar(512) NOT NULL, - cert_type varchar(16) NOT NULL, - expiration int, - PRIMARY KEY (device_name, vs_name, cert_type));''') +cur.execute('''CREATE TABLE IF NOT EXISTS adc_vs_ssl_info( + device_name varchar(32) NOT NULL, + vs_name varchar(512) NOT NULL, + vhost_name varchar(512) NOT NULL, + cert_type varchar(16) NOT NULL, + expiration int, + PRIMARY KEY (device_name, vs_name, cert_type)); + ALTER TABLE adc_vs_ssl_info DROP CONSTRAINT adc_vs_ssl_info_pkey; + ALTER TABLE adc_vs_ssl_info DROP CONSTRAINT adc_vs_ssl_info_device_id_fkey; + ALTER TABLE adc_vs_ssl_info ADD COLUMN IF NOT EXISTS id BIGSERIAL; + ALTER TABLE adc_vs_ssl_info ADD CONSTRAINT adc_vs_ssl_info_pkey PRIMARY KEY (id); + ALTER TABLE adc_vs_ssl_info ADD COLUMN IF NOT EXISTS device_id varchar(64); + ALTER TABLE adc_vs_ssl_info ADD CONSTRAINT adc_vs_ssl_info_device_id_fkey FOREIGN KEY (device_id) + REFERENCES device (id) MATCH SIMPLE + ON UPDATE NO ACTION + ON DELETE NO ACTION; + ''') cur.execute('delete from adc_vs_ssl_info') conn.commit() -cur.execute("select name, ip_address, restapi_port, restapi_username, restapi_password, connection, type from device") +# ToDo: support for filtered device list instead of all the devices. +cur.execute("select name, ip_address, restapi_port, restapi_username, restapi_password, connection, type, " + "id from device") + rows = cur.fetchall() restapi_options = [] result = [] @@ -140,7 +154,7 @@ for row in rows: if row[6].lower() in ADC_TYPE_LIST and row[5] == 'connected': url = modify_url("/rest/device_type/cli_extend", row[6]) - restapi_options.append(['POST', url, ssl_cli_data, row[1], row[2], row[3], row[4], 'https', '', row[0]]) + restapi_options.append(['POST', url, ssl_cli_data, row[1], row[2], row[3], row[4], 'https', '', row[0], row[7]]) restapi_result = call_restapi_multi(restapi_options) cert_restapi_options = [] for index, rst in enumerate(restapi_result): @@ -160,7 +174,8 @@ cert_restapi_options.append(['POST', restapi_options[index][1], cert_cli_data, restapi_options[index][3], restapi_options[index][4], restapi_options[index][5], restapi_options[index][6], 'https', '', - restapi_options[index][9], vs_name, vhost_name]) + restapi_options[index][9], vs_name, vhost_name, + restapi_options[index][10]]) except: pass cert_restapi_result = call_restapi_multi(cert_restapi_options) @@ -186,15 +201,21 @@ if cert_str[:len('Status')] == "Status": if cert_type: if cert_str.find("Expired") != -1: - insertCertEvent('1d', cert_restapi_options[index][3], cert_restapi_options[index][10], 'virtualService', 'CertKeyExpired', 'Exception') - + insert_certificate_event('1d', cert_restapi_options[index][3], + cert_restapi_options[index][10], 'virtualService', + 'CertKeyExpired', 'Exception') + expiration = -1 - cur.execute("insert into adc_vs_ssl_info values ('%s','%s','%s', '%s', %d) on conflict" - " (device_name, vs_name, cert_type) do update set vhost_name=excluded.vhost_name," - "expiration=excluded.expiration" % (cert_restapi_options[index][9], + cur.execute("insert into adc_vs_ssl_info (device_id, device_name, vs_name, vhost_name, " + "cert_type, expiration) values ('%s', '%s','%s','%s', '%s', %d) on " + "conflict (id) do update set vhost_name=excluded.vhost_name, " + "expiration=excluded.expiration" % (cert_restapi_options[index][12], + cert_restapi_options[index][9], cert_restapi_options[index][10], cert_restapi_options[index][11], - cert_type, expiration)) + cert_type, + expiration + )) result.append([cert_restapi_options[index][9], cert_restapi_options[index][10], cert_restapi_options[index][11], cert_type, "Expired"]) cert_type = "" @@ -205,30 +226,38 @@ expiration = int(cert_str[len('Days before expiration:'):]) result.append([cert_restapi_options[index][9], cert_restapi_options[index][10], cert_restapi_options[index][11], cert_type, expiration]) - + if expiration <= 60: if expiration <= 1: - insertCertEvent('1d', cert_restapi_options[index][3], cert_restapi_options[index][10], 'virtualService', 'CertKeyExpired_1', 'StatusNotification') + insert_certificate_event('1d', cert_restapi_options[index][3], + cert_restapi_options[index][10], 'virtualService', + 'CertKeyExpired_1', 'StatusNotification') elif expiration <= 7: - insertCertEvent('7d', cert_restapi_options[index][3], cert_restapi_options[index][10], 'virtualService', 'CertKeyExpired_7', 'StatusNotification') + insert_certificate_event('7d', cert_restapi_options[index][3], + cert_restapi_options[index][10], 'virtualService', + 'CertKeyExpired_7', 'StatusNotification') elif expiration <= 30: - insertCertEvent('30d', cert_restapi_options[index][3], cert_restapi_options[index][10], 'virtualService', 'CertKeyExpired_30', 'StatusNotification') + insert_certificate_event('30d', cert_restapi_options[index][3], + cert_restapi_options[index][10], 'virtualService', + 'CertKeyExpired_30', 'StatusNotification') else: - insertCertEvent('60d', cert_restapi_options[index][3], cert_restapi_options[index][10], 'virtualService', 'CertKeyExpired_60', 'StatusNotification') - - cur.execute("insert into adc_vs_ssl_info values ('%s','%s','%s', '%s', %d) on conflict" - " (device_name, vs_name, cert_type) do update set vhost_name=excluded.vhost_name," - "expiration=excluded.expiration" % (cert_restapi_options[index][9], - cert_restapi_options[index][10], - cert_restapi_options[index][11], - cert_type, expiration)) + insert_certificate_event('60d', cert_restapi_options[index][3], + cert_restapi_options[index][10], 'virtualService', + 'CertKeyExpired_60', 'StatusNotification') + + cur.execute("insert into adc_vs_ssl_info (device_id, device_name, vs_name, vhost_name, " + "cert_type, expiration) values ('%s', '%s','%s','%s', '%s', %d) on conflict (id) " + "do update set vhost_name=excluded.vhost_name, expiration=excluded.expiration" % ( + cert_restapi_options[index][12], cert_restapi_options[index][9], + cert_restapi_options[index][10], + cert_restapi_options[index][11], + cert_type, expiration)) cert_type = "" continue except: # print "Cert Response error", cert_rsp pass - conn.commit() conn.close() @@ -241,3 +270,5 @@ result.sort(key=get_expire, reverse=False) print json.dumps(result) + + Index: /branches/amp_bug_fixes_base_3_6_0/src/webui/webui/htdocs/new/src/cm/lib/postgres_db.py =================================================================== --- /branches/amp_bug_fixes_base_3_6_0/src/webui/webui/htdocs/new/src/cm/lib/postgres_db.py (revision 2371) +++ /branches/amp_bug_fixes_base_3_6_0/src/webui/webui/htdocs/new/src/cm/lib/postgres_db.py (working copy) @@ -419,7 +419,11 @@ action varchar(20) NOT NULL, time varchar(30) NOT NULL, result varchar(20) NOT NULL - )''' + ); + ALTER TABLE ext_log ADD COLUMN IF NOT EXISTS id BIGSERIAL; + ALTER TABLE ext_log DROP CONSTRAINT ext_log_pkey; + ALTER TABLE ext_log ADD CONSTRAINT ext_log_pkey PRIMARY KEY (id); + ''' self.execute_sql(create_table_sql) def create_table_user_authorization(self): Index: /branches/amp_bug_fixes_base_3_6_0/src/webui/webui/htdocs/new/src/cm/router.py =================================================================== --- /branches/amp_bug_fixes_base_3_6_0/src/webui/webui/htdocs/new/src/cm/router.py (revision 2371) +++ /branches/amp_bug_fixes_base_3_6_0/src/webui/webui/htdocs/new/src/cm/router.py (working copy) @@ -536,7 +536,7 @@ db.close() def get_extension_log(request): - select_sql = "select * from ext_log" + select_sql = "select name, action, time, result from ext_log;" try: db = DB.get_connected_db() data = db.fetchall(select_sql)