Integrating PowerShell with PowerBI: Automating in Azure (Part 3)

In this blogpost we will take a look at how to automate the process of updating the data in the Power BI report we created in part 2, without any need of a computer/server. We will manage to do this by scheduling a Runbook in Azure Automation.

I asume that you allready have signed up for a Azure subscription (if not thers a 30 day trial you can use for testing).

Four things is needed:
– Create automation account.
– Create credential asset.
– Import needed modules.
– Setup runbook with script and schedule it.

Create Automation Account

  1. Go to portal.azure.com.
  2. Select More services, and search for Automation Accounts, select it.
    azure1.jpg
  3. Press +/Add.
  4. Set Name, Subrcription etc.., then Create.
    azure2.jpg

Create credential asset.

Now we need to create a asset were we can store the username and password needed for the script.

  1. Go to Automation Accounts and select your newly created account.
  2. Select Credentials, then +/Add a credential.
  3. Fill in the form and press create.
    azure4.jpg

Import modules.

Now we need to import the needed moduels into Azure.

  1. Go to Automation Accounts and select your newly created account.
  2. Select Modules gallery and search for Microsoft.ADAL.PowerShell. Select the module.
    azure5.jpg
  3. Press Import.
    azure6.jpg
  4. Now serach for and import Microsoft.PowerBI.PowerShell.
  5. Our script needs the Exchange Online module, but this is not to be found in the Moduels Gallery. We need to copy that from a comptuere where its installed.
    Open a PowerShell window and enter: (Get-Module -ListAvailable *).path
  6. Go to the path of the Exchange module and compress the content to a zip file.
  7. Go to Modules under your Automation Account and select +/Add a module.
    azure7.jpg
  8. Select the path to where you stored the zip file and press OK.
    azure9.jpgazure8

Setup Runbook with script.

  1. Go to Automation Accounts and select your newly created account.
  2. Select Runbooks and press +/Add a runbook.
    azure9
  3. Create a new runbook.
    azure10.jpg
  4. Select your runbook.
  5. Press Edit.
    azure11.jpg
  6. Select your runbook and enter the modified script under.
    azure12
    Script:
#If running as a automated task, remember to store credentials.

$myCredential = Get-AutomationPSCredential -Name 'o365'
$userName = $myCredential.UserName
$securePassword = $myCredential.Password
$password = $myCredential.GetNetworkCredential().Password

$cred = New-Object System.Management.Automation.PSCredential ($userName, $securePassword)
Set-ModuleCredential -Cred $cred -ConnectionUri "https://ps.outlook.com/PowerShell-LiveID?PSVersion=5.1.14393.187" 

#Define $info array
$info = @()

#Get all groups
$Groups = Get-UnifiedGroup | Select-Object Alias,Accesstype,ManagedBy,PrimarySmtpAddress,Displayname,Notes,GroupMemberCount,GroupExternalMemberCount,WhenChanged

foreach($Group in $Groups) {

    Write-Host -Object "Number of Groups left to process $GroupsCount" -ForegroundColor Green
    $Members = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType members
    $Owners = Get-UnifiedGroupLinks -Identity $Group.alias -LinkType owners
    $MembersCount = $Members.count
    $OwnerCount = $Group.ManagedBy

    foreach($Owner in $Owners){
        $Object=[PSCustomObject]@{
            Name = $Group.Displayname
            Group = $Group.Alias
            Email = $Group.PrimarySmtpAddress
            UserName = $Owner.name
            NumberOfMembers = $Group.GroupMemberCount
            MemberOrOwner = 'Owner'
            NumberOfOwners = $OwnerCount.count
            GroupType = $Group.AccessType
            ExternalMemberCount = $Group.GroupExternalMemberCount
            WhenChanged = $Group.WhenChanged | Get-Date -Format 'yyyy.MM.dd hh:mm'
            Description = $Group.Notes
            }#EndPSCustomObject
        $info+=$object
    }

    foreach($Member in $Members){
        $Object=[PSCustomObject]@{
            Name = $Group.Displayname
            Group = $Group.Alias
            Email = $Group.PrimarySmtpAddress
            UserName = $Member.name
            NumberOfMembers = $Group.GroupMemberCount
            MemberOrOwner = 'Member'
            NumberOfOwners = $OwnerCount.count
            GroupType = $Group.AccessType
            ExternalMemberCount = $Group.GroupExternalMemberCount
            WhenChanged = $Group.WhenChanged | Get-Date -Format 'yyyy.MM.dd hh:mm'
            Description = $Group.Notes
            }#EndPSCustomObject
        $info+=$object
    }

    $GroupsCount--

}

#Connects to PowerBI
Connect-PowerBI -AuthorityName m365x992073.onmicrosoft.com -ClientId 'fa7af8a7-56ad-429f-8f57-76b1bd2087e1'  -UserName $username -Password $password

#Removes old data before updating with new data.
Remove-PowerBIRows -DataSetId '025bc397-b8a2-4a3e-bca1-7502b4e50e33' -TableName 'GroupReport'

#Adds new data.
Add-PowerBIRows -DataSetId '025bc397-b8a2-4a3e-bca1-7502b4e50e33' -TableName 'GroupReport' -Rows $info

 

7. Select Save and then Publish.
azure13.jpg
8. Go to overview and select Schedule.
azure14.jpg

9. Select Link a schedule to your runbook, Create a new Schedule, fill out the details then select Create.
azure15.jpg

10. You are now good to go. You can get some statistics about the Job if you go back to Overview.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s