I'm trying to find a quick way to take Cloudwatch metrics and graph them in excel because it's too much data for Cloudwatch to render in one API call. I only need one metric, but I need it from 526 EC2 instances and I need it for several months.
So far I've fetched all the data
mkdir /tmp/i
jq -r '.Reservations[].Instances[]|select([.Tags[].Value == "'"$tag"'"]|any).InstanceId' /tmp/instances \
| xargs -n1 -P6 sh -c 'aws cloudwatch get-metric-statistics \
--namespace the_namespace --metric-name the_metric_name \
--dimensions Name=InstanceId,Value=$0 \
--statistics Average \
--start-time $start \
--end-time $end \
--period 86400 \
--region us-west-2 | tee /tmp/i/$0'
And can dump the data in CSV format:
(cd /tmp/i; find . -type f -print0 | xargs --null -n1 sh -c "jq -c '.Datapoints[]|[\"'\$0'\",.Timestamp,.Average]|@csv' /tmp/i/\$0" | sort)
Now there are two challenges:
The data is sparse. If an instance was stopped during a period there won't be a zero datapoint, there will be no datapoints. I think Excel can handle this if I set the X axis values for the timeseries
The series are all in one big column. For example:
....
"./i-oneoneoneoneoneone","2021-02-04T00:00:00Z",0
"./i-oneoneoneoneoneone","2021-02-05T00:00:00Z",0.0002314814814814815
"./i-oneoneoneoneoneone","2021-02-06T00:00:00Z",0
"./i-oneoneoneoneoneone","2021-02-07T00:00:00Z",0
"./i-oneoneoneoneoneone","2021-02-08T00:00:00Z",0
"./i-oneoneoneoneoneone","2021-02-09T00:00:00Z",0
"./i-oneoneoneoneoneone","2021-02-10T00:00:00Z",0
"./i-twotwotwotwotwotwo","2020-11-15T00:00:00Z",0
"./i-twotwotwotwotwotwo","2020-11-16T00:00:00Z",0
"./i-twotwotwotwotwotwo","2020-11-17T00:00:00Z",0
"./i-twotwotwotwotwotwo","2020-11-18T00:00:00Z",0
"./i-twotwotwotwotwotwo","2020-11-19T00:00:00Z",0
"./i-twotwotwotwotwotwo","2020-11-20T00:00:00Z",0
...
I imagine Excel wants them more like:
"","i-oneoneoneoneoneone","","i-twotwotwotwotwotwo", ...
"2021-02-04T00:00:00Z",0,"2020-11-15T00:00:00Z",0
"2021-02-05T00:00:00Z",0.0002314814814814815,"2020-11-16T00:00:00Z",0
"2021-02-06T00:00:00Z",0,"2020-11-16T00:00:00Z",0
"2021-02-07T00:00:00Z",0,"2020-11-17T00:00:00Z",0
"2021-02-08T00:00:00Z",0,"2020-11-18T00:00:00Z",0
"2021-02-09T00:00:00Z",0,"2020-11-19T00:00:00Z",0
"2021-02-10T00:00:00Z",0,"2020-11-20T00:00:00Z",0
...
Where series are in adjacent pairs of (x,y) columns. Seeing as I have hundreds of these, I would really hate to have to cut & paste them.