Daniel's Tech Blog

Cloud Computing, Cloud Native & Kubernetes

Azure like chargeback with System Center & Windows Azure Pack – Part 2

In my previous blog post I have shown you how to do an Azure like chargeback with the different KPI’s out of Service Reporting and the cost calculation.

-> https://www.danielstechblog.io/azure-like-chargeback-with-system-center-windows-azure-pack-part-1/

In this blog post I will present you the PowerShell runbooks that will automate the manual part whom I talked about in part one.

It is a solution which automatically generates a chargeback report for the tenant with its monthly costs and will send them an email.

Let us reflect the requirements for our usage metering & reporting and our chargeback solution.

  • Windows Server 2012 R2 Hyper-V as hypervisor
  • System Center 2012 R2 Virtual Machine Manager
  • System Center 2012 R2 Operations Manager
  • System Center 2012 R2 Service Provider Foundation
  • System Center 2012 R2 Service Management Automation
  • System Center 2012 R2 Service Reporting
  • Windows Azure Pack
  • PowerShell

Another requirement is that the SMA runbook server service account needs the permissions to query the SQL Server Analysis Services data warehouse.

ChargebackReport09

The focus in this part lies on the products System Center 2012 R2 Service Management Automation, Windows Azure Pack and PowerShell. For the solution I have created two PowerShell runbooks and one PowerShell script for the installation.

  • Install-ChargebackReport.ps1
  • Invoke-ChargebackReport.ps1
  • Send-ChargebackReport.ps1

Install-ChargebackReport.ps1:

The Install-ChargebackReport.ps1 script will create the required variables and imports the two PowerShell runbooks Invoke-ChargebackReport.ps1 & Send-ChargebackReport.ps1 into the Serivce Management Automation. For that you need the SMA powershell module on your workstation or copy all the stuff onto your SMA management server for the import process. When you run the script you will be ask for the SMA web service endpoint and the port. Now let us have a look at the variables.

$DataSource=”SRV-5.neumanndaniel.localSR”
$Database=”UsageAnalysisDB”
$VMMServer=”SRV-1.neumanndaniel.local”
[String]$ComputeCost=0.050275
[String]$ComputeCostFactor=1.333167578319244
[String]$StorageCost=0.0373
[String]$TransactionCost=0.0027

  • $DataSource defines the SQL Server Analysis Services server on which the data warehouse resides.
  • $Database is used for the default data warehouse name UsageAnalysisDB.
  • $VMMServer should be clear enough that you have to enter here the FQDN of your VMM management  server.
  • $ComputeCost, $ComputeCostFactor, $StorageCost and $TransactionCost contains your cost definition. Have a look into part one how to get/define your cost definition. Very important is the conversion to the type String otherwise we are not able to preserve the “.”. Why we do that? If we do not do that the SMA variable will be from the type special and we are not able to change the cost definition afterwards through the Windows Azure Pack Admin Portal. Another reason is the behavior of PowerShell on a multiplication process. If you multiply a number with 0,03 which is the common notation in Europe instead of the American one which is 0.03 you will get the following error “Method invocation failed because [System.Object[]] does not contain a method named ‘op_Multiply’.”. That is the reason why we have to preserve the “.”.

The next step is to create the SMA variables “Service Reporting Analysis Services”, “Service Reporting Analysis Services Database”, “Compute Cost”, “Compute Cost Factor”, “Storage Cost”, “Storage Transaction Cost”, “Chargeback Email From” and “Chargeback Email Server”.

Set-SmaVariable -Name ‘Service Reporting Analysis Services’ -Value $DataSource -WebServiceEndpoint $WebServiceEndPoint -Port $Port
Set-SmaVariable -Name ‘Service Reporting Analysis Services Database’ -Value $Database -WebServiceEndpoint $WebServiceEndPoint -Port $Port

Set-SmaVariable -Name ‘Compute Cost’ -Value $ComputeCost -WebServiceEndpoint $WebServiceEndPoint -Port $Port
Set-SmaVariable -Name ‘Compute Cost Factor’ -Value $ComputeCostFactor -WebServiceEndpoint $WebServiceEndPoint -Port $Port
Set-SmaVariable -Name ‘Storage Cost’ -Value $StorageCost -WebServiceEndpoint $WebServiceEndPoint -Port $Port
Set-SmaVariable -Name ‘Storage Transaction Cost’ -Value $TransactionCost -WebServiceEndpoint $WebServiceEndPoint -Port $Port

Set-SmaVariable -Name ‘Chargeback Email From’ -Value ‘wapack@neumanndaniel.de’ -WebServiceEndpoint $WebServiceEndPoint -Port $Port
Set-SmaVariable -Name ‘Chargeback Email Server’ -Value ‘srv-5.neumanndaniel.local’ -WebServiceEndpoint $WebServiceEndPoint -Port $Port

After that we will import our PowerShell runbooks into SMA. So make sure all three scripts are in the same folder on your workstation or the SMA management server.

Import-SmaRunbook -Path .Invoke-ChargebackReport.ps1 -Tags ‘ServiceReporting’ -WebServiceEndpoint $WebServiceEndPoint -Port $Port
Import-SmaRunbook -Path .Send-ChargebackReport.ps1 -Tags ‘ServiceReporting’ -WebServiceEndpoint $WebServiceEndPoint -Port $Port

Invoke-ChargebackReport.ps1:

The Invoke-ChargebackReport.ps1 runbook does all the processing for you. Let us focus on the important parts of the runbook.

line 18: $Connection=Get-AutomationConnection -Name ‘SCVMM Connect’

As you can see you have to create a SMA connection for you VMM management server with the FQDN and the service account credentials. The runbook needs that because it gathers all the tenant user roles from VMM and will query on that basis the Service Reporting data warehouse on a per tenant basis to get the chargeback details.

line 26: [Double]$ComputeCost=Get-AutomationVariable -Name ‘Compute Cost’
line 27: [Double]$ComputeCostFactor=Get-AutomationVariable -Name ‘Compute Cost Factor’
line 28: [Double]$StorageCost=Get-AutomationVariable -Name ‘Storage Cost’
line 29: [Double]$TransactionCost=Get-AutomationVariable -Name ‘Storage Transaction Cost’

The variables with the cost definitions will be converted to the type Double. That is required to do the multiplication correctly in the calculation process later in this runbook.

line 46: $query=”SELECT NON EMPTY { [Measures].[MemoryUsage-Monthly], [Measures].[DiskIOPS-Monthly], [Measures].[CoreAllocated-Monthly], [Measures].[DiskSpaceUsage-Monthly], [Measures].[TotalVMRunTime-Monthly] } ON COLUMNS, NON EMPTY { ([DateDim].[CalendarMonth].[CalendarMonth].ALLMEMBERS * [VirtualMachineDim].[DisplayName].[DisplayName].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [DateDim].[CalendarMonth].&[$Year]&[$Month] } ) ON COLUMNS FROM ( SELECT ( { [UserRoleDim].[DisplayName].&[$Username] } ) ON COLUMNS FROM [SRUsageCube])) WHERE ( [UserRoleDim].[DisplayName].&[$Username] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS”

In line 46 we have our SQL query for the data warehouse cube. The easiest way to create the query and do some modification regarding the date and username afterwards in PowerShell is to use the SQL Server Management Studio on the Service Reporting server.

Chargeback02

There you have to click on the red marked button to get into the design view. Now comes the most important part in the runbook on how to connect to the SQL Server Analysis Services database.

line 63: [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices.AdomdClient”) | Out-Null
line 64: $connectionString=”Data Source=$USING:DataSource;Catalog=$USING:Database”
line 65: [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]$connection = $connectionString
line 66: $connection.Open()
line 67: $Command = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand($query, $connection)
line 68: $dataAdapter = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdDataAdapter($command)
line 69: $ds = New-Object System.Data.DataSet
line 70: $dataAdapter.Fill($ds) | Out-Null
line 71: $connection.Close();

One specialty here is line 65. We do not want to create a new connection object with the New-Object cmdlet here because PowerShell will throw an error that the Assembly Microsoft.AnalysisServices.AdomdClient.AdomdConnection has not been loaded yet. The only way to fix this is to do [Microsoft.AnalysisServices.AdomdClient.AdomdConnection]$connection instead.

In line 147 you can see that the runbook exports the finalized chargeback report into the folder C:Chargeback on the runbook server. The chargeback reports have the following naming scheme $Username.$Month.$Year.csv that will look like daniel.neumann@neumanndanie_f12fd177-f588-4568-8a43-927d69d52bf2.1.CY2015.csv.

At the end of the runbook the next runbook Send-ChargebackReport is called for the email dispatch.

Send-ChargebackReport.ps1:

Let us focus on the only important part of the runbook.

line 19: $ChargebackEmailTo=$Username.Split(“@”)[0]+”@neumanndaniel.de”

Here you have to change the domain ending regarding your Active Directory Domain ends with .local or a real TLD. In a Cloud Service Provider scenario you have to edit the Send-ChargebackReport.ps1 runbook to fit your needs.

Installation and configuration:

As I have mentioned it in the upper part of the blog post you have to use a workstation or the SMA management server for the import process because we need the SMA powershell cmdlets for it. Copy all three PowerShell scripts on the appropriate computer and double check if all three scripts are in the same folder. Then execute the Install-ChargebackReport.ps1 script for the installation. Prior to this you have to adjust the values of the variables or you do that afterwards through the WAP Admin Portal.

ChargebackReport01ChargebackReport02

Log in to the WAP Admin Portal and check if everything is in place. Next step is to publish the Send-ChargebackReport runbook prior to the Invoke-ChargebackReport runbook. If you want to trigger the runbook automatically then add a schedule to the Invoke-ChargebackReport runbook.

ChargebackReport03ChargebackReport04ChargebackReport05

After a run you can have a look at the job history which tenant has been processed for chargeback.

ChargebackReport06

As a tenant you will get the following email with the chargeback report attached.

ChargebackReport07

The chargeback report looks like the following one depending on your usage.

ChargebackReport08

In summary I hope you got some ideas and pattern on how you can provide an Azure like chargeback with System Center & Windows Azure Pack to your tenants.

A very important note at the end! Make sure that every component in the usage metering and reporting chain runs 24/7 otherwise the monthly chargeback report will get inaccurate because Service Reporting will interpolate the KPI’s based on the records that have been stored in the data warehouse to fit the monthly scheme. If you need a very accurate chargeback report then you have to go with the hourly KPI’s and not the monthly KPI’s. That means you have to invest a lot of work into the Invoke-ChargebackReport runbook. Just keep that in mind.

Download: https://gallery.technet.microsoft.com/Azure-like-chargeback-with-b3b26227

WordPress Cookie Notice by Real Cookie Banner