Having Fun with SquaredUp and Powershell

With the SquaredUp Dashboard Server Standalone and PowerShell, you can create a variety of attractive visualizations. Whether it’s for personal use, your team, or even executive management — donuts and bar charts often communicate more than raw data when dealing with complex information.

Since I don’t have large amounts of data privately, I used the data from my World of Warcraft characters as sample data. These data are stored in a MySQL database. However, SquaredUp doesn’t support MySQL directly — only MSSQL. Fortunately, PowerShell can access MySQL databases. To make this work, you’ll need an appropriate PowerShell module. I use the SimplySql module from the PowerShell Gallery.

To avoid repeating the database connection details in every tile, I created a profile under “PowerShell” in the SquaredUp settings. This profile contains a script that runs before the script in each tile. This way, the script in the tile can simply reuse the same variables and utilize the already established MySQL connection.

If you stick with the default settings, the results might look a bit bland. But if you take some time to explore the details, you can design truly beautiful dashboards. Here’s an example:

Information can be beautifully presented with images instead of displaying a legend. The PowerShell script is quite concise and gets the job done without unnecessary complexity.

$characters = Invoke-SqlQuery -ConnectionName $ConnectionName -Query 'SELECT * FROM characters'
$characters | Group-Object -Property faction | Select-Object -Property Name, Count

To ensure that the donut colors match the images, you need to adjust them in the JSON, since the GUI only allows you to choose from twelve predefined colors. So, you first configure the necessary settings in the GUI and then modify them in the JSON. For this kind of graphic, it’s easiest to use the label name. Examples of these conditions can be found under the {{}} button.

If you now take a look at the JSON, you’ll find an entry called “customColours” where you can easily adjust the RGB color values.

"customColours": [
	{
		"key": "rgb(153, 0, 0)",
		"value": "{{label.match('Horde') != null}}"
	},
	{
		"key": "rgb(0, 51, 153)",
		"value": "{{label.match('Allianz') != null}}"
	}
]

This also works with significantly more values than just two. Here’s an example with 13 values.

With a bit of effort, even a simple list view can be enhanced with small images or icons. This isn’t done through JSON in this case, but rather via PowerShell. You just need to know where to place the images so that SquaredUp can locate them.

The images must be placed directly in the folder to which the site in IIS points. In my case, this is C:\inetpub\SquaredUp. You can organize the images into different folders there. I stored all images in a folder called images and sorted them by size. To display the images next to the text in the list, I extended the PowerShell object with HTML tags for images.

$characters = Invoke-SqlQuery -ConnectionName $ConnectionName -Query 'SELECT * FROM characters'
foreach($character in $characters)
{
  $character.classname = -join('<img src="\images\18\class_',$character.class,'.png"> ',$character.classname)
  $character.racename = -join('<img src="\images\18\race_',$character.race,'_',$character.gender,'.png"> ',$character.racename)
  $character.specname = -join('<img src="\images\18\spec_',$character.spec,'.png"> ',$character.specname)
}
$characters | Select-Object -Property Name, classname, specname, racename, level, realm | Sort-Object -Descending level

The first object, for example, looks like this:

name      : Strandmaus
classname : <img src="\images\18\class_2.png"> Paladin
specname  : <img src="\images\18\spec_70.png"> Vergeltung
racename  : <img src="\images\18\race_10_1.png"> Blutelf
level     : 80
realm     : Azshara