Skip to main content

Using Azure tags in your powerbi reporting

So you have gone through the hard work of defining tags and its value for all the resources in Azure portal which you want to make use of them when reporting the costing in PowerBi. Here I will describe how you can expose those tags for billing purposes so that you can filter your costing via tags as well.

First of all we need to get the API access key and enrollment number which you will need to ask whoever that has access to your Enterprise agreement information. You can check if you have access by going to https://ea.azure.com in case someone has given you access. Within the EA portal you will see your enrollment number on the top left of the screen which you will need to note down. To get the API access key you will need to click on "reports" then go to "Download Usage > API Access Key". You will see two keys, make sure you have a look at the effective date (remember US date format). Once you have decided which key to use then click on "expand key" and select "copy" to capture the whole key. If both has expired then clic on "regenerate" for one of them.


Now that you have both the enrollment number and API access key then we can move on to working within PowerBi desktop version. The version I will be using is dated as October 2020 (2.86.902.0 64 bit)

When you start up PowerBi desktop if you get the start up screen then select get data otherwise on the menu bar select "Get Data > More"

This should open up a new window and select "Online Services" followed by "Microsoft Azure Consumption Insights (Beta)" then select "Connect"
You will receive a warning about the connector is still under development but I think I have read somewhere below that they are not actually going to develop this anymore. You can click continue to carry on.
You will need to enter your enrollerment number which you have gathered at the start of this task. Once you have entered your enrollment number click "OK"
You will now need the Account Key which is actually the API key that you have gathered as the first step so enter the API key and select "Connect"

To keep it simple I will just select the "UsageDetails" table and click "Load". Now wait for the data to be downloaded which could take some time.

Once loaded you will see on the righthand side there is a "fields" section and if you scroll down you will only see "tags" as the possible selected field. The idea is to try and break out all the tags in this field so that we can can make use of the tags that we have defined and to use those as filters.

On the menu bar select "Transform data > Transform data"
You will be in the power query editor now. Scroll across to find the "Tags" column. You should see all your tag name and values all within this field. Right click on the column to bring up the menu select Transform > JSON. Give it some time to process
Once the transformation is completed click on the icon next to the Tags column as shown below and wait for the tags names to load up.
Select the columns you want and then click "OK". Again wait for the new columns to be created
Once they are created you will see them and they will be prefixed with "tags." followed by your tag names
At the menu of the Power Query Editor select "Close & Apply which would start to apply the query changes.
If you now look at the fields section you will see that the Tags field has disappeared and the tags values that I decided to expand are shown here for me to select.


So now you can build queries and make use of the tag values that you have defined for your resources in your subscriptions.

Comments

Popular posts from this blog

Rolling back a version of ESXi

There is an option in VMware where after you have performed an major upgrade of ESXi you can roll back to your previous version. The benefit of this is that you would not need to reinstall your ESXi and its configuration if you had issues with the new software. I had to do this on one occassion in my lab where I upgraded from 6.5 to 6.7 and my VMs would not run because the CPU was not supported in 6.7. Please remember if you are using ISO method to upgrade ESXi please ensure you select "Upgrade ESXi, preserve VMFS datastore". Selecting "Install ESXi, preserve VMFS datastore" does not mean preserving datastore means retaining ESXi as it will still do a clean install of ESXi. This method does not work for vSphere 7.0 as there are changes to the partitions on the boot device. Below are the steps to roll back to a previous version which is quite straight forward. As always perform an backup of your host configuration before you upgrade or rollback ( KB2042141 ). I have

Configuring ESXi 6 host to send logs to Syslog Server

In my previous post I talked about configuring VMware Syslog server for Windows which is installed and enabled by default on installation of vCenter 6 for Windows. I will now describe the basic configuration that is required on an ESXi 6 host to be able to send logs out to a syslog server using my vCenter as the example. 1) Navigate to your ESXi host within vCenter. Go to "Manage" tab and select "Settings" followed by "Advanced System Settings". Look for the settings "Syslog.global.loghost" and highlight this settings. Click the pencil icon to edit the configuration for this setting. 2) You can now add the host name or ip address of your syslog server/s. You can enter just hostname or IP address, use udp://hostname:514 or ssl://hostname:1514 to be more specific on the port and protocol to be used. If you have multiple hosts then you use the comma (,) to separate each server i.e. udp://192.168.0.1:514,udp://192.168.0.2:514 3)We n

Custom ESXi Image - ISO using PowerCLI

There comes a time when you have purchased a new hardware to run your ESXi software and discover that the installable base media provided by VMware does not include the drivers or the drivers are out of date. In the world of Windows (Plug and Play) it would discover the hardware and prompt you to provide the drivers so that Windows would install/update the drivers for the hardware. For ESXi if the drivers are not present during load time then the hardware will possibly not work. VMware uses VIB (vSphere Installation Bundle) as a way for vendors to distribute their drivers. To install these VIBs you can either use Update Manager or command line (esxcli). Now this is all good but it does mean you have to first install the base ESXi then use one of the steps above to install/update the drivers.   Some people might feel that it is OK to update the drivers using the above methods but what if it was the network card that was the new hardware and you needed new drivers. Without the net