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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.