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

&nbsp;

$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"

&nbsp;

$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)

)

&nbsp;

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)

)

&nbsp;

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.

 

 

Post Category

0

Your Cart