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.
   
3. Press +/Add.
4. Set Name, Subrcription etc.., then Create.
   

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.
   

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.
   
3. Press Import.
   
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.
   
8. Select the path to where you stored the zip file and press OK.
   

Setup Runbook with script.

1. Go to Automation Accounts and select your newly created account.
2. Select Runbooks and press +/Add a runbook.
   
3. Create a new runbook.
   
4. Select your runbook.
5. Press Edit.
   
6. Select your runbook and enter the modified script under.
   
   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.

8. Go to overview and select Schedule.

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

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