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.
janhoedtAsked:
Who is Participating?
 
Dustin SaundersDirector of OperationsCommented:
Yeah, it should scale automatically-- but your labels will get squished if you aren't careful.  But in your Powershell code you can average or aggregate totals before generating the chart if that is a problem.

For a DateTime object you can use $obj.Year, $obj.Month, $obj.Day to get those values.

$dataPoints = ""
foreach ($row in $table.Rows)
{
    $dataPoints += "{ x: new Date($($row.Date.Year),$($row.Date.Month),$($row.Date.Day)), y: $($row.NumberOfUsers) },"
}

Open in new window


Then for each graph just repeat that process with the data you want to show.
1
 
Dustin SaundersDirector of OperationsCommented:
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.
1
 
janhoedtAuthor Commented:
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 ...?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dustin SaundersDirector of OperationsCommented:
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

1
 
janhoedtAuthor Commented:
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

0
 
janhoedtAuthor Commented:
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

0
 
janhoedtAuthor Commented:
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 ...?
0
 
janhoedtAuthor Commented:
Great input, but tell me one more thing in your solution:
$dataPoints contains a comma at the end. How do you remove it (Powershell)?
0
 
Dustin SaundersDirector of OperationsCommented:
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

1
 
janhoedtAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.