How to collect bandwidth data for each day/month for every VM and CR in SolusVM 2

Question

How to collect bandwidth data for each day/month for every VM and CR in SolusVM 2?

Answer

The script below is a workaround to get and aggravate the data that is already in SolusVM 2 database.

The resulting file which is defined by OUTPUT_FILE variable will contain bandwidth consumed values for VMs and CRs:

#!/bin/bash

CONTAINER_ID=$(docker ps -q -f name=solus_postgres | head -n1)

POSTGRES_USER=$(docker exec $CONTAINER_ID env | grep POSTGRES_USER | awk -F "=" {'print $2'})
POSTGRES_DB=$(docker exec $CONTAINER_ID env | grep POSTGRES_DB | awk -F "=" {'print $2'})

OUTPUT_FILE="/root/bw_$(date +\%F).csv"

echo "VMs Bandwidth usage" > $OUTPUT_FILE
DETAILED_QUERY="COPY (SELECT id, name, (usage->'network'->'incoming'->>'value')::bigint / (1024 * 1024) AS incoming_MB, (usage->'network'->'outgoing'->>'value')::bigint / (1024 * 1024) AS outgoing_MB, ((usage->'network'->'incoming'->>'value')::bigint / (1024 * 1024) + (usage->'network'->'outgoing'->>'value')::bigint / (1024 * 1024)) AS total_MB FROM compute_resource_vms) TO STDOUT WITH CSV HEADER"

docker exec -i $CONTAINER_ID psql -U $POSTGRES_USER -d $POSTGRES_DB -c "$DETAILED_QUERY" >> $OUTPUT_FILE

echo "" >> $OUTPUT_FILE
echo "Total CR usage" >> $OUTPUT_FILE
TOTALS_QUERY="COPY (SELECT compute_resource_id, SUM((usage->'network'->'incoming'->>'value')::bigint) / (1024 * 1024) AS total_incoming_MB, SUM((usage->'network'->'outgoing'->>'value')::bigint) / (1024 * 1024) AS total_outgoing_MB, (SUM((usage->'network'->'incoming'->>'value')::bigint) / (1024 * 1024) + SUM((usage->'network'->'outgoing'->>'value')::bigint) / (1024 * 1024)) AS total_MB FROM compute_resource_vms GROUP BY compute_resource_id) TO STDOUT WITH CSV HEADER"

docker exec -i $CONTAINER_ID psql -U $POSTGRES_USER -d $POSTGRES_DB -c "$TOTALS_QUERY" >> $OUTPUT_FILE