get_metrics.py

import boto3
import sqlite3
from datetime import datetime
from dateutil import tz

CLUSTER = 'sh-runners'
REGION = 'us-east-1'

ecs_client = boto3.client('ecs', region_name=REGION)

# conn = sqlite3.connect('ecs_data_v1.sqlite') # Disabled, used for the first solution
conn = sqlite3.connect('ecs_data_v2.sqlite') # Used for the second solution
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS tasks (
        createdAt TEXT,
        startedAt TEXT,
        timeDifferenceInSeconds REAL,
        taskFamily TEXT
    )
''')

response = ecs_client.list_tasks(
    cluster=CLUSTER
)
TASKS = response.get('taskArns', [])

if TASKS:
    response = ecs_client.describe_tasks(
        tasks=TASKS,
        cluster=CLUSTER
    )
    tasks = response['tasks']

    for task in tasks:
        created_at = task['createdAt'].astimezone(tz.tzlocal()).strftime('%Y-%m-%d %H:%M:%S')
        started_at = task['startedAt'].astimezone(tz.tzlocal()).strftime('%Y-%m-%d %H:%M:%S')
        time_difference = (task['startedAt'] - task['createdAt']).total_seconds()
        task_family = task['group']

        cursor.execute('''
            INSERT INTO tasks (createdAt, startedAt, timeDifferenceInSeconds, taskFamily)
            VALUES (?, ?, ?, ?)
        ''', (created_at, started_at, time_difference, task_family))

        conn.commit()

        print(f"Saved data: {created_at}, {started_at}, {time_difference}, {task_family}")

    print("All data has been saved in the database.")

conn.close()