diff --git a/site-modules/profile/files/azure_billing_report/compute_stats.py b/site-modules/profile/files/azure_billing_report/compute_data.py similarity index 94% rename from site-modules/profile/files/azure_billing_report/compute_stats.py rename to site-modules/profile/files/azure_billing_report/compute_data.py index a4f2ad3e..52a8dd5d 100644 --- a/site-modules/profile/files/azure_billing_report/compute_stats.py +++ b/site-modules/profile/files/azure_billing_report/compute_data.py @@ -1,115 +1,124 @@ ## # 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 json 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") + ## + # Load balance information + ## + balances = None + + with open(f"{output_dir}/balances.json", "r") as balancesFile: + balances = json.load(balancesFile) + ## # 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) + index = template.render(generated_date=generated_date, balances=balances) 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_data.py similarity index 76% rename from site-modules/profile/files/azure_billing_report/get_csv.py rename to site-modules/profile/files/azure_billing_report/get_data.py index fa8b5b20..ebb2e4d8 100644 --- a/site-modules/profile/files/azure_billing_report/get_csv.py +++ b/site-modules/profile/files/azure_billing_report/get_data.py @@ -1,112 +1,136 @@ ## # 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 json 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" +EXPECTED_CSV_FILE = "AzureUsage.csv" +BALANCE_FILE = "balances.json" 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: + while not os.path.exists(EXPECTED_CSV_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") + print(f"Usage csv file downloaded and available in the {EXPECTED_CSV_FILE} file") + + print("Downloading balances information") + BALANCE_URL = f"{BASE_SPONSORSHIP_URL}/Balance" + print(f"Balance url: {BALANCE_URL}") + + driver.get(BALANCE_URL) + + wait.until(EC.visibility_of_element_located((By.ID, "balance-container"))) + if DEBUG: + driver.save_screenshot("balances.png") + + balances = { + "usedCredits": driver.find_element(by=By.CSS_SELECTOR, value="span.used-balance.value").text, + "remainingCredits": driver.find_element(by=By.CSS_SELECTOR, value="span.remaining-balance.value").text, + "remainingDays": driver.find_element(by=By.CSS_SELECTOR, value="span.remainingDays").text + } + + print("Writing balance information in {BALANCE_FILE}") + with open(BALANCE_FILE, "w") as balanceFile: + json.dump(balances, balanceFile) driver.close() + + print("Done") diff --git a/site-modules/profile/files/azure_billing_report/index.html.tmpl b/site-modules/profile/files/azure_billing_report/index.html.tmpl index cb461f8c..d08a86ac 100644 --- a/site-modules/profile/files/azure_billing_report/index.html.tmpl +++ b/site-modules/profile/files/azure_billing_report/index.html.tmpl @@ -1,32 +1,43 @@ Azure reporting ({{ generated_date.strftime('%Y-%m-%d') }})

Azure reporting until the {{ generated_date.strftime('%Y-%m-%d') }}

+ +

Balances

+

+ All time consumption: {{ "{:,.2f}".format(balances.usedCredits | float).replace(",", " ") }} $ +

+

+ Remaining credits : {{ "{:,.2f}".format(balances.remainingCredits | float).replace(",", " ") }} $ +

+

+ {{ balances.remainingDays }} +

Cost per day


Raw data: html / markdown

Cost per month


Raw sdata: html / markdown

Cost per service per day

Raw data: html / markdown

- +

Cost per service per month

Raw data: 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 index 44dda782..cd415820 100755 --- a/site-modules/profile/files/azure_billing_report/refresh_azure_report.sh +++ b/site-modules/profile/files/azure_billing_report/refresh_azure_report.sh @@ -1,26 +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 + ${DATA_DIRECTORY}/.venv/bin/python3 ${INSTALL_DIRECTORY}/get_data.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} +${DATA_DIRECTORY}/.venv/bin/python3 ${INSTALL_DIRECTORY}/compute_data.py ${DATA_DIRECTORY} echo "Report refreshed."