Export munin data to SQL.

rdd-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);