Skip to content

Instantly share code, notes, and snippets.

@raelga
Last active October 30, 2021 05:23
  • Star 4 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save raelga/5926594 to your computer and use it in GitHub Desktop.
Export munin data to SQL.
echo 'DROP TABLE munin;' > munin.sql
echo 'CREATE TABLE munin (id SERIAL PRIMARY KEY, node VARCHAR(10), data VARCHAR(50), value FLOAT, time TIMESTAMP WITH TIME ZONE);' >> munin.sql;
echo 'BEGIN;' >> munin.sql;
for rrd in *rrd;
do
rrdtool dump $rrd $rrd.xml;
host=`echo $rrd | sed 's/\(.*\)_\(.*\)\.rrd/\1/'`;
data=`echo $rrd | sed 's/\(.*\)_\(.*\)\.rrd/\2/'`;
sed -n "s@.*-- \(.* CEST\).*<v>\(.*\)</v></row>@INSERT INTO munin (node,data,value,time) VALUES (\'$host\',\'$data\',\'\2\',\'\1\');@p" $rrd.xml >> munin.sql;
rm $rrd.xml;
done;
echo 'COMMIT;' >> munin.sql;
# SELECT date_trunc('month',ss.d) d, ROUND(SUM(ss.used))/100 u, ROUND(AVG(ss.reserved)) r, ROUND(SUM(ss.memory))/1073741824 r
# FROM ( SELECT m.node n, date_trunc('month',m.time) d,
# AVG(coalesce(m.value, '0')) used,
# AVG(coalesce(s.value, '0')) reserved,
# AVG(coalesce(c.value, '0')) memory
# FROM munin m, munin s, munin c
# WHERE s.time=m.time AND s.time=c.time AND c.time=m.time
# AND s.data='slurm-cpus-slurm-cpus-reserved-g'
# AND m.data='cpu-user-d'
# AND c.data='memory-active-g'
# AND m.value<>'NaN'
# AND c.value<>'NaN'
# AND s.value<>'NaN'
# GROUP BY m.node, date_trunc('month',m.time)
# ) ss
# GROUP BY date_trunc('month',ss.d)
# ORDER BY date_trunc('month',ss.d);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment