Working with Azure Storage Tables from PowerShell


Hello, this is Paulo. In this blog post I’m going to show how to work with Azure Storage Tables from PowerShell, through a sample PowerShell module that I created for operations like Add, Retrieve, Update and Delete table rows/entities exposed as functions.

 

Update 01/31/2017: Included a section in this post to show how to use this module from Azure Automation.

Introduction

Azure Storage Tables is one of the four Microsoft Azure Storage abstractions available (Blobs, Queues and Azure Files are the other ones) at the time that this blog was written. It is basically a way to store data in a structured way on a non relational database system (meaning, not an RDBMS system).

Since up to today there are no official cmdlets to support entity/row management inside the tables from Azure PowerShell module, I decided to create this simple module to help IT Pros to leverage this service without having knowledge of .NET framework through some simple cmdlets as follows:

 

Cmdlet Description
Add-StorageTableRow Adds a row/entity to a specified table
Get-AzureStorageTableRowAll Returns all rows/entities from a storage table – no filtering
Get-AzureStorageTableRowByPartitionKey Returns one or more rows/entities based on Partition Key
Get-AzureStorageTableRowByColumnName Returns one or more rows/entities based on a specified column and its value (equal comparison operation)
Get-AzureStorageTableRowByCustomFilter Returns rows/entities from a table based on customer filtering
Update-AzureStorageTableRow Updates a specified table row/entity
Remove-AzureStorageTableRow Removes a specified table row

 

There are a number of use cases for an IT Pro work with Azure Tables from PowerShell where it becomes a great repository, the ones below are just few examples:

  • Logging for SCCM executed scripts
  • Azure Automation for VM expiration, shutdown, startup in classic mode (Azure Service Manager)
  • VM Deployment scripts, where it becomes a central location for logs
  • Easily extract Performance and Diagnostics data from VMs with Diagnostics enabled

 

Requirements

  • PowerShell 4.0 or greater
  • This module requires Azure PowerShell module installed, which can be downloaded from http://aka.ms/webpi-azps. Despite of some cmdlets we need to use to get the storage account context table resource, we also use two DLLs directly that are included on it, Microsoft.WindowsAzure.Storage.dll and Microsoft.WindowsAzure.Commands.Common.Storage.dll.

 

Installation/Source Code

Since this module is published on PowerShell Gallery, you can install this module directly from PowerShell 5.0 and Windows 10 by executing the following cmdlet in an elevated PowerShell command prompt window:

Install-Module AzureRmStorageTable

 

Note that if you want to use the PowerShellGet module in other Windows versions, please look at the https://www.powershellgallery.com/ initial page on how to download and install it.

 

You can manually download it from my GitHub repository in this link as well and extract to C:\Program Files\WindowsPowerShell\Modules and rename the folder to AzureRmStorageTable.

 

Working with Azure Storage Table PowerShell Module

The following steps will walk you through loading the module and perform one or more example tasks of the basic operations offered in this module.

 

Before you start working with it, you need to authenticate to Azure and select the correct subscription if you have multiple subscriptions:

Add-AzureRmAccount
Select-AzureRmSubscription -SubscriptionName <your subscription>

 

Next, lets import AzureRmTableStorage PowerShell module and list the functions available on it:

Import-Module AzureRmStorageTable
Get-Command -Module AzureRmStorageTable

 

You should see the following cmdlets

image

 

For the sake of simplicity, we need to define some variables at this point to make our examples a little bit more clean, please, make sure you have a table already created in your storage account and that you change the values of the variables below to reflect your environment. Notice that one of the variables is called $partitionKey, in this example we are using only one partition, please refer to the documentation at the end of this blog in order to get a better understanding on benefits of partitions.

$subscriptionName = "<your subscription name here>"
$resourceGroup = "sample-azuretablesblog-rg"
$storageAccount = "azuretableblogstorage01"
$tableName = "table01"
$partitionKey = "LondonSite"
 

Some storage account operations requires the usage of what is called Context. To obtain that object within a single command line, please execute the line below to get the context object:

$saContext = (Get-AzureRmStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount).Context
 

OPTIONAL: If you didn’t create the table using Azure Storage Explorer (downloaded from here) or by any other means yet, please use the following cmdlet to perform this operation at this moment (make sure that you set the variable $tableName to have the new table name since we may use a reference to this variable in subsequent cmdlets.

New-AzureStorageTable –Name $tableName –Context $saContext

 

Lastly, let’s get the table resource, important step because all cmdlets will need to use this table as argument. This will be stored under the $table object.

$table = Get-AzureStorageTable -Name $tableName -Context $saContext

 

Up to this point we just prepared our PowerShell session by authenticating, importing the module, setting up some variables and getting our table, from this point moving forward we will focus on the basic operations exposed through the module. I’m creating a section per function/operation.

 

Adding Rows/Entities

Adding lines one by one

Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ([guid]::NewGuid().tostring()) -property @{"computerName"="COMP01";"osVersion"="Windows 10";"status"="OK"}
Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ([guid]::NewGuid().tostring()) -property @{"computerName"="COMP02";"osVersion"="Windows 8.1";"status"="OK"}
Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ([guid]::NewGuid().tostring()) -property @{"computerName"="COMP03";"osVersion"="Windows XP";"status"="NeedsOsUpgrade"}

 

Result, notice the 204 HttpsStatusCode, indicating that your operation succeeded.

image

 

Getting data from a JSON string and using a foreach loop to load the data

$computerList = '[{"computerName":"COMP04","osVersion":"Windows 7","status":"OK"},{"computerName":"COMP05","osVersion":"Windows 8","status":"OK"},{"computerName":"COMP06","osVersion":"Windows XP","status":"NeedsOsUpgrade"},{"computerName":"COMP07","osVersion":"Windows NT 4","status":"NeedsOsUpgrade"}]'
$newPartitionKey = "NewYorkSite"
foreach ($computer in ($computerList | ConvertFrom-Json) )
{
    Add-StorageTableRow -table $table `
        -partitionKey $newPartitionKey `
        -rowKey ([guid]::NewGuid().tostring()) `
        -property @{"computerName"=$computer.computerName;"osVersion"=$computer.osVersion;"status"=$computer.status}
}

Result

image

 

If we open Azure Storage Explorer and navigate to the table, we will see all inserted entities.

 

image

 

Retrieving Rows/Entities

When retrieving rows using the functions described below, they will return a PS Object instead of a DynamicTableEntity since they will give you some extra work to manipulate/access the properties, so a PS Object is returned instead.

Below is an example of how a DynamicTableEntity looks like:

Properties   : {[ActivityId, Microsoft.WindowsAzure.Storage.Table.EntityProperty], [DateTime, Microsoft.WindowsAzure.Storage.Table.EntityProperty], [Message, Microsoft.WindowsAzure.Storage.Table.EntityProperty], [OriginatedVmName, Microsoft.WindowsAzure.Storage.Table.EntityProperty]…}
PartitionKey : AutomationLogs
RowKey       : 966ff6ac-6c76-4f80-8f67-0f70a8bcf367
Timestamp    : 10/2/2016 3:16:25 PM +00:00
ETag         : W/”datetime’2016-10-02T15%3A16%3A25.0144501Z'”

 

Below is an example of a DynamicTableEnitty converted into PS Object when it is returned from the functions exposed in this module:

ActivityId       : Activity-1
DateTime         : 10/02/YYYY 07:09:28
Message          : An error ocurred creating a VM.
OriginatedVmName : VM01
Severity         : Error
PartitionKey     : AutomationLogs

RowKey           : 966ff6ac-6c76-4f80-8f67-0f70a8bcf367

Retrieving all rows/entities

 

Get-AzureStorageTableRowAll -table $table | ft

 

Result

image

 

Getting rows/entities by partition key

Get-AzureStorageTableRowByPartitionKey -table $table –partitionKey “LondonSite” | ft

 

Result

image

 

Getting rows/entities by specific column

Get-AzureStorageTableRowByColumnName -table $table -columnName "computerName" 
-value "COMP01" -operator Equal


Result

image

 

Queries using custom filters with help of Microsoft.WindowsAzure.Storage.Table.TableQuery class and direct string text

Simple filter

[string]$filter1 = [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("computerName",[Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal,"COMP06")
Get-AzureStorageTableRowByCustomFilter -table $table -customFilter $filter1

 

Result

image

 

Combined filter

[string]$filter1 = [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("computerName",[Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal,"COMP03")
[string]$filter2 = [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("status",[Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal,"NeedsOsUpgrade")
 
[string]$finalFilter = [Microsoft.WindowsAzure.Storage.Table.TableQuery]::CombineFilters($filter1,"and",$filter2)
 
Get-AzureStorageTableRowByCustomFilter -table $table -customFilter $finalFilter


Result

image

 

String filter

Get-AzureStorageTableRowByCustomFilter -table $table -customFilter "(computerName eq 'COMP07') and (status eq 'NeedsOsUpgrade')"

 

Result

image

 

Updating an entry

This process requires three steps:

  1. Retrieve the row/entity to update
  2. Perform the change on this retrieved item
  3. Commit the change

 

Notice that the Update-AzureStorageTableRow function will accept one entry at a time, so don’t pass an array of entities or pipe an array of entities to the function.

 

Example:

 

# Creating the filter and getting original entity
[string]$filter = [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("computerName ",[Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal,"COMP03")
$computer = Get-AzureStorageTableRowByCustomFilter -table $table -customFilter $filter
 
# Changing values
$computer.osVersion = "Windows 10"
$computer.status = "OK"
 
# Updating the content
$person | Update-AzureStorageTableRow -table $table
 
# Getting the entity again to check the changes
Get-AzureStorageTableRowByCustomFilter -table $table -customFilter $filter

 

Result

image

 

Deleting rows/entities

Similarly to the update process here we have two steps as follows unless you know the partitionKey and rowKey properties, in this case you can delete directly:

  1. Retrieve the entity
  2. Delete the entity passing the retrieved one as argument

 

Deleting a single row/entity by piping the entity

 

[string]$filter1 = [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("computerName",[Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal,"COMP02")
$computerToDelete = Get-AzureStorageTableRowByCustomFilter -table $table -customFilter $filter1
 
$computerToDelete | Remove-AzureStorageTableRow -table $table

Result

image

Deleting a single row/entity passing entity as argument

[string]$filter1 = [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("computerName",[Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal,"COMP06")
$computerToDelete = Get-AzureStorageTableRowByCustomFilter -table $table -customFilter $filter1
 
Remove-AzureStorageTableRow -table $table –entity $computerToDelete

Result

image

Deleting an entry by using PartitionKey and RowKey directly

Remove-AzureStorageTableRow -table $table -partitionKey "NewYorkSite" -rowKey "<RowKey value here>"

 

Deleting everything

Get-AzureStorageTableRowAll -table $table | Remove-AzureStorageTableRow -table $table

 

 

Using this module on Azure Automation

 

If running this module from Azure Automation, please make sure you follow these steps in order to execute it from that environment:

The script below automates and replaces the steps 1 through 4 if everything in the source computer is installed using the default paths, you can skip to step 5 directly if running this script.

$destinationFolder="c:\AzureAutomation\source"
$ArchiveFile=(Join-Path "$destinationFolder\..\" "AzureRmStorageTable.zip")
$armFolder=$null
$asmFolder=$null
$azureRmStoragetableFolder="${env:programfiles}\WindowsPowerShell\Modules\AzureRmStorageTable"
 
if (test-path "${env:ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ResourceManager\AzureResourceManager\AzureRM.Storage")
{
    $armFolder="${env:ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ResourceManager\AzureResourceManager\AzureRM.Storage"
    $asmFolder="${env:ProgramFiles(x86)}Microsoft SDKs\Azure\PowerShell\StorageAzure.Storage "
}
elseif (test-path "${env:ProgramFiles}\WindowsPowerShell\Modules\AzureRM.Storage")
{
    $armFolder="${env:ProgramFiles}\WindowsPowerShell\Modules\AzureRM.Storage"
    $asmFolder="${env:ProgramFiles}\WindowsPowerShell\Modules\Azure.Storage"
}
else
{
    throw "Required Azure PowerShell modules not installed, install via Install-Module or Web Platform Installer"
}
 
 
if (!(Test-Path $destinationFolder))
{
    mkdir $destinationFolder
}
 
# Files from AzureRm.Storage module
$files=@("Microsoft.Data.Edm.dll",`
         "Microsoft.Data.OData.dll",`
         "Microsoft.Data.Services.Client.dll",`
         "Microsoft.WindowsAzure.Commands.Common.Storage.dll",`
         "Microsoft.WindowsAzure.Storage.dll","System.Spatial.dll")
 
foreach ($file in $files)
{
    copy (join-path $armFolder $file) $destinationFolder
}
 
# Files from Azure.storage
$files=@("Microsoft.WindowsAzure.Commands.Storage.dll")
foreach ($file in $files)
{
    copy (join-path $asmFolder $file) $destinationFolder
}
 
# Files from AzureRm Storage Table module
if (Test-path "${env:programfiles}\WindowsPowerShell\Modules\AzureRmStorageTable")
{
    copy "${env:programfiles}\WindowsPowerShell\Modules\AzureRmStorageTable\*" $destinationFolder
}
else
{
    throw "AzureRm Storage Table module not installed"
}
 
# Compressing file
if (Test-Path $destinationFolder)
{
    Add-Type -Assembly System.IO.Compression.FileSystem
    Remove-Item -Path $ArchiveFile -ErrorAction SilentlyContinue
    [System.IO.Compression.ZipFile]::CreateFromDirectory($destinationFolder, $ArchiveFile)
}

 

  1. After installing the latest version of Azure PowerShell module, copy the following DLLs from one of the folders below depending on how you installed the Azure PowerShell module to a folder with the source files of this module:

    If module was installed from the Web Platform Installer (https://aka.ms/webpi-azps) the folder where you will find the DLLs is:
    C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ResourceManager\AzureResourceManager\AzureRM.Storage

    If module was installed using Install-Module method, the folder where you will find the DLLs is:
    C:\Program Files\WindowsPowerShell\Modules\AzureRM.Storage

    Microsoft.Azure.Common.dll
    Microsoft.Data.Edm.dll
    Microsoft.Data.OData.dll
    Microsoft.Data.Services.Client.dll
    Microsoft.WindowsAzure.Commands.Storage.dll
    Microsoft.WindowsAzure.Storage.dll
    System.Spatial.dll

  2. Also, copy the Microsoft.WindowsAzure.Commands.Storage.dll from C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\Storage\Azure.Storage or C:\Program Files\WindowsPowerShell\Modules\Azure.Storage, if installed via Install-Module. Notice that if you installed the Azure PowerShell module from the PowerShell gallery with Install-Module AzureRM this DLL is not installed by default, this is because you need to install Azure module as well with Install-Module Azure,

  3. Compress all files, including AzureRmStorageTable.psd1 and AzureRmStorageTableCoreHelper.psm1 from the Azure Storage Tables module (which can be located in any folder if you downloaded the files directly from GitHub or located at C:\Program Files\WindowsPowerShell\Modules\AzureRmStorageTable if you installed it via Install-Module AzureArmStorageTable method) into a file called AzureRmStorageTable.zip

  4. At your Azure Automation account, open “Assets” and click on “Modules”

  5. Click “Browse gallery” and install the following modules in this order (please give some wait until functions get extracted between modules) if they are not present or update if the Automation Account was created before January 2017 since it will contain outdated modules:

    AzureRM.profile
    Azure.Storage
    AzureRM.Storage

  6. Back at the Modules blade, click “Add a module” and select your ZIP file with the required DLLs plus the AzureRmStorageTable module files

 

Runbook Sample Code for Azure Resource Manager based Storage:

 

$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
    }
}
 
Import-Module AzureRmStorageTable
 
$resourceGroup = "resourceGroup01"
$storageAccount = "storageAccountName"
$tableName = "table01"
$partitionKey = "TableEntityDemo"
 
$saContext = (Get-AzureRmStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount).Context
$table = Get-AzureStorageTable -Name $tableName -Context $saContext
 
# Adding rows/entities
Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ([guid]::NewGuid().tostring()) -property @{"firstName"="Paulo";"lastName"="Costa";"role"="presenter"}
 
# Getting all rows
Get-AzureStorageTableRowAll -table $table

 

Runbook Sample Code for Azure Service Manager (classic) based Storage:

$ConnectionAssetName = "AzureClassicRunAsConnection"
 
# Get the connection
$connection = Get-AutomationConnection -Name $connectionAssetName        
 
# Authenticate to Azure with certificate
Write-Verbose "Get connection asset: $ConnectionAssetName" -Verbose
$Conn = Get-AutomationConnection -Name $ConnectionAssetName
if ($Conn -eq $null)
{
    throw "Could not retrieve connection asset: $ConnectionAssetName. Assure that this asset exists in the Automation account."
}
 
$CertificateAssetName = $Conn.CertificateAssetName
Write-Verbose "Getting the certificate: $CertificateAssetName" -Verbose
$AzureCert = Get-AutomationCertificate -Name $CertificateAssetName
if ($AzureCert -eq $null)
{
    throw "Could not retrieve certificate asset: $CertificateAssetName. Assure that this asset exists in the Automation account."
}
 
Write-Verbose "Authenticating to Azure with certificate." -Verbose
Set-AzureSubscription -SubscriptionName $Conn.SubscriptionName -SubscriptionId $Conn.SubscriptionID -Certificate $AzureCert 
Select-AzureSubscription -SubscriptionId $Conn.SubscriptionID
 
Import-Module AzureRmStorageTable
  
$resourceGroup = "pmcrg01"
$storageAccount = "pmcstorage600"
$tableName = "table01"
$partitionKey = "TableEntityDemo"
 
Set-AzureSubscription -SubscriptionName $Conn.SubscriptionID -CurrentStorageAccountName $StorageAccount
 
$saKey = (Get-AzureStorageKey -StorageAccountName $StorageAccount).Primary
write-output "sas key" + $saKey 
 
$saContext = New-AzureStorageContext -StorageAccountName $storageAccount -StorageAccountKey $saKey
if($saContext -ne $null)
{    
    write-output "StorageContext" + $saContext
    $table = Get-AzureStorageTable -name $tableName -Context $saContext
    write-output "table" + $table
    if($table)
    {
        # Adding rows/entities
        Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ([guid]::NewGuid().tostring()) -property @{"firstName"="Paulo";"lastName"="Costa";"role"="presenter"}
        
        # Getting all rows
        Get-AzureStorageTableRowAll -table $table
    }
}

 

 

 

That’s it for this blog and I hope you enjoy this module to help you use Azure Tables from PowerShell.

 

For more information about Azure Storage Tables, please refer to the following documents:

Get started with Azure Table storage using .NET

Azure Storage Client Library for .NET

Getting Started with Azure Table Storage in .NET

Azure Storage Table Design Guide: Designing Scalable and Performant Tables


Comments (8)

  1. Felix Bodmer says:

    Thanks, this is excellent and desperately needed. Any plans to add this to the Azure PowerShell module?

    1. Hi Felix,

      I’m talking to the product group but no plans so far.

      Regards

      Paulo

  2. J Metz says:

    Hi Paulo,
    I’m very excited to start using this module, but I get this message when I try to import the module after installation.. I have the latest Azure Powershell module installed. Any ideas?
    import-module : Azure PowerShell module must be installed in order to expose Microsoft.WindowsAzure.Storage.dll file.
    At line:1 char:1
    + import-module AzureRMStorageTable

    1. Hi J Metz,

      Glad to hear that it will help you :-), now, related to your issue, when working from a regular PowerShell command prompt session or scheduled task the module looks for two Dlls, Microsoft.WindowsAzure.Storage.dll and Microsoft.WindowsAzure.Commands.Common.Storage.dll at “${env:ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ResourceManager\AzureResourceManager\AzureRM.Storage” path, which is the default installation path of the Azure PowerShell module (http://aka.ms/webpi-azps).

      Can you please check if you have these two Dlls at that folder? Also, please execute “get-module -name Azure -ListAvailable” and check if the version you have is 3.4.0.

      Not related to your question/issue, but I just found a bug on 1.0.0.5 version and fixed on 1.0.0.6 version, please update your local module with this new version as well.

      Regards

      Paulo

      1. J Metz says:

        Thanks Paulo, I have everything working now!!

        One thing I am still a bit confused about is the fact that you reference the SDK path vs the default PSGet\Install-Module target folder (C:\Program Files\WindowsPowershell). Since there are two “correct ways” of acquire these modules\dll’s, do you think you might be able to check both the SDK and PSGet paths for the proper dll’s during your validation checks? I had a few discussions with our PFE and people in the Powershell product group to try to better understand these two acquisition methods, so I think this might be the way to go. Let me know what you think and thanks again for this great module. 🙂

        1. Yep, I completely forgot about the module installed via Install-Module, I always use the web platform installer. I’ll add support for those folders as well.

          1. J Metz says:

            Thank you sir. 😀

  3. Charles says:

    Excellent. Thanks Paulo. Tao Yang also wrote a module to do pretty much the same. Thanks a lot guys.
    http://blog.tyang.org/2016/11/30/powershell-module-for-managing-azure-table-storage-entities/

Skip to main content