Here’s another example of how to use the D3 javascript library to produce a Pie Chart this time.
Essentially it’s the same as the bar chart example. I have added labels this time but you’ll get the general idea.
The script is more or less the same as the previous one just with the D3 bits before and after the SQLPLUS command heavily altered.
The results look something like this.
Now I appreciate that this example isn’t something you’d chose to display like this. But all you have to do is change the sql select to something more appropriate and you’re away. You might see that the wrapper is more HTML like this time. Once I’ve worked out what I think would be useful to show, I’m going to do a big catch all email with 4-5 graphs of differing types in one script with parameterised sql so you can use it out of the box, so to speak.
Here is the script…
I keep the email addresses as variables in the .profile, so it’s easier to amend them when someone new joins the team.
#!/bin/sh
. $HOME/.profile 2>/dev/null
tmpfile= `date +%d%b%Y`.html
rm ${tmpfile} 2>/dev/null
cat <<EOF1 >> ${tmpfile}
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-type" content="text/html; charset=utf-8">
<title>Oracle 11g - Pie Chart</title>
<style type="text/css">
.slice text {
font-size: 16pt;
font-family: Arial;
}
</style>
</head>
<body>
<script src="http://d3js.org/d3.v3.min.js"></script>
<script type="text/javascript">
var canvasWidth = 500,
canvasHeight = 500,
outerRadius = 100,
color = d3.scale.category20();
var dataSet = [
EOF1
sqlplus -s / as sysdba <<EOF >> ${tmpfile} set lin 400 set pages 1000 set feedback off set head off
SELECT '{name: "'||ts.tablespace_name||'", value: '||round((fr."FREE(MB)" / df."SIZE(MB)") * 100,2) || '},'
FROM (SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*) "File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+) ORDER BY (fr."FREE(MB)" / df."SIZE(MB)") * 100;
exit
EOF
cat <<EOF2 >> ${tmpfile}
];
var vis = d3.select("body")
.append("svg:svg")
.data([dataSet])
.attr("width", canvasWidth)
.attr("height", canvasHeight)
.append("svg:g") //make a group to hold our pie chart
.attr("transform", "translate(" + 1.5*outerRadius + "," + 1.5*outerRadius + ")")
var arc = d3.svg.arc()
.outerRadius(outerRadius);
var pie = d3.layout.pie()
.value(function(d) { return d.value; })
.sort( function(d) { return null; } );
var arcs = vis.selectAll("g.slice")
.data(pie)
.enter()
.append("svg:g")
.attr("class", "slice");
arcs.append("svg:path")
.attr("fill", function(d, i) { return color(i); } )
.attr("d", arc);
arcs.append("svg:text")
.attr("transform", function(d) {
d.outerRadius = outerRadius + 50;
d.innerRadius = outerRadius + 45;
return "translate(" + arc.centroid(d) + ")";
})
.attr("text-anchor", "middle")
.style("fill", "Purple")
.style("font", "bold 10px Arial")
.text(function(d, i) { return dataSet[i].name; });
arcs.filter(function(d) { return d.endAngle - d.startAngle > .2; }).append("svg:text")
.attr("dy", ".35em")
.attr("text-anchor", "middle")
.attr("transform", function(d) {
d.outerRadius = outerRadius;
d.innerRadius = outerRadius/2;
return "translate(" + arc.centroid(d) + ")rotate(" + angle(d) + ")";
})
.style("fill", "White")
.style("font", "bold 12px Arial")
.text(function(d) { return d.data.value; });
function angle(d) {
var a = (d.startAngle + d.endAngle) * 90 / Math.PI - 90;
return a > 90 ? a - 180 : a;
}
</script>
</body>
</html>
EOF2
echo emailing $MY_EMAIL
( cat <<HERE; uuencode "${tmpfile}" "${tmpfile}" ) | sendmail -oi -t
From: production
To: ${MY_EMAIL}
Subject: Database info $ORACLE_SID from cron job
HERE
echo Done
Happyjohn.
No comments:
Post a Comment