In my last Blog posts, I described how you can get data from the Power BI REST API and how you can execute them via Azure Automation. When you use the last one to execute your scripts, it is also nice to save the output 😊 Now I want to describe how you can write the output to the Azure Blob Storage. The following script exports the assigned Users to a Power BI Pro License – the same story from my last post. I have created before a Storage account and a container within.

$connectionName = "AzureRunAsConnection"

try {
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

     "Logging in to Azure..."
    Add-AzureRmAccount `
    -ServicePrincipal `
    -TenantId $servicePrincipalConnection.TenantId `
    -ApplicationId $servicePrincipalConnection.ApplicationId `
    -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}

# the following user writes to the blob storage and needs also access to the AD
$myPassword = '<Password>'
$myUsername = 'philipp@plenz.onmicrosoft.com'

$password = ConvertTo-SecureString $myPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)
Connect-AzureAD -Credential $credential

$date = Get-Date -Format "ddMMyyyy"

$PBIProServicePlanID = "70d33638-9c74-4d01-bfd3-562de28bd4ba"
$ProLicenseFile = 'proLicense' + $date  + '.csv'

$ADUsers = Get-AzureADUser -All $true | Select-Object ObjectId, ObjectType, DisplayName, UserPrincipalName, UserType, @{Name="Date Retrieved";Expression={$RetrieveDate}}

$OrgO365Licenses = Get-AzureADSubscribedSku | Select-Object SkuID, SkuPartNumber,CapabilityStatus, ConsumedUnits -ExpandProperty PrepaidUnits | `
    Select-Object SkuID,SkuPartNumber,CapabilityStatus,ConsumedUnits,Enabled,Suspended,Warning, @{Name="Retrieve Date";Expression={$RetrieveDate}} 
   
$UserLicenseDetail = ForEach ($ADUser in $ADUsers)
    {
        $UserObjectID = $ADUser.ObjectId
        $UPN = $ADUser.UserPrincipalName
        $UserName = $ADUser.DisplayName
        Get-AzureADUserLicenseDetail -ObjectId $UserObjectID -ErrorAction SilentlyContinue | `
        Select-Object ObjectID, @{Name="User Name";Expression={$UserName}},@{Name="UserPrincipalName";Expression={$UPN} `
        } -ExpandProperty ServicePlans
    }
$ProUsers = $UserLicenseDetail | Where-Object {$_.ServicePlanId -eq $PBIProServicePlanID}

Set-AzureRmCurrentStorageAccount -StorageAccountName savedfiles -ResourceGroupName PBI
$MSOPwd = ConvertTo-SecureString -String $myPassword -AsPlainText -Force
$MSOCred = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $myUsername, $MSOPwd

# append the output to a CSV format
write-output $ProUsers | Export-Csv savedfiles -Append
# write the output to the blob storage
Set-AzureStorageBlobContent -Container test -File savedfiles -Blob $ProLicenseFile

After running the script, the output is available at the container and can also used at Power BI with the Blob Connector.