Working with Azure tags in Power BI

Using tags in Azure is a great way to logically organise resources by categories.  Combining tags with the Power BI Microsoft Azure Consumption Insights connector should offer a powerful way to analyse and gain insights into your Azure expenditure.

AzurePowerBI1.png

Unfortunately, the connector imports the tags as a single field containing the tagging JSON, which makes it difficult to work with the tags in a meaningful way.  For example, if you have tags called System, Geography and Environment it would be great to be able to work with these in Power BI as an individual fields.  Fortunately, this is possible, but not with Power BI in the browser.

Download Power BI Desktop  and get data from the Microsoft Azure Consumption Insights (Beta).

AzurePowerBI2

Enter your EA enrollment number, which you can get from https://ea.azure.com/ .  You’ll also need an API access key, which is available from https://ea.azure.com/ under reports -> download usage -> API access key.

Select UsageDetails, then right-click and select edit.

AzurePowerBI3

If you’ve only recently added tags order the data by date descending.  This is because the Power BI editor loads 1000 rows initially and if you’ve only recently started using tags they’ll only be listed against recent records.

Find the Tags column, right-click choose Transform, followed by JSON.

AzurePowerBI4

Power BI will now parse the JSON and enable you to split out each tag into a separate column.  Click on the arrows and the tag fields will be loaded.

AzurePowerBI5

AzurePowerBI6

Click ok to add the new tag fields to the data set, followed by close & apply.

AzurePowerBI7

You can now work with each tag individually.

AzurePowerBI8

Advertisements

Mirrored SQL Databases Stuck In Recovery Mode

Following a server restart, two of three SQL Server databases in a SQL Server Database Mirroring configuration were stuck in recovery mode on the primary server.

Running sys.dm_tran_locks showed a session with a resource_database_id for each of the databases stuck in recovery.

It appeared that SQL Server Database Mirroring was preventing the recovery process completing on the two databases following the server restart.

The resolution was to stop and start the database mirroring endpoints on the primary and secondary SQL Servers.  To return a list of endpoints run select * from sys.endpoints.

To stop and restart the endpoints run

ALTER ENDPOINT %ENDPOINT NAME% STATE=STOPPED

ALTER ENDPOINT %ENDPOINT NAME% STATE=STARTED

Windows 10 April 2018 Update (1803) fails to install with error 0x87D0070C

When deploying the Windows 10 April 2018 Update (1803) via SCCM the update fails to install with error 0x87D0070C.

1803-0

In Event Viewer you see the error “Installation Failure: Windows failed to install the following update with error 0x800704C7: Feature update to Windows 10 (business editions), version 1803, en-gb.”

The error is caused by the default maximum run time setting in SCCM of 60 mins, which in this case is insufficient for the update to install and it times out.  To increase the timeout value, find the update within the Software Updates section of SCCM, right-click on the update and select properties.

1803-1

Increase the value to something like 240 mins and click OK.

1803-2

Windows 10 April 2018 Update (1803) fails to install with error 0x80070241

When installing the Windows 10 April 2018 Update (1803) the installation fails with error 0x80070241.  When installing via SCCM you see the error “The software change returned error code 0x80070241(-2147024319).”

1802-0

In Event Viewer you see the error “Installation Failure: Windows failed to install the following update with error 0x80070241: Feature update to Windows 10 (business editions), version 1803, en-gb.”

To resolve the issue uninstall the Windows Assessment and Deployment Kit – Windows 10 (ADK).

Azure VM Agent Status – Not Ready

In a secure sandbox environment withing Azure I encountered and issue whereby the Azure VM Agent status was reporting as not ready and the agent version as unknown.

Agent0

Opening the VM Agent log in C:\WindowsAzure\Logs\WaAppAgent.log I saw the error:

[ERROR] GetVersions() failed with exception: System.ServiceModel.EndpointNotFoundException: There was no endpoint listening at http://168.63.129.16/?comp=versions that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details. —> System.Net.WebException: Unable to connect to the remote server —> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 168.63.129.16:80
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Exception& exception)
— End of inner exception stack trace —
at System.Net.HttpWebRequest.GetResponse()
at System.ServiceModel.Channels.HttpChannelFactory`1.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)
— End of inner exception stack trace —

Research showed that the IP address 168.63.129.16 is a virtual IP address used for Azure VM Agent communication amongst other things.  See here for more detail.

In this case traffic was flowing through an Azure security appliance and a rule needed to be created to allow traffic to and from 168.63.129.16.  Once this was done the VM Agent functioned correctly.

The volume shadow copy provider is not registered in the system

On a Windows Server 2012 R2 VM protected by Veeam Backup & Replication, Veeam’s application aware processing was failing with the error below.

Retrying snapshot creation attempt (Could not create backup checkpoint for virtual machine ‘%VMNAME%’: The volume shadow copy provider is not registered in the system. (0x80042304). (Virtual machine ID 464FEFAA-0EF5-4E9F-98BF-D76EBE72717B))
Failed to create snapshot (Dell EqualLogic VSS HW Provider) (mode: Veeam application-aware processing with failover) Details: Writer ‘Microsoft Hyper-V VSS Writer’ is failed at ‘VSS_WS_FAILED_AT_PREPARE_SNAPSHOT’. The writer experienced a non-transient error. If the backup process is retried, the error is likely to reoccur. –tr:Failed to verify writers state. –tr:Failed to perform pre-backup tasks.
Retrying snapshot creation attempt (Writer ‘Microsoft Hyper-V VSS Writer’ is failed at ‘VSS_WS_FAILED_AT_PREPARE_SNAPSHOT’. The writer experienced a non-transient error. If the backup process is retried, the error is likely to reoccur. –tr:Failed to verify writers state. –tr:Failed to perform pre-backup tasks.)
Guest processing skipped (check guest OS VSS state and hypervisor integration components version)

The VM is also protected by Azure Site Recovery/Hyper-V Replica and the virtual host running the VM logged a “The volume shadow copy provider is not registered in the system” error each time an application consistent recovery point was attempted.

I checked the registry key HKLM\SYSTEM\CurrentControlSet\Services\VSS\Providers and noticed one of the providers ({74600e39-7dc5-4567-a03b-f091d6c7b092}) was missing a CLSID key.

Reg1

I found another VM running Windows Server 2012 R2 and navigated to the same registry key.  In this case the CLSID key was populated.  I copied the CLSID key from the working system and applied to the VM with the issue.

Reg2

Next I restarted the Hyper-V Volume Shadow Copy Requestor on the VM and found that the VSS error was resolved.

Server 2012 R2 – VSSAdmin Waiting for response & VSSAdmin List Writers is empty

On a Windows Server 2012 R2 Hyper-V cluster I had an issue whereby backups were failing with VSS errors.

When running VSSAdmin List Writers I received the response: “Waiting for responses. These may be delayed if a shadow copy is being prepared.”

VSS0

In order to resolve this I restarted the COM+ Event System service.  Following this VSSAdmin List Writers failed to return anything.

VSS1

When restarting the COM+ Event System service there are a number of dependant services.  I noticed that the COM+ System Application service hadn’t automatically restarted.  I started the service, then restarted the services below.  Following this VSSAdmin List Writers returned the expected list of writers.

  • EqualLogic VSS Requestor – Specific to my use of EqualLogic storage
  • EqualLogic VSS Service – Specific to my use of EqualLogic storage
  • Hyper-V Virtual Machine Management
  • Microsoft Software Shadow Copy Provider
  • Volume Shadow Copy