diff --git a/manifests/site.pp b/manifests/site.pp --- a/manifests/site.pp +++ b/manifests/site.pp @@ -219,6 +219,10 @@ include role::zfs_snapshots_storage } +node 'money.internal.admin.swh.network' { + include role::swh_azure_billing_report +} + node default { include role::swh_base } diff --git a/site-modules/profile/files/azure_billing_report/compute_stats.py b/site-modules/profile/files/azure_billing_report/compute_stats.py new file mode 100644 --- /dev/null +++ b/site-modules/profile/files/azure_billing_report/compute_stats.py @@ -0,0 +1,115 @@ +## +# File managed by puppet (class profile::azure_billing_report), changes will be lost. + +from datetime import datetime +from jinja2 import Environment, FileSystemLoader + +import click +import matplotlib.pyplot as plt +import pandas + +def generate_simple_costs( + data: pandas.DataFrame, + date_format: str, + base_file_name: str) -> None: + + data.reset_index(inplace=True) + + data['Date'] = data['Date'].dt.strftime(date_format) + data.filter(items=['Date', 'Cost']) + generate_data_files(data[['Date', 'Cost']], base_file_name) + + +def pad_series(series: pandas.Series) -> pandas.Series: + return series.astype(str).str.pad(2, fillchar='0') + + +def generate_data_files(data: pandas.DataFrame, base_name: str) -> None: + with open(f"{base_name}.md", 'w') as f: + print(f"Generating {f.name}") + f.write(data.to_markdown(index=False)) + with open(f"{base_name}.html", 'w') as f: + print(f"Generating {f.name}") + f.write(data.to_html( + index=False, + float_format=lambda x: '%10.2f' % x) + ) + + +@click.command() +@click.argument('output_dir', type=click.Path(exists="true"), default="AzureUsage.csv") +def main(output_dir) -> None: + + csv = pandas.read_csv(output_dir + '/AzureUsage.csv', parse_dates=[2]) + + # Cost per day + cost_per_day = csv.groupby('Date', as_index=True).sum() + cost_per_day.plot(y='Cost') + plt.savefig(f"{output_dir}/cost_per_day.png") + + cost_per_day.reset_index(inplace=True) + cost_per_day['Year'] = cost_per_day['Date'].dt.year + cost_per_day['Month'] = cost_per_day['Date'].dt.month + cost_per_day['Day'] = cost_per_day['Date'].dt.day + cost_per_day.sort_values(by=['Year', 'Month', 'Day'], inplace=True, ascending=False) + + generate_simple_costs(cost_per_day, "%Y-%m-%d", output_dir + "/cost_per_day") + + # Cost per month + cost_per_month = csv.groupby(pandas.Grouper(key='Date', freq='M'), as_index=True).sum() + cost_per_month.plot(y='Cost') + plt.savefig(f"{output_dir}/cost_per_month.png") + + cost_per_month.reset_index(inplace=True) + cost_per_month['Year'] = cost_per_month['Date'].dt.year + cost_per_month['Month'] = cost_per_month['Date'].dt.month + cost_per_month.sort_values(by=['Year', 'Month'], inplace=True, ascending=False) + + generate_simple_costs(cost_per_month, "%Y-%m", output_dir + "/cost_per_month") + + # Cost per service per month + cost_per_service = csv.copy() + cost_per_service['Year'] = cost_per_service['Date'].dt.year + cost_per_service['Month'] = cost_per_service['Date'].dt.month + cost_per_service['Day'] = cost_per_service['Date'].dt.day + cost_per_service_per_month = cost_per_service.groupby(['Year', 'Month', 'ServiceName', 'ServiceResource']).sum() + cost_per_service_per_month.reset_index(inplace=True) + cost_per_service_per_month.sort_values(by=['Year', 'Month','Cost'], inplace=True, ascending=False) + cost_per_service_per_month['Date'] = cost_per_service_per_month['Year'].astype(str) + \ + '-' + \ + pad_series(cost_per_service_per_month['Month']) + + generate_data_files( + cost_per_service_per_month[['Date','ServiceName', 'ServiceResource', 'Cost']], + output_dir + "/cost_per_service_per_month") + + # Cost per service per day + cost_per_service_per_day = cost_per_service.groupby(['Year', 'Month', 'Day', 'ServiceName', 'ServiceResource']).sum() + cost_per_service_per_day.reset_index(inplace=True) + cost_per_service_per_day.sort_values(by=['Year', 'Month', 'Day', 'Cost'], inplace=True, ascending=False) + cost_per_service_per_day['Date'] = cost_per_service_per_day['Year'].astype(str) + \ + '-' + \ + pad_series(cost_per_service_per_day['Month']) + \ + '-' + \ + pad_series(cost_per_service_per_day['Day']) + + generate_data_files(cost_per_service_per_day[['Date','ServiceName', 'ServiceResource', 'Cost']], output_dir + "/cost_per_service_per_day") + + ## + # index.html page generation + ## + index_file_name = f"{output_dir}/index.html" + print(f"Generating {index_file_name}") + + generated_date = datetime.now() + + template_file_loader = FileSystemLoader(searchpath='./') + env = Environment(loader=template_file_loader) + template = env.get_template('index.html.tmpl') + index = template.render(generated_date=generated_date) + + with open(index_file_name, 'w') as f: + f.write(index) + +if __name__ == '__main__': + main() diff --git a/site-modules/profile/files/azure_billing_report/get_csv.py b/site-modules/profile/files/azure_billing_report/get_csv.py new file mode 100644 --- /dev/null +++ b/site-modules/profile/files/azure_billing_report/get_csv.py @@ -0,0 +1,112 @@ +## +# File managed by puppet (class profile::azure_billing_report), changes will be lost. + +from selenium import webdriver +from selenium.webdriver.common.by import By +from selenium.webdriver.common.keys import Keys +from selenium.webdriver.support.ui import WebDriverWait +from selenium.webdriver.support import expected_conditions as EC +from datetime import datetime, timedelta + +import os +import time + +# To always have a complete month +START_DATE_FORMAT = "%Y-%m-01" +END_DATE_FORMAT = "%Y-%m-%d" +BASE_SPONSORSHIP_URL = "https://www.microsoftazuresponsorships.com" +EXPECTED_FILE = "AzureUsage.csv" + +def wait_for_download(): + MAX_COUNT = 10 + print("Waiting for download", end="") + count = 0 + + while not os.path.exists(EXPECTED_FILE) and count < MAX_COUNT: + time.sleep(2) + print(".", end="") + count += 1 + if count >= MAX_COUNT: + raise Exception("File not found") + print("") + print("done!") + + +if __name__ == '__main__': + login = os.environ.get("LOGIN") + password = os.environ.get("PASSWORD") + DEBUG = os.environ.get("DEBUG") in ["1", "true"] + + assert login is not None + assert password is not None + + now = datetime.now() + last_year = now - timedelta(365) + end = time.strftime(END_DATE_FORMAT, now.timetuple()) + start = time.strftime(START_DATE_FORMAT, last_year.timetuple()) + + print(f"Retrieving consumption from {start} to {end}") + + CSV_URL = f"{BASE_SPONSORSHIP_URL}/Usage/DownloadUsage?startDate={start}&endDate={end}&fileType=csv" + print(f"CSV url: {CSV_URL}") + + options = webdriver.ChromeOptions() + options.add_argument("no-sandbox") + options.add_argument("--disable-dev-shm-usage") + options.add_argument("--window-size=800,600") + options.add_argument("--headless") + + driver = webdriver.Chrome(options=options) + driver.set_page_load_timeout(30) + + print("Going to the portal login page...") + driver.get(f"{BASE_SPONSORSHIP_URL}/Account/Login") + wait = WebDriverWait(driver, 30) + + wait.until(EC.visibility_of_element_located((By.NAME, "loginfmt"))) + + print("Entering login...") + loginInput = driver.find_element(by=By.NAME, value="loginfmt") + loginInput.send_keys(login, Keys.ENTER) + if DEBUG: + driver.save_screenshot("user.png") + + wait.until(EC.visibility_of_element_located((By.NAME, "passwd"))) + + print("Entering password...") + + passwordInput = driver.find_element(by=By.NAME, value="passwd") + + try: + passwordInput.send_keys(password, Keys.ENTER) + finally: + if DEBUG: + driver.save_screenshot("password.png") + + print("Waiting for stay signed page...") + + try: + wait.until(EC.visibility_of_element_located((By.CSS_SELECTOR, "form"))) + finally: + if DEBUG: + driver.save_screenshot("staysigned.png") + + print("On stay signed page") + button = driver.find_element(by=By.CSS_SELECTOR, value="input[value='No']") + button.send_keys(Keys.ENTER) + + print("Waiting for home page") + try: + wait.until(EC.visibility_of_element_located((By.CSS_SELECTOR, "div.pagecontent"))) + finally: + if DEBUG: + driver.save_screenshot("sponsorships-home.png") + + print("Downloading usage summary csv") + driver.get(CSV_URL) + + wait_for_download() + + print(f"Usage csv file downloaded and available in the {EXPECTED_FILE} file") + + driver.close() diff --git a/site-modules/profile/files/azure_billing_report/index.html.tmpl b/site-modules/profile/files/azure_billing_report/index.html.tmpl new file mode 100644 --- /dev/null +++ b/site-modules/profile/files/azure_billing_report/index.html.tmpl @@ -0,0 +1,32 @@ + + + + +
+ +
+
+
+ Raw data: html / markdown
+
+
+
+ Raw sdata: html / markdown
+
generation date: {{ generated_date }}
+ + diff --git a/site-modules/profile/files/azure_billing_report/refresh_azure_report.sh b/site-modules/profile/files/azure_billing_report/refresh_azure_report.sh new file mode 100755 --- /dev/null +++ b/site-modules/profile/files/azure_billing_report/refresh_azure_report.sh @@ -0,0 +1,26 @@ +#!/bin/bash + +## +# File managed by puppet (class profile::azure_billing_report), changes will be lost. + +set -e + +pushd "${DATA_DIRECTORY}" + +CSV_FILE=${DATA_DIRECTORY}/AzureUsage.csv +echo "Cleanup previous csv file..." +rm -fv ${CSV_FILE} + +if [ ! -e "${CSV_FILE}" ]; then + echo "Getting new statistics from azure portal..." + ${DATA_DIRECTORY}/.venv/bin/python3 ${INSTALL_DIRECTORY}/get_csv.py +else + echo "${CSV_FILE} already exists, reusing it..." +fi + +echo "Generating report..." + +pushd ${INSTALL_DIRECTORY} +${DATA_DIRECTORY}/.venv/bin/python3 ${INSTALL_DIRECTORY}/compute_stats.py ${DATA_DIRECTORY} + +echo "Report refreshed." diff --git a/site-modules/profile/files/azure_billing_report/requirements.txt b/site-modules/profile/files/azure_billing_report/requirements.txt new file mode 100644 --- /dev/null +++ b/site-modules/profile/files/azure_billing_report/requirements.txt @@ -0,0 +1,9 @@ +## +# File managed by puppet (class profile::azure_billing_report), changes will be lost. + +click +jinja2 +matplotlib +pandas +selenium +tabulate diff --git a/site-modules/profile/manifests/azure_billing_report.pp b/site-modules/profile/manifests/azure_billing_report.pp new file mode 100644 --- /dev/null +++ b/site-modules/profile/manifests/azure_billing_report.pp @@ -0,0 +1,93 @@ +# Install and configure the azure +class profile::azure_billing_report { + $billing_user = 'azbilling' + $install_path = '/opt/azure_billing' + $data_path = '/var/lib/azure_billing' + $installed_flag = "${data_path}/.pip_updated" + + $azure_user = lookup('azure_billing::user') + $azure_password = lookup('azure_billing::password') + + $packages = ['python3-venv', 'python3-pip', 'chromium-driver'] + + ensure_packages($packages) + + user {$billing_user: + ensure => present, + system => true, + shell => '/bin/bash', + home => $data_path, + } + + file { '/var/lib/azure_billing': + ensure => directory, + owner => $billing_user, + group => 'root', + mode => '0744', + } + + # Install the scripts + file { $install_path: + ensure => directory, + recurse => true, + purge => true, + owner => $billing_user, + group => 'root', + source => 'puppet:///modules/profile/azure_billing_report', + notify => Exec['azure_billing_prepare_pip'], + } + + file { "${install_path}/refresh_azure_report.sh": + ensure => present, + source => 'puppet:///modules/profile/azure_billing_report/refresh_azure_report.sh', + owner => $billing_user, + group => 'root', + mode => '0744' + } + + # create the venv if it doesn't exist already + exec { 'azure_billing_venv': + command => "sudo -u ${billing_user} python3 -m venv ${data_path}/.venv", + path => '/usr/bin', + creates => "${data_path}/.venv", + notify => Exec['azure_billing_prepare_pip'], + require => [User[$billing_user], File[$data_path], Package['python3-venv']], + } + + # run pip install if there is any changes in the scripts + exec { 'azure_billing_prepare_pip': + command => 'rm -f /var/lib/azure_billing/.installed', + path => '/usr/bin', + refreshonly => true, + notify => Exec['azure_billing_pip_install'], + require => Exec['azure_billing_venv'], + } + + exec { 'azure_billing_pip_install': + command => "sudo -u ${billing_user} ${data_path}/.venv/bin/pip install -r ${install_path}/requirements.txt && touch ${installed_flag}", + path => '/usr/bin', + refreshonly => true, + creates => $installed_flag, + require => User[$billing_user], + } + + # Create the service and configuration + file {'/etc/default/azure-billing-report': + ensure => present, + content => template('profile/azure_billing_report/azure-billing-report.default.erb'), + owner => $billing_user, + group => 'root', + mode => '0660', + } + + $service_basename = 'azure-billing-report' + + ::systemd::timer { "${service_basename}.timer": + timer_content => template('profile/azure_billing_report/azure-billing-report.timer.erb'), + service_content => template('profile/azure_billing_report/azure-billing-report.service.erb'), + service_unit => "${service_basename}.service", + active => true, + enable => true, + } + +} diff --git a/site-modules/profile/templates/azure_billing_report/azure-billing-report.default.erb b/site-modules/profile/templates/azure_billing_report/azure-billing-report.default.erb new file mode 100644 --- /dev/null +++ b/site-modules/profile/templates/azure_billing_report/azure-billing-report.default.erb @@ -0,0 +1,8 @@ +## +# File managed by puppet (class profile::azure_billing_report), changes will be lost. + +LOGIN=<%= @azure_user %> +PASSWORD=<%= @azure_password %> +DATA_DIRECTORY=<%= @data_path %> +INSTALL_DIRECTORY=<%= @install_path %> +DEBUG=true diff --git a/site-modules/profile/templates/azure_billing_report/azure-billing-report.service.erb b/site-modules/profile/templates/azure_billing_report/azure-billing-report.service.erb new file mode 100644 --- /dev/null +++ b/site-modules/profile/templates/azure_billing_report/azure-billing-report.service.erb @@ -0,0 +1,16 @@ +## +# File managed by puppet (class profile::azure_billing_report), changes will be lost. + +[Unit] +Description=Refresh azure billing report +After=network-online.target +Wants=network-online.target + +[Install] +WantedBy=multi-user.target + +[Service] +Type=simple +User=<%= @billing_user %> +EnvironmentFile=/etc/default/azure-billing-report +ExecStart=<%= @install_path %>/refresh_azure_report.sh diff --git a/site-modules/profile/templates/azure_billing_report/azure-billing-report.timer.erb b/site-modules/profile/templates/azure_billing_report/azure-billing-report.timer.erb new file mode 100644 --- /dev/null +++ b/site-modules/profile/templates/azure_billing_report/azure-billing-report.timer.erb @@ -0,0 +1,13 @@ +## +# File managed by puppet (class profile::azure_billing_report), changes will be lost. +[Unit] +Description=Azure billing report refresh trigger + +[Install] +WantedBy=timers.target + +[Timer] +Persistent=true +OnCalendar=daily UTC +AccuracySec=1h +Unit=<%= @service_basename %>.service diff --git a/site-modules/role/manifests/swh_azure_billing_report.pp b/site-modules/role/manifests/swh_azure_billing_report.pp new file mode 100644 --- /dev/null +++ b/site-modules/role/manifests/swh_azure_billing_report.pp @@ -0,0 +1,4 @@ +# +class role::swh_azure_billing_report inherits role::swh_base { + include profile::azure_billing_report +}