VM Cloud Migration dashboard using SQL Analytics

[travis]

I have recently been working with a customer during their cloud migration, they found using a dashboard to keep track of the progress of the migration has been extremely useful.
A great example in the screenshot below is the gauge tile, which we have used to display the percentage of migrated virtual machines (VMs).
In this particular use case, we’re focusing on migrating VMs from VMware to Azure. The visual representation helps teams stay informed on the progress of the migration.

Here are the steps to create this tile:

1. The first step is to create a new tile and enable SQL Analytics. This will allow you to add multiple datasets:

2. The next step is to configure your first dataset. In this example I am using the Availability Data Stream, with a dynamic selection of all my hosts in Azure.
   Dataset 1:

3. Once you have configured your first dataset, click the + to create a second dataset. Here I have used the Health State / VM data stream and used a dynamic selection of all my VMs in VMware.
   Dataset 2:

Tip💡:
Clicking on the name of the dataset will allow you to rename it. By default they will be named dataset1 and dataset2:

4. Now you have both datasets configured it’s time to use our SQL query. Click on SQL, paste the query and click ‘Execute’.

Here is the SQL query, this query counts the number of rows in each dataset to provide a total number of virtual machines, it then works out the percentage that belong to Azure.
Note: You will need to replace the dataset names in the query with your own (Azure, VMware).

SELECT
  (SELECT COUNT(*) FROM Azure) AS Azure_rows,
  (SELECT COUNT(*) FROM VMware) AS VMware_rows,
  ROUND((SELECT COUNT(*) FROM Azure) * 100 / ((SELECT COUNT(*) FROM Azure) + (SELECT COUNT(*) FROM VMware)), 2) AS percentage

5. Once you have input the query clicked the ‘Execute’ button to run it, you can choose the gauge visualisation and ensure the data mapping value is set to Percentage.

Tip :
To give the gauge more of a visual impact you could enable monitoring:

I have configured monitoring with the conditions set so if my VM migration is less than 75% complete I want the tile to roll up in an error state.

Here is a preview of the complete dashboard. I have used the gauge tile as a starting point, then added some scalar tiles below it to provide some additional insight: