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.

Dustin Saunders

You can display charts with jQuery.
https://canvasjs.com/jquery-charts/

It's simple code to create, see example:
https://canvasjs.com/javascript-charts/chart-index-data-label/

<!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: 10, y: 71 },
			{ x: 20, y: 55 },
			{ x: 30, y: 50 },
			{ x: 40, y: 65 },
			{ x: 50, y: 92, indexLabel: "Highest" },
			{ x: 60, y: 68 },
			{ x: 70, y: 38 },
			{ x: 80, y: 71 },
			{ x: 90, y: 54 },
			{ x: 100, y: 60 },
			{ x: 110, y: 36 },
			{ x: 120, y: 49 },
			{ x: 130, y: 21, indexLabel: "Lowest" }
		]
	}]
});
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>

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.

janhoedt

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 ...?

Dustin Saunders

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.

$dataPoints = ""
foreach ($row in $table.Rows)
{
    $dataPoints += '{ x: "' + $row.Date.ToShortDateString() + '", y: ' + $row.NumberOfUsers.ToString() + ' },'
}

Open in new window

And insert those values into your HTML template in that part of the script:

indexLabelPlacement: "outside",
		dataPoints: [
                      #THIS IS WHERE YOUR DATAPOINTS GOES
		]
	}]

Open in new window

janhoedt

ASKER

Thanks. Tried to make the html but output is empty.
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>

Open in new window

janhoedt

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?

<!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>

Open in new window

ASKER CERTIFIED SOLUTION

Dustin Saunders

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

janhoedt

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 ...?

janhoedt

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)?

Dustin Saunders

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:

$dataPoints = $dataPoints.Substring(0,$dataPoints.Length -1)

Open in new window

janhoedt

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 ....

$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>"

Open in new window