def run_action(credentials, rule, entity, params):
logging.info(f'{__file__} - ${run_action.__name__} started')
server_name = entity['name']
subscription_id = entity['accountNumber']
group_name = entity['resourceGroup']
param_name = 'log_retention_days'
logging.info(
f'{__file__} - subscription_id : {subscription_id} - group_name : {group_name} - server_name : {server_name}'
)
if not subscription_id or not credentials:
return raise_credentials_error()
try:
db_client = PostgreSQLManagementClient(credentials, subscription_id)
db_client.configurations.begin_create_or_update(
group_name,
server_name,
param_name,
parameters=Configuration(value='7'))
msg = f'Log retention was set to 7 days successfully on PostgreSQL server: {server_name}'
logging.info(f'{__file__} - {msg}')
return f'{msg}'
except HttpResponseError as e:
msg = f'Unexpected error : {e.message}'
logging.info(f'{__file__} - {msg}')
return msg
def get_postgresql_management_client(cli_ctx, **_):
from os import getenv
from azure.mgmt.rdbms.postgresql import PostgreSQLManagementClient
# Allow overriding resource manager URI using environment variable
# for testing purposes. Subscription id is also determined by environment
# variable.
rm_uri_override = getenv(RM_URI_OVERRIDE)
if rm_uri_override:
client_id = getenv(CLIENT_ID)
if client_id:
from azure.common.credentials import ServicePrincipalCredentials
credentials = ServicePrincipalCredentials(
client_id=client_id,
secret=getenv(CLIENT_SECRET),
tenant=getenv(TENANT_ID))
else:
from msrest.authentication import Authentication # pylint: disable=import-error
credentials = Authentication()
return PostgreSQLManagementClient(
subscription_id=getenv(SUB_ID_OVERRIDE),
base_url=rm_uri_override,
credentials=credentials)
else:
# Normal production scenario.
return get_mgmt_service_client(cli_ctx, PostgreSQLManagementClient)
def run_action(credentials, rule, entity, params):
logging.info(f'{__file__} - ${run_action.__name__} started')
server_name = entity['name']
subscription_id = entity['accountNumber']
group_name = entity['resourceGroup']
logging.info(
f'{__file__} - subscription_id : {subscription_id} - group_name : {group_name} - server_name : {server_name}'
)
if not subscription_id or not credentials:
return raise_credentials_error()
try:
db_client = PostgreSQLManagementClient(credentials, subscription_id)
db_client.servers.update(
group_name, server_name,
ServerUpdateParameters(ssl_enforcement='Enabled'))
msg = f'Force SSL connection was enabled successfully on PostgreSQL server: {server_name}'
logging.info(f'{__file__} - {msg}')
return f'{msg}'
except CloudError as e:
msg = f'Unexpected error : {e.message}'
logging.info(f'{__file__} - {msg}')
return msg
def run(self,list_subscription_name,list_dbserver_type,subscription_id,client_id,client_tenant):
list_result = list()
for subscription_name in list_subscription_name:
print subscription_name
for dbserver_type in list_dbserver_type:
print dbserver_type
subscriptionid = subscription_id[subscription_name]
client = Client(base_url='http://localhost')
client_secret = client.keys.get_by_name(name='azure_stackstorm_secret', decrypt=True)
credentials = ServicePrincipalCredentials(client_id, client_secret.value, tenant=client_tenant, china=True)
if dbserver_type == "mysql":
dbserverclient = MySQLManagementClient(credentials, subscriptionid, base_url="https://management.chinacloudapi.cn")
if dbserver_type == "postgresql":
dbserverclient = PostgreSQLManagementClient(credentials, subscriptionid, base_url="https://management.chinacloudapi.cn")
if dbserver_type == "sqlserver":
dbserverclient = SqlManagementClient(credentials, subscriptionid, base_url="https://management.chinacloudapi.cn")
list_dict_resgroup_dbserver = self._list_all_dbserverserver(dbserverclient)
num = len(list_dict_resgroup_dbserver)
for n in range(0, num):
dict_resgroup_rules = dict()
list_rule =self._list_firewall_rule(dbserverclient,list_dict_resgroup_dbserver[n]["resource_group"],list_dict_resgroup_dbserver[n]["dbserver_name"])
dict_resgroup_rules["resource_group"] = list_dict_resgroup_dbserver[n]["resource_group"]
dict_resgroup_rules["dbserver_name"] = list_dict_resgroup_dbserver[n]["dbserver_name"]
dict_resgroup_rules["rule_name"] = list_rule
now_time = datetime.datetime.now().strftime("%y-%m-%d")
rule_num = len(dict_resgroup_rules["rule_name"])
for i in range(0, rule_num):
if dict_resgroup_rules["rule_name"][i].split('_')[0] == now_time:
result = self._delete_rule(dbserverclient,dict_resgroup_rules["resource_group"],dict_resgroup_rules["dbserver_name"],dict_resgroup_rules["rule_name"][i])
list_result.append(result)
return (True,list_result)
def _get_postgres_server_details(self, server_index, server_name, rg_name,
sub_index, sub):
"""Get details Postgres server.
Arguments:
sub_index (int): Subscription index (for logging only).
sub (Subscription): Azure subscription object.
rg_name (str): Resource group name.
server_index (int): Server index (for logging only).
server_name (str): Name of the Postgres server.
Yields:
dict: An Azure Postgres server record with configuration.
"""
_log.info('Working on Postgres server #%d: %s; %s',
server_index, server_name,
util.outline_az_sub(sub_index, sub, self._tenant))
sub_id = sub.get('subscription_id')
creds = self._credentials
postgres_client = PostgreSQLManagementClient(creds, sub_id)
server_details = postgres_client.servers.get(rg_name, server_name)
server_details = server_details.as_dict()
server_configuration_list = \
postgres_client.configurations.list_by_server(rg_name, server_name)
configurations, derived_configs = \
self._get_postgres_server_configuration(server_configuration_list)
yield from self._process_postgres_server_details(
sub, server_details, configurations, derived_configs)
def run_action(credentials, rule, entity, params):
logging.info(f'{__file__} - ${run_action.__name__} started')
server_name = entity['name']
subscription_id = entity['accountNumber']
group_name = entity['resourceGroup']
logging.info(
f'{__file__} - subscription_id : {subscription_id} - group_name : {group_name} - server_name : {server_name}'
)
if not subscription_id or not credentials:
return raise_credentials_error()
try:
db_client = PostgreSQLManagementClient(credentials, subscription_id)
db_client.configurations.create_or_update(group_name,
server_name,
'log_disconnections',
value='ON')
msg = f'Log disconnections was enabled successfully on PostgreSQL server: {server_name}'
logging.info(f'{__file__} - {msg}')
return f'{msg}'
except CloudError as e:
msg = f'Unexpected error : {e.message}'
logging.info(f'{__file__} - {msg}')
return msg
def run(self, args):
"""Run the remediation job.
:param args: List of arguments provided to the job.
:type args: list.
:returns: int
"""
params = self.parse(args[1])
credential = ClientSecretCredential(
client_id=os.environ.get("AZURE_CLIENT_ID"),
client_secret=os.environ.get("AZURE_CLIENT_SECRET"),
tenant_id=os.environ.get("AZURE_TENANT_ID"),
)
client = PostgreSQLManagementClient(credential, params["subscription_id"])
return self.remediate(
client, params["resource_group_name"], params["postgre_server_name"],
)
def _get_subscription_postgres_servers(self, sub_index, sub):
"""Get Postgres servers from a single subscrption.
Arguments:
sub_index (int): Subscription index (for logging only).
sub (Subscription): Azure subscription object.
Yields:
tuple: A tuple which when unpacked forms valid arguments for
:meth:`_get_postgres_details`.
"""
try:
tenant = self._tenant
creds = self._credentials
sub_id = sub.get('subscription_id')
postgres_client = PostgreSQLManagementClient(creds, sub_id)
db_server_list = postgres_client.servers.list()
for server_index, postgres_server in enumerate(db_server_list):
postgres_server = postgres_server.as_dict()
server_id = postgres_server.get('id')
server_name = postgres_server.get('name')
_log.info('Found Postgres Server #%d: %s; %s', server_index,
server_name,
util.outline_az_sub(sub_index, sub, tenant))
rg_name = \
tools.parse_resource_id(server_id)['resource_group']
yield (server_index, server_name, rg_name, sub_index, sub)
# Break after pulling data for self._max_recs number
# of Postgres servers for a subscriber. Note that if
# self._max_recs is 0 or less, then the following
# condition never evaluates to True.
if server_index + 1 == self._max_recs:
_log.info(
'Stopping Postgres server fetch due '
'to _max_recs: %d; %s', self._max_recs,
util.outline_az_sub(sub_index, sub, self._tenant))
break
except CloudError as e:
_log.error('Failed to fetch Postgres servers; %s; error: %s: %s',
util.outline_az_sub(sub_index, sub, tenant),
type(e).__name__, e)
def __init__(self):
self.SUBSCRIPTION_ID = "d1d6c02e-83ed-4898-8e84-20570565e603"
self.RESOURCE_GROUP = "PBL_resource"
self.tenant_id = "6f4432dc-20d2-441d-b1db-ac3380ba633d"
self.client_id = "c5adeff9-7861-4ec8-b233-93dc5fc8fb91"
self.client_secret = "94218968-bd01-4b92-b596-fd1ed28b2426"
self.SERVER = "pblserver1" # SET_YOUR_SERVER_NAME
self.ADMIN_USER = "******" # SET_YOUR_DATABASE_USERNAME
self.ADMIN_PASSWORD = "******" # SET_YOUR_DATABASE_PASSWORD
self.LOCATION = "southeastasia"
self.credential = ServicePrincipalCredentials(
tenant=self.tenant_id,
client_id=self.client_id,
secret=self.client_secret)
self.client = PostgreSQLManagementClient(
credentials=self.credential, subscription_id=self.SUBSCRIPTION_ID)
def get_postgresql_flexible_management_client(cli_ctx, **_):
from os import getenv
from azure.mgmt.rdbms.postgresql_flexibleservers import PostgreSQLManagementClient
# Allow overriding resource manager URI using environment variable
# for testing purposes. Subscription id is also determined by environment
# variable.
rm_uri_override = getenv(RM_URI_OVERRIDE)
if rm_uri_override:
client_id = getenv(AZURE_CLIENT_ID)
if client_id:
credentials = get_environment_credential()
else:
from msrest.authentication import Authentication # pylint: disable=import-error
credentials = Authentication()
return PostgreSQLManagementClient(
subscription_id=getenv(SUB_ID_OVERRIDE),
base_url=rm_uri_override,
credential=credentials)
# Normal production scenario.
return get_mgmt_service_client(cli_ctx, PostgreSQLManagementClient)
def azure_dbs():
tenant_id = config.TENANT
application_id = config.CLIENT_ID_JENKINS
application_secret = config.CLIENT_SECRET_JENKINS
subscription_id = config.SUBSCRIPTION_ID
credentials = ServicePrincipalCredentials(client_id=application_id,
secret=application_secret,
tenant=tenant_id)
resource_client = ResourceManagementClient(credentials, subscription_id)
sql_client = PostgreSQLManagementClient(credentials, subscription_id)
monitor_client = MonitorManagementClient(credentials, subscription_id)
now = datetime.datetime.now().time()
hour_ago = datetime.datetime.now() - datetime.timedelta(hours=1)
day_ago = datetime.datetime.now() - datetime.timedelta(days=1)
databases = sql_client.servers.list()
resources = sql_client.servers.list()
def get_metric(resourceid, metric, aggregation):
metric_data = monitor_client.metrics.list(resource_uri=resourceid,
timespan="{}/{}".format(
day_ago, now),
interval='PT15M',
metricnames=metric,
aggregation=aggregation)
return metric_data
def get_timeseries_current_value(metric_data):
x_values, y_values = [], []
for item in metric_data.value:
for timeserie in item.timeseries:
for data in timeserie.data:
current_value = data.average
return current_value
def get_timeseries_avg_data(metric_data):
x_values, y_values = [], []
for item in metric_data.value:
for timeserie in item.timeseries:
for data in timeserie.data:
x_metrics, y_metrics = [], []
x_values.append("".join(str(data.average)))
y_values.append("".join(str(data.time_stamp)))
x_metrics.extend(x_values)
y_metrics.extend(y_values)
return x_metrics, y_metrics, metric_name
# for database in databases:
# resourceid = database.id
# cpu_usage = get_metric(resourceid,'cpu_percent','Average')
# # storage_usage = get_metric(resourceid,'storage_percent','Average')
# # cpu_metrics = get_timeseries_avg_data(cpu_usage, 10, 'CPU percent (Avg)')
# # storage_metrics = get_timeseries_avg_data(storage_usage, 10, 'Storage percent (Avg)')
if request.method == 'POST':
requested_database = request.form["databases"]
cpu_usage = get_metric(requested_database, 'cpu_percent', 'Average')
current_value = get_timeseries_current_value(cpu_usage)
message = current_value
flash(message)
return render_template('az_services/databases.html', databases=databases)
import plotly.graph_objs as go
import numpy as np
tenant_id = os.environ.get('TENANT')
application_id = os.environ.get('CLIENT_ID_JENKINS')
application_secret = os.environ.get('CLIENT_SECRET_JENKINS')
subscription_id = os.environ.get('SUBSCRIPTION_ID')
credentials = ServicePrincipalCredentials(client_id=application_id,
secret=application_secret,
tenant=tenant_id)
# AZURE
resource_client = ResourceManagementClient(credentials, subscription_id)
monitor_client = MonitorManagementClient(credentials, subscription_id)
sql_client = PostgreSQLManagementClient(credentials, subscription_id)
databases = sql_client.servers.list()
# TIMESPAN
now = datetime.datetime.now().time()
hour_ago = datetime.datetime.now() - datetime.timedelta(hours=1)
day_ago = datetime.datetime.now() - datetime.timedelta(days=1)
def get_metric(metric, aggregation):
metric_data = monitor_client.metrics.list(resource_id,
timespan="{}/{}".format(
day_ago, now),
interval='PT15M',
metricnames=metric,
def pgsql_client(self):
return PostgreSQLManagementClient(self.credentials,
self.subscription_id)
