janhoedt
asked on
Powershell: Create html graph of trend: number if users on rds in time?
Hi,
I data into SQL database usage of an RDS server.
Now I would like to show this in an HTML page, using power shell. F.e. 4 th January 8 am, 30 users, 10 am 50 users etc. then the sale for next days whole year round.
I can create the HTML via power shell, but how to build/show the graph?
J.
I data into SQL database usage of an RDS server.
Now I would like to show this in an HTML page, using power shell. F.e. 4 th January 8 am, 30 users, 10 am 50 users etc. then the sale for next days whole year round.
I can create the HTML via power shell, but how to build/show the graph?
J.
ASKER
Thanks but not clear howto implement that via powershell.
What I got now = $table
Which is TypeName: System.Data.DataRow
$table =
NumberOfUsers Date
------------- ----
367 16/01/2018 12:24:00
188 16/01/2018 17:07:00
173 16/01/2018 17:14:00
152 16/01/2018 17:30:00
143 16/01/2018 17:34:00
165 16/01/2018 17:20:00
367 16/01/2018 12:24:00
188 16/01/2018 17:07:00
173 16/01/2018 17:14:00
152 16/01/2018 17:30:00
143 16/01/2018 17:34:00
165 16/01/2018 17:20:00
How do I get this in a graph then ...?
What I got now = $table
Which is TypeName: System.Data.DataRow
$table =
NumberOfUsers Date
------------- ----
367 16/01/2018 12:24:00
188 16/01/2018 17:07:00
173 16/01/2018 17:14:00
152 16/01/2018 17:30:00
143 16/01/2018 17:34:00
165 16/01/2018 17:20:00
367 16/01/2018 12:24:00
188 16/01/2018 17:07:00
173 16/01/2018 17:14:00
152 16/01/2018 17:30:00
143 16/01/2018 17:34:00
165 16/01/2018 17:20:00
How do I get this in a graph then ...?
So if you have $table which is a DataTable, just have the page template set up however you want and for the chart data generate that with your Powershell.
And insert those values into your HTML template in that part of the script:
$dataPoints = ""
foreach ($row in $table.Rows)
{
$dataPoints += '{ x: "' + $row.Date.ToShortDateString() + '", y: ' + $row.NumberOfUsers.ToString() + ' },'
}
And insert those values into your HTML template in that part of the script:
indexLabelPlacement: "outside",
dataPoints: [
#THIS IS WHERE YOUR DATAPOINTS GOES
]
}]
ASKER
Thanks. Tried to make the html but output is empty.
Probably because date is all the same and datetime should also contain seconds(?)
Probably because date is all the same and datetime should also contain seconds(?)
<!DOCTYPE HTML>
<html>
<head>
<script> window.onload = function () {var chart = new CanvasJS.Chart("chartContainer", {
animationEnabled: true,
exportEnabled: true,
theme: "light1", // "light1", "light2", "dark1", "dark2"
title:{
text: "Simple Column Chart with Index Labels"
},
data: [{
type: "column", //change type to bar, line, area, pie, etc
//indexLabel: "{y}", //Shows y value on all Data Points
indexLabelFontColor: "#5A5757",
indexLabelPlacement: "outside",
dataPoints: [
{ x: "16/01/2018", y: 367 },{ x: "16/01/2018", y: 188 },{ x: "16/01/2018", y: 173 },{ x: "16/01/2018", y: 152 },{ x: "16/01/2018", y: 143 },{ x: "16/01/2018", y: 165 },{ x: "16/01/2018", y: 367 },{ x: "16/01/2018", y: 188 },{ x: "16/01/2018", y
: 173 },{ x: "16/01/2018", y: 152 },{ x: "16/01/2018", y: 143 },{ x: "16/01/2018", y: 165 }
]
}]
});
chart.render();
}
</script>
</head>
<body>
<div id="chartContainer" style="height: 370px; width: 100%;"></div>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>
</body>
</html>
ASKER
This works and is what I want to achieve ... but have to add seconds (will it scale when I add each hour an entry during months?) +
how do I get the output from Powershell into this html?
how do I get the output from Powershell into this html?
<!DOCTYPE HTML>
<html>
<head>
<script>
window.onload = function () {
var chart = new CanvasJS.Chart("chartContainer", {
animationEnabled: true,
title:{
text: "RDS Users"
},
axisX: {
valueFormatString: "DD MMM,YY"
},
axisY: {
title: "Number of Users",
includeZero: false,
},
legend:{
cursor: "pointer",
fontSize: 16,
itemclick: toggleDataSeries
},
toolTip:{
shared: true
},
data: [{
name: "TOTAL",
type: "spline",
yValueFormatString: "#0.## °C",
showInLegend: true,
dataPoints: [
{ x: new Date(2017,6,24), y: 42},
{ x: new Date(2017,6,25), y: 39 },
{ x: new Date(2017,6,26), y: 49 },
{ x: new Date(2017,6,27), y: 49 },
{ x: new Date(2017,6,28), y: 48 },
{ x: new Date(2017,6,29), y: 48 },
{ x: new Date(2017,6,30), y: 48 }
]
},
{
name: "Team01",
type: "spline",
yValueFormatString: "#0.## °C",
showInLegend: true,
dataPoints: [
{ x: new Date(2017,6,24), y: 20 },
{ x: new Date(2017,6,25), y: 20 },
{ x: new Date(2017,6,26), y: 25 },
{ x: new Date(2017,6,27), y: 25 },
{ x: new Date(2017,6,28), y: 25 },
{ x: new Date(2017,6,29), y: 25 },
{ x: new Date(2017,6,30), y: 25 }
]
},
{
name: "Team02",
type: "spline",
yValueFormatString: "#0.## °C",
showInLegend: true,
dataPoints: [
{ x: new Date(2017,6,24), y: 22 },
{ x: new Date(2017,6,25), y: 19 },
{ x: new Date(2017,6,26), y: 23 },
{ x: new Date(2017,6,27), y: 24 },
{ x: new Date(2017,6,28), y: 24 },
{ x: new Date(2017,6,29), y: 23 },
{ x: new Date(2017,6,30), y: 23 }
]
}]
});
chart.render();
function toggleDataSeries(e){
if (typeof(e.dataSeries.visible) === "undefined" || e.dataSeries.visible) {
e.dataSeries.visible = false;
}
else{
e.dataSeries.visible = true;
}
chart.render();
}
}
</script>
</head>
<body>
<div id="chartContainer" style="height: 370px; width: 100%;"></div>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Note that I check every hour so I'd need the data for every hour, f.e. 8 hour would then be:
{ x: new Date(2017,6,24,8,00), y: 42},
How do I get this in the graph? I guess I need to change
axisX: {
valueFormatString: "DD MMM,YY"
but this is confusing to me, it is DD MMM yy, whereas I see 2017,6,24 in the info I add?
I would change it to DD,MM,YYYY,HH,MM ...?
{ x: new Date(2017,6,24,8,00), y: 42},
How do I get this in the graph? I guess I need to change
axisX: {
valueFormatString: "DD MMM,YY"
but this is confusing to me, it is DD MMM yy, whereas I see 2017,6,24 in the info I add?
I would change it to DD,MM,YYYY,HH,MM ...?
ASKER
Great input, but tell me one more thing in your solution:
$dataPoints contains a comma at the end. How do you remove it (Powershell)?
$dataPoints contains a comma at the end. How do you remove it (Powershell)?
I would think "DD MM YYYY HH:mm" would get you what you are looking for.
To remove the last character from a string, you can do a substring of said string and cut the length by 1 character. After the foreach loop:
To remove the last character from a string, you can do a substring of said string and cut the length by 1 character. After the foreach loop:
$dataPoints = $dataPoints.Substring(0,$dataPoints.Length -1)
ASKER
Great, thanks!
Last thing I'm looking for: howto get the $datapoint in an $html output.
When you add the $datapoints in an $html variable, the quotes are a disaster ....
Last thing I'm looking for: howto get the $datapoint in an $html output.
When you add the $datapoints in an $html variable, the quotes are a disaster ....
$html = "<html>
<head>
<script>
window.onload = function () {
var chart = new CanvasJS.Chart("chartContainer", {
animationEnabled: true,
title:{
text: "RDS Users"
},
axisX: {
valueFormatString: "DD MMM,YY"
},
axisY: {
title: "Number of Users",
includeZero: false,
},
legend:{
cursor: "pointer",
fontSize: 16,
itemclick: toggleDataSeries
},
toolTip:{
shared: true
},
data: [{
name: "TOTAL",
type: "spline",
yValueFormatString: "#0.## ",
showInLegend: true,
dataPoints: [
$datapoints
]
},
{
name: "Team01",
type: "spline",
yValueFormatString: "#0.## ",
showInLegend: true,
dataPoints: [
{ x: new Date(2017,6,24), y: 20 },
{ x: new Date(2017,6,25), y: 20 },
{ x: new Date(2017,6,26), y: 25 },
{ x: new Date(2017,6,27), y: 25 },
{ x: new Date(2017,6,28), y: 25 },
{ x: new Date(2017,6,29), y: 25 },
{ x: new Date(2017,6,30), y: 25 }
]
},
{
name: "Team02",
type: "spline",
yValueFormatString: "#0.## ",
showInLegend: true,
dataPoints: [
{ x: new Date(2017,6,24), y: 22 },
{ x: new Date(2017,6,25), y: 19 },
{ x: new Date(2017,6,26), y: 23 },
{ x: new Date(2017,6,27), y: 24 },
{ x: new Date(2017,6,28), y: 24 },
{ x: new Date(2017,6,29), y: 23 },
{ x: new Date(2017,6,30), y: 23 }
]
}]
});
chart.render();
function toggleDataSeries(e){
if (typeof(e.dataSeries.visible) === "undefined" || e.dataSeries.visible) {
e.dataSeries.visible = false;
}
else{
e.dataSeries.visible = true;
}
chart.render();
}
}
</script>
</head>
<body>
<div id="chartContainer" style="height: 370px; width: 100%;"></div>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>
</body>
</html>"
https://canvasjs.com/jquery-charts/
It's simple code to create, see example:
https://canvasjs.com/javascript-charts/chart-index-data-label/
Open in new window
Basically just write the chart data into that script from your powershell as you generate the HTML. If you need the chart to display on pages that don't have internext access, you can download the js and reference it locally somewhere.