Power BI – Analyze Azure Costs

by

Most organizations have Enterprise Agreement (EA) accounts for Azure billing. Microsoft offers the Cost Management App which can be used to view and analyze Azure costs using Power BI. The Cost Management App only works with EA accounts.

But you might have situations where you are managing Azure billing using Customer Agreement. Microsoft has updated the Azure Cost Manager connector in Power BI to support Customer Agreement. The Azure Cost Management connector for Power BI Desktop can be used to build powerful, customized visualizations and report to help us understand Azure spending.

Azure Cost Management allows 3 kinds of connections:

  • Customer Agreement: Common for small business or individual accounts
  • Enterprise Agreement: Accounts used by big organizations where payment goes through via purchase orders and such
  • Billing Profile: This is sort of like a subset of Customer Agreement. Allows us to organize via rules, like, cost-center, department, etc

To proceed further, make sure you have the Power BI desktop App downloaded and installed on your machine.

Determine Billing Information in Azure

To connect to a billing account, we need to retrieve the Billing account ID from Azure portal:

  1. In the Azure portal, search for Cost Management + Billing
  2. Select Billing profile
  3. In the left navigation menu, under Settings in the menu, select Properties
    • Make sure this billing account has a at least Billing account reader assigned to it
    • This can be determined by clicking on Billing scopes in the left navigation menu or in the Properties tab
  4. Under Billing profile, copy ID
Billing Account ID

Connect using Azure Cost Management in Power BI

To use the Azure Cost Management connector in Power BI Desktop:

  1. Launch Power BI Desktop
  2. Click Get data from the splash page or from the Home ribbon
  3. Click Azure from the list of data categories
  4. Select Azure Cost Management
Connect Azure Cost Management
  1. Under Choose Scope,
    • To connect to a Billing Account
      • Select Manually Input Scope and input the connection string in below format, with {billingAccountId} that we determined in the earlier section
        • /providers/Microsoft.Billing/billingAccounts/{billingAccountId}
    • To connect to a Billing profile
      • Select Manually Input Scope and input the connection string in below format, the {billingAccountId} and {billingProfileId} can be determined in the same properties tab as in earlier section
        • /providers/Microsoft.Billing/billingAccounts/{billingAccountId}/billingProfiles/{billingProfileId}
    • To connect to an EA account
      • Select Enrollment Number and type the account number
  1. I’m entering 12 months but this is up to you
    • The Advanced Options can be left blank
  2. Click OK
Azure Cost Management
  1. A Navigator window shows all the available data tables
    • Select a table to see a preview dialog
    • One or more tables can be selected by selecting the boxes beside their name and then click Load
      • For the report I have in mind, I only need the Usage details table and I’m selecting it to be loaded
Available Tables
Table Description
Balance summarySummary of the balance for the current billing month for EA
Billing eventsEvent log of new invoices, credit purchases, etc. Microsoft Customer Agreement only
BudgetsBudget details to view actual costs or usage against existing budget targets
ChargesA month-level summary of Azure usage, Marketplace charges, and charges billed separately. Microsoft Customer Agreement only.
Credit lotsAzure credit lot purchase details for the provided billing profile. Microsoft Customer Agreement only.
PricesheetsApplicable meter rates for the provided billing profile or EA enrollment.
RI chargesCharges associated to Reserved Instances over the last 24 months. This table is in the process of being deprecated, please use RI transactions
RI recommendations (shared)Reserved Instance purchase recommendations based on all subscription usage trends for the last 30 days
RI recommendations (single)Reserved Instance purchase recommendations based on single subscription usage trends for the last 30 days
RI transactionsList of transactions for reserved instances on billing account scope
RI usage detailsConsumption details for existing Reserved Instances over the last month
RI usage summaryDaily Azure reservation usage percentage
Usage detailsA breakdown of consumed quantities and estimated charges for the given billing profile on EA enrollment
Usage details amortizedA breakdown of consumed quantities and estimated amortized charges for the given billing profile on EA enrollment
Data available through the connector
  1. When we select Load, the data is loaded into Power BI Desktop
    • Depending on the tables you choose, you may be asked to for authentication
    • When the data we selected is loaded, the data tables and fields are shown in the Fields pane
Loaded fields

I built this visualization below using some of these fields,

Visualization showing Azure cost by ResourceGroup Name, Date, Meter and Meter sub-category

The data is there and you are only limited by the amount on time you have, to spend within Power BI and your imagination.😉

I’m a big fan of Tableau and I love creating visualizations. Now I’ve started using Power BI more and more with Azure related stuff. Plus Power BI Pro comes bundled with Office 365 E5.

Hope this post helped you in setting up your Azure cost reports with Power BI.

Thank you for stopping by. ✌

M365 – Manage Group Creation Permission

by

All users can create M365 groups, this is the option enabled by default. Microsoft probably took this approach so as to make sure users can collaborate without any IT assistance.

This is good but when it comes to start managing Teams and the related resources that get created, it can easily become an IT data governance nightmare. If your organization is in its initial phases of Teams rollout, IMO it is better to disable group creation ability for the masses and preferable do a phased approach.

When we disable M365 group creation, it affects all services that rely on groups for access, including:

  • Outlook
  • SharePoint
  • Microsoft Teams
  • Microsoft Stream
  • Yammer
  • Planner
  • Power BI (classic)
  • Project for the web

To have a solution that is sort of a best of both worlds scenario, we can designate an Azure AD group with specific users who have the permissions to create M365 groups.

Create an Azure AD Group

To create a new Azure AD group, the New-AzureADGroup cmdlet can be used or can also be created from the Azure AD admin portal. I’m naming the group ‘M365 – Group Creators’

New-AzureADGroup -DisplayName "M365 - Group Creators" -Description "Group that allows users to create M365 groups" -MailEnabled $false -SecurityEnabled $true -MailNickName "NotSet"
New Azure AD group

Keep in mind this doesn’t prevent users with Azure AD admin roles which has group creation capabilities from creating new groups.

Set Group Creators

The following needs to be run from PowerShell. Make sure AzureADPreview is installed and connected.

Install-module AzureADPreview
Import-Module AzureADPreview
Connect-AzureAD

Run the following commands,

$Template = Get-AzureADDirectorySettingTemplate | where {$_.DisplayName -eq 'Group.Unified'}
$Setting = $Template.CreateDirectorySetting()
New-AzureADDirectorySetting -DirectorySetting $Setting
‘..already exists’ error

If you get an ‘..already exists’ error, that means your tenant already this setting defined. Proceed with the next steps below,

$Setting = Get-AzureADDirectorySetting -Id (Get-AzureADDirectorySetting | where -Property DisplayName -Value "Group.Unified" -EQ).id
$Setting["EnableGroupCreation"] = $False
$Setting["GroupCreationAllowedGroupId"] = (Get-AzureADGroup -SearchString "<Name of your security group>").objectid
EnableGroupCreation and GroupCreationAllowedGroupId

Use the Set-AzureADDirectorySetting below to set the value in the $Setting variable which has the object ID of the Azure AD group.

Set-AzureADDirectorySetting -Id (Get-AzureADDirectorySetting | where -Property DisplayName -Value "Group.Unified" -EQ).id -DirectorySetting $Setting

To determine if the group is allowed to create to groups,

(Get-AzureADDirectorySetting).Values
verify settings

Only one group can be used to control the ability to create Microsoft 365 Groups. But, other groups can be nested as members of this group.

In case your organization wants to revert back this setting in the future, you can do so by changing $AllowGroupCreation to “True” and the group value to “”

$Setting = Get-AzureADDirectorySetting -Id (Get-AzureADDirectorySetting | where -Property DisplayName -Value "Group.Unified" -EQ).id
$Setting["EnableGroupCreation"] = $true
$Setting["GroupCreationAllowedGroupId"] = ""
Set-AzureADDirectorySetting -Id (Get-AzureADDirectorySetting | where -Property DisplayName -Value "Group.Unified" -EQ).id -DirectorySetting $Setting
(Get-AzureADDirectorySetting).Values
Enable group creation

Usually the settings takes 30ish minutes to take effect. You can verify this by trying to create a group with a user who is a non-member of the allowed Azure AD group.

If a user who is part of the group creators can’t create a M365 group, it’s worth checking the OWA policy. The Get-OwaMailboxPolicy can be used to check this,

Get-OwaMailboxPolicy | Select GroupCreationEnabled

If the above output shows ‘False’, you can enable this by using the Set-OwaMailboxPolicy cmdlet,

Set-OwaMailboxPolicy -Identity "Name of your OWA Policy" -GroupCreationEnabled $true

Hope this helped you in setting up the policies to disable M365 group creation.

Thank you for stopping by.✌

O365 – Create Distribution Groups using PowerShell

by

In this post, I’ll go through the steps to create distribution groups in O365 using PowerShell.

Before proceeding further make sure you are connected to Exchange Online,

$o365cred = Get-Credential
Connect-ExchangeOnline -credential $o365cred

To create a mail-enabled security group named Managers without specifying any members:

$Name = Read-Host "Enter a name for the DistributionGroup"
New-DistributionGroup -Type "Security" -Name $Name -DisplayName $Name -Alias $Name

To create a mail-enabled security group named Managers with members:

Note: -Member is a ‘MultiValuedProperty’ and as we input users comma seperated, we need to split the (comma-separated) string to get an actual array.

$Name = Read-Host "Enter a name for the DistributionGroup"
$Members = Read-Host "Enter email addresses seperated by comma"
$members = $members -split ' *, *'
New-DistributionGroup -Type "Security" -Name $Name -DisplayName $Name -Alias $Name -Members $Members

To add multiple members to an existing Distribution Group:

$Name = Read-Host "Enter DistributionGroup name to add members"
$Members = "user01@domain.onmicrosoft.com","user01@domain.onmicrosoft.com"
$Members | ForEach-Object { Add-DistributionGroupMember -Identity $Name -Member $_}

To import members from a csv and add to an existing Distribution Group:

$Name = Read-Host "Enter DistributionGroup name to add members"
Import-csv "C:\tmp\members.csv" | ForEach-Object {
Add-DistributionGroupMember -Identity $Name -Member $_.member
}

To determine existing distribution group members for a distribution group:

To set distribution group to accept messages from authenticated (internal) and unauthenticated (external) senders.

Note: If you don’t specify this parameter while creating the distribution group, the default value is set to ‘true’ meaning messages from unauthenticated (external) senders are rejected.

$Name = Read-Host "Enter DistributionGroup's name to allow external senders"
Set-DistributionGroup -Identity $Name -RequireSenderAuthenticationEnabled $false

To change an existing distribution group’s name:

$Name = Read-Host "Enter name of existing group to be renamed" 
$NewName = Read-Host "Enter new name" 
Set-DistributionGroup -Identity $Name -Name $NewName -DisplayName $NewName -Alias $NewName

Thank you for stopping by. ✌

O365 – Prevent Users from Signing Up for Trials and Purchasing Their Own Pro license

by

I’ve updated this post with the newer MSCommerce PowerShell module

Self-service is a great idea in several instances but in my opinion when it comes to users signing up for trials and purchasing licenses, it seems to be causing unexpected issues especially while answering to the purchasing team.

Most organizations these days have procurement processes in place to meet regulatory, security, compliance and governance needs. And the need to ensure that all licenses are approved and managed according to defined processes is very important. And when you take expenses, privacy or security into consideration, it is a good idea to disable self-service sign-up and purchases.

To disable all self-service sign-ups

This can be achieved using the MSOL PowerShell module. Type below command to check current settings:

Get-MsolCompanyInformation | fl AllowAdHocSubscriptions
current settings

To disable all self-service sign-ups,

Set-MsolCompanySettings -AllowAdHocSubscriptions:$false

When users try to sign-up, they’ll see the below message,

Your IT department has turned off signup

Prevent users from purchasing their own Pro license

To install and connect the MSCommerce module, start PowerShell as an administrator to install the module,

Install-Module -Name MSCommerce
Import-Module -Name MSCommerce
Connect-MSCommerce
Get-Command *-mscommerce*

To determine current setting,

Get-MSCommercePolicy -PolicyId AllowSelfServicePurchase
Get-MSCommercePolicy

View a list of all available self-service purchase products and the status,

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase
Get-MSCommerceProductPolicies

Disable the policy setting for a specific product, Below example: ‘Power BI Pro’

Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0L3PB -Enabled $False

Below is an example script on how your can disable AllowSelfServicePurchase by getting the ProductID for ‘Power BI Pro’

$p = Read-Host "Enter product name"
$product = Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase | where {$_.ProductName -match $p }
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId $product.ProductID -Enabled $false
Update-MSCommerceProductPolicy

In scenarios where there are more than one values for the product, Below example: ‘Power Automate’

$p = Read-Host "Enter product name"
$product = Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase | where {$_.ProductName -match $p }
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId $product[0].ProductID -Enabled $false
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId $product[1].ProductID -Enabled $false
Update-MSCommerceProductPolicy # more than one value

To disable AllowSelfServicePurchase for all products,

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase | ? {$_.PolicyValue -eq "Enabled" } | ForEach {Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId $_.ProductId -Enabled $False }
To disable for all products

Thanks for stopping by.✌

O365 – Determine Mailbox Folder Size from Exchange Online using PowerShell

by

I’m sure all Exchange Online environments have users who are data hoarders in their email environment. Most tenants have policies to limit how big the mailbox can be for various reasons. Users may come back with increasing mailbox sizes so they can hoard more of those outdated data. It is good practice to maintain the folder items. Get-MailboxFolderStatistics helps retrieve information about folders in mailboxes, including number and size of items in the folder and other information.

Before proceeding, make sure you are connected to Exchange Online module,

$credential = Get-Credential
Connect-ExchangeOnline -credential $credential

To determine a folders from a user’s mailbox, the folder size and the number of items in the folders,

$EmailId = Read-Host "Enter user's email address"
Get-MailboxFolderStatistics -Identity $EmailId | Select Name,FolderSize,ItemsinFolder

To determine individual folders and subfolder sizes of a specific user:

$EmailId = Read-Host "Enter user's email address"
Get-MailboxFolderStatistics -Identity $EmailId | Select Name,FolderAndSubfolderSize,ItemsInFolderAndSubfolders

To determine inbox folders statistics of a specific user:

$EmailId = Read-Host "Enter user's email address"
Get-MailboxFolderStatistics -Identity $EmailId -FolderScope Inbox | Format-Table Identity,ItemsInFolderAndSubfolders,FolderAndSubfolderSize -AutoSize

To determine and display inbox folder sizes for all mailboxes in the organization 

$All = Get-Mailbox -ResultSize Unlimited
$All | foreach {Get-MailboxFolderStatistics -Identity $_.Identity -FolderScope Inbox} | Select Identity,ItemsInFolderAndSubfolders,FolderAndSubfolderSize | ft -AutoSize

and export to CSV file:

$Allmbx = Get-Mailbox -ResultSize Unlimited
$Allmbx | foreach {Get-MailboxFolderStatistics -Identity $_.Identity -FolderScope Inbox | Select Identity,ItemsInFolderAndSubfolders,FolderAndSubfolderSize | Export-Csv "C:\tmp\Inbox_data.csv" -NoTypeInformation -Append}

Get-MailboxFolderStatistics returns IPM subtree folders. This folder structure consists of messages between recipients(Inbox, Sent Items). In the Exchange Online, the Non-IMP subtree is quite larger, as different O365 applications have been using mailboxes to store and process data. Teams, Delve, MyAnalytics, all have their own folders or folder trees inside the Non-IPM root.

To determine Non-IPM subtree folders and their sizes:

$EmailId = Read-Host "Enter user's email address"
Get-MailboxFolderStatistics -Identity $EmailId -FolderScope NonIpmRoot | Format-Table Identity,ItemsInFolderAndSubfolders,FolderAndSubfolderSize -AutoSize

Thank you for stopping by. ✌