Welcome to the final piece of the puzzle: displaying our metrics. We now have a pile of bits in our database, and now it needs to be displayed in a meaningful way.
As you may recall from the previous parts of this series, we first built a PowerShell script which gathers information from the local machine. Using the Windows task scheduler, the script is set to run at particular intervals. In the case of the example, the interval is one minute.
The PowerShell “collector” then posted the data to the “ingestor”. The Ingestor that I demonstrated was written with classic ASP. Why ASP? Because it is simple, quick, and it’s what I am most comfortable with. The ingestor receives the data from the collector(s), validates it, performs any necessary “massage” of the data, and then pokes it into the database.
For the database, I’m using MySQL 5.7. For this example, it works great. Properly tweaked, it can handle massive datasets, so it would be relatively easy to move this to a cluster MySQL instance on big hardware, or even up to a cloud service such as Amazon.
I’ll save you some trouble; here is the script that will create the database:
CREATE DATABASE `psmetrics` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE TABLE `tblmain` (
`RecordID` int(11) NOT NULL AUTO_INCREMENT,
`Tree` text,
`SubTree` text,
`Metric` text,
`cValue` decimal(10,0) DEFAULT NULL,
`DateStamp` datetime DEFAULT NULL,
PRIMARY KEY (`RecordID`),
UNIQUE KEY `RecordID_UNIQUE` (`RecordID`)
) ENGINE=InnoDB AUTO_INCREMENT=6478 DEFAULT CHARSET=utf8;
Now, we simply need some way to display the metrics on screen. I’ve thrown together a quick and dirty ASP script that will pull the data and poke it into a chart, using Google Charts.
The code is pretty straightforward. First, we define the CSS stylesheet to use, and set up the database connection.
Next, any values that are passed via form are retrieved, and trimmed down. Using any values that are passed, the form is then built, with the selected values matching any values that were passed through the session.
Afte the form is built, all three values are checked for a length of more than 1. If all three values are present, the database is queried, and the Google chart is built. In this example, the last 120 minutes of data is grabbed and charted.
Code for the whole script is posted below.
And that’s it. Easy as pie. I hope these concepts will help you in building out your own metrics monitoring solution. Or take these scripts and build on them. The sky is the limit!
Code for ASP Metrics Display below; watch for line wrap.
<html>
<head>
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<%
CFG_Conn="Driver={MySQL ODBC 5.3 ANSI Driver};Server=192.168.1.114;Database=PSMetrics;Uid=psmetrics;Pwd=psmetrics;"
Set objConn = server.CreateObject("ADODB.Connection")
objConn.ConnectionString=CFG_Conn
PSTree=Request.Form("Tree")
PSSubTree=Request.Form("SubTree")
PSMetric=Request.Form("Metric")
PSTree=Trim(PSTree)
PSSubTree=Trim(PSSubTree)
PSMetric=Trim(PSMetric)
%>
<body>
<div id="header">
<h1>PSMetrics</h1>
</div>
<div id="content">
<div id="col1">
<form method="post" action="default.asp" id="frmMakeMetrics" name="frmMakeMetrics">
<p>
<select name="Tree">
<option selected value="0">None Selected</option>
<%
strSQL="SELECT distinct Tree FROM tblmain order by Tree desc"
objConn.Open
Set objRS=objConn.Execute(strSQL)
Do While not objRS.EOF
TreeName=objRS("Tree")
If TreeName=PSTree then
%> <option selected value="<% =TreeName %>"><% =TreeName %></option>
<%
Else
%> <option value="<% =TreeName %>"><% =TreeName %></option>
<%
end if
objRS.MoveNext
Loop
objRS.Close
Set objRS=Nothing
objConn.Close
%> </select>
</p>
<p>
<select name="SubTree">
<option selected value="0">None Selected</option>
<%
strSQL="SELECT distinct SubTree FROM tblmain order by SubTree desc"
objConn.Open
Set objRS=objConn.Execute(strSQL)
Do While not objRS.EOF
STName=objRS("SubTree")
if STName=PSSubTree then
%> <option selected value="<% =STName %>"><% =STName %></option>
<%
else
%> <option value="<% =STName %>"><% =STName %></option>
<%
end if
objRS.MoveNext
Loop
objRS.Close
Set objRS=Nothing
objConn.Close
%> </select>
</p>
<p>
<select name="Metric">
<option selected value="0">None Selected</option>
<%
strSQL="SELECT distinct Metric FROM tblmain order by SubTree desc"
objConn.Open
Set objRS=objConn.Execute(strSQL)
Do While not objRS.EOF
MetricName=objRS("Metric")
if MetricName=PSMetric then
%> <option selected value="<% =MetricName %>"><% =MetricName %></option>
<%
else
%> <option value="<% =MetricName %>"><% =MetricName %></option>
<%
end if
objRS.MoveNext
Loop
objRS.Close
Set objRS=Nothing
objConn.Close
%> </select>
</p>
<p>
<input style="float:right;" type="submit" id="btnSubmit" name="btnSubmit" value="Get Metrics" />
</p>
</form>
</div>
<div id="col3">
<%
if len(PSTree)>1 and len(PSSubTree)>1 and len(PSMetric)>1 then
strSQL="select * from tblmain where Tree='" & PSTree & "' and subtree='" &_
PSSubTree & "' and Metric='" & PSMetric & "' order by datestamp desc limit 120"
objConn.Open
set objRS=objConn.Execute(strSQL)
%>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['TimeStamp', 'Measurement'],<%
Do While not objRS.EOF
cValue=objRS("cValue")
DateStamp=objRS("DateStamp")
response.write "['" & DateStamp & "'," & cValue & "],"
objRS.Movenext
Loop
%>]);
var options = {
title: 'Latest 120 minutes'
};
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
<div id="chart_div" style="width: 1000px; height: 500px;"></div>
<%
objRS.Close
Set objRS=Nothing
objConn.Close
end if
%>
</div>
</div>
</body>
</html>