The Acterys product of our sister company utilizes large database pools with hundreds of customer databases. It is crucial for them to always be in control of current resource consumption levels and pass on excessive usage charges to customers. Having the option to make this clearly visible in Power BI is not just important from an insights perspective but has significant revenue implications.
In this blog I would like to cover how we helped them to automate the collection of Azure consumption data, export it to an Azure SQL database and use Power BI for analysis.
The approach covered here uses a PowerShell script to collect and load the data:
Install New Azure PowerShell Az Module
As of December 2018, the Azure PowerShell Az module is in general release and now the best PowerShell module for interacting with Azure. Az uses the .NET standard library, which means it will run on PowerShell 5 and PowerShell 6.
Requirement: Check your PowerShell version
The Azure Az Module works with PowerShell 5.1 or higher on Windows, Or PowerShell 6 on any platform, to check your PowerShell version, you can run the following command:
$PSVersionTable
Install Az PowerShell Module
To install modules, open PowerShell (“Run as Administrator” on Windows, or with superuser privileges on MacOS or Linux), and run the following command:
Install-Module –Name Az
Important Note:
You can have both the AzureRM and Az modules installed at the same time. If you have both modules installed, don’t enable aliases. Enabling aliases will cause conflicts between AzureRM cmdlets and Az command aliases and could cause unexpected behaviour. It’s recommended that before installing the Az module, you uninstall AzureRM. You can always uninstall AzureRM or enable aliases at any time. To learn about the AzureRM command aliases, see Migrate from AzureRM to Az. For uninstall instructions, see Uninstall the AzureRM module.
Reference: https://docs.microsoft.com/en-us/powershell/azure/install-az-ps?view=azps-1.6.0
Sign In to Azure Account
After installing the Azure Az module, you will need to activate it with running the following command:
Import-Module Az
To start working with Azure PowerShell, you need to sign in with your Azure credentials, so that you can see all resources that you are currently using. You can sign in to the Azure account by using this command:
Connect-AzAccount
This will prompt you to enter the Azure account username and password, if you want to automate this process, you can use the script below:
$username =<your Azure account username>' $tenantId = "<your Azure tenant ID>" $passwd = '<your password>' | ConvertTo-SecureString -AsPlainText -Force # login to the azure tenant with your username and password specified and tenantId $pscredential = New-Object System.Management.Automation.PSCredential($username, $passwd) $cred = Connect-AzAccount -Credential $pscredential -TenantId $tenantId
Get Azure Resources
Azure typically contains a variety resources in your current subscription, you can run the following command to get all resources:
Get-AzResource | Format-Table
To retrieve Azure consumption data for Azure SQL databases, run the following command:
Get-AzResource | Where-Object {$_.ResourceType -like “*database*”}
Getting Azure consumption metric definition
For different resource types, the Azure monitor API defines different metric measures. For instance, we can get the metric definitions for Azure SQL database by running the following command:
$ResourceId = (Get-AzResource | Where-Object {$_.ResourceType -like "*database*"}).ResourceID $metrics = Get-AzMetricDefinition -ResourceId $ResourceId[0] $metrics| Select-Object @{Name="Name";Expression={$_.Name.Value}},@{Name="Tag";Expression={$_.Name.LocalizedValue}},@{Name="PrimaryAggregationType";Expression={$_.PrimaryAggregationType}},@{Name="Unit";Expression={$_.Unit}} | Format-Table
For each of the above measures, we can get the monitoring data within a specified time frame and specified measures.
Getting Azure Metric monitoring data
Azure Metric are numerical values that return data for a specific resource type at a particular point in time. We can collect this data at regular intervals and specified points in time.
For instance, we can get the CPU percentage usage on a particular database resource by running the following script, the script will collect the data for the last 2 days within a 60 minutes interval:
$timeInterval = 60; #minutes $startTime =(get-date (get-date).addDays(-2) -UFormat "%m/%d/%Y 12:00:00 AM") $endTime = (get-date (get-date) -UFormat "%m/%d/%Y 12:00:00 PM") $ResourceId = (Get-AzResource | Where-Object {$_.ResourceType -like "*database*"}).ResourceID $metrics = Get-AzMetricDefinition -ResourceId $ResourceId[0] $LocalizedValue = $metrics[0].Name.LocalizedValue $metricName = $metrics[0].Name.Value $unit = $metrics[0].Unit $PrimaryAggregationType = $metrics[0].PrimaryAggregationType $metricsData = Get-AzMetric -ResourceId $ResourceId[0] -TimeGrain ([TimeSpan]::FromMinutes($timeInterval)) -StartTime $startTime -EndTime $endTime -MetricNames "cpu_percent" $metricsData.Data | Select-Object @{Name="metricName";Expression={$metricName}},@{Name="Tag";Expression={$LocalizedValue}},@{Name="Unit";Expression={$unit}},@{Name="Time";Expression={$_."Timestamp"}},@{Name="Type";Expression={$_.PrimaryAggregationType}},@{Name="Value";Expression={$_.($PrimaryAggregationType)}} | Format-Table
As you can see, we now get the data for the CPU percentage usage for the particular Azure SQL database. We can also collect data for all other metric measures that we have already collected above.
Export the data to database or files
With all the monitoring data, we can export the data into a file or SQL server database. We use an automated process that loads data into a staging table in Azure SQL and runs the PowerShell script using an automated scheduling process on a daily basis that inserts the records into our metrics table.
Here are the SQL scripts that create the staging table and metrics table as well as the stored procedure for the incremental insertion.
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'AzureAllMetric_STG' AND xtype = 'U') Create table AzureAllMetric_STG( [ResourceId] [nvarchar](255) NULL, [ResourceType] [nvarchar](255) NULL, [MetricName] [nvarchar](255) NULL, [Tag] [nvarchar](255) NULL, [Unit] [nvarchar](255) NULL, [Type] [nvarchar](255) NULL, [RetrievedDate] datetime not null, [Value] numeric(38,10) ) IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'Azure_Metric_DB' AND xtype = 'U') Create table Azure_Metric_DB( [ResourceId] [nvarchar](255) NULL, [MetricName] [nvarchar](255) NULL, [MetricId] int NOT NULL, [RetrievedDate] datetime not null, [Value] numeric(38,10) ) Create Procedure SP_PopulateMetricRecords AS begin Merge Azure_Metric_DB as Target USING (select am.ResourceId,amd.Id as MetricId, amd.Item as MetricName, am.[RetrievedDate],am.[Value] from AzureAllMetric_STG am JOIN Azure_MetricDefinition amd ON am.MetricName = amd.Item and am.ResourceType = amd.ResourceType WHERE am.ResourceType LIKE '%Database%' ) As Source ON (source.ResourceId = target.ResourceId and Target.metricId = Source.metricId AND Target.[RetrievedDate] = Source.[RetrievedDate]) when not matched then Insert(ResourceId, [MetricName],metricId, [RetrievedDate], [Value]) VALUES (source.ResourceId, source.MetricName, source.metricId, source.[RetrievedDate],Source.[Value]); end
In PowerShell, we can use the following command to insert records into the database. Adapt the script with your SQL query, specifying SQL server name, database name, SQL authentication username and password.
$sql_query = “<your SQL query>” $servername = “<SQL server name or IP address>” $databaseName = ”<Destination database name>” $sqlauthUsername =”database SQL authentication username” $sqlauthPassword =”database SQL authentication password” Invoke-Sqlcmd -Query $sql_query -ServerInstance $serverName -Database $databaseName -Username $sqlauthUsername -Password $sqlauthPassword -OutputSqlErrors $True
Build Power BI Report Template
Based on the data, here is an example Power BI template built for Azure SQL resource consumption:
With the same techniques, we can also collect the monitoring data for virtual machines and database elastic pool consumption.
Get Azure consumption Usage Detail
Using the Azure usage detail API, we can gather usage and cost (without tax) information for the current subscription, by running the following command for getting the last 14 days of usage detail data for all resources available in the account:
Get-AzConsumptionUsageDetail -Expand MeterDetails
You can also specify start and end date for retrieving data:
Get-AzConsumptionUsageDetail -Expand MeterDetails -StartDate $UsageStart -EndDate $UsageEnd
This data is also exported to the Azure SQL server database and the basis for another PowerBI report, so we can monitor the usage details and cost on a daily basis. Here is our dashboard with some sample data:
Don’t hesitate to contact us for further questions or support with building Azure based Power BI solutions for your organisation.