Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
In this blog post 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 needing a computer or server. We will do this by scheduling a Runbook in Azure Automation.
I assume that you have already have signed up for an Azure subscription. If not, there is a 30-day trial you can use for testing.
Four things you need to do:
- Create automation account.
- Create credential asset.
- Import needed modules.
- Setup runbook with script and schedule it.
Create Automation Account
Create credential asset
Next, we need to create an asset where we can store the username and password needed for the script.
Import modules
Now, we need to import the needed modules into Azure.
Setup Runbook with 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. Click 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 and then select Create.
10. You are now good to go. You can get some statistics about the job if you go back to Overview.
Missing a step? View part 1 and part 2 of this post series.
This article was first published on my blog alexholmeset.blog.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.