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. Now supporting the new Az PowerShell module plus the SDK change from Microsoft.WindowsAzure.Storage to Microsoft.Azure.Cosmos assembly.

In this blog post

Topics covered in this blog post:

• Updates
• Introduction
• Requirements
• Installation/Source Code
• Using Azure Storage Table PowerShell Module
  • Adding Rows/Entities
  • Retrieving Rows/Entities
  • Updating an entity
  • Deleting rows/entities
• Azure Automation
• Troubleshooting
• References

Updates

Update 09/23/2019: As of this date this blog post will not be updated anymore, for updated information, please use the docs section on this module’s repo on Github.

Update 08/06/2019: Comments were removed from this blog post due to efforts to centralize discussions and/or issues into the module repo due to this module ownership being changed. If you have any issues/comments, please file a comment/issue here.

Update 04/04/2019: To avoid confusion if this module supports the new Az Powershell module, a new module name was released in the PowerShell Gallery under the name AzTable. The old name, AzureRmStorageTable will be kept in the gallery for compatibility purposes, both references the same code. This document will reference the new module name only.

Update 03/26/2019: Updates to use the new Az.Storage PowerShell module, which is now the requirement for this module to work since the old Microsoft.WindowsAzure.Storage assembly got replaced by Microsoft.Azure.Cosmos. It also runs on PowerShell core as well, tested on PS 5.1, PS 6.2 and Linux PS. Kudos to jakedenyer for his contributions on async methods.

Update 03/08/2018: Due to a number of conflicts related to assemblies needed on Storage Tables and Cosmos DB, as of this date, Cosmos DB support was removed from this module and I’ll be working in a separate module for Cosmos DB and this will be release shortly.

Update 12/04/2017: Due to a large number of customers that have applications that adds a Timestamp entity attribute to the tables, the default Azure Table system attribute called Timestamp got renamed to TableTimeStamp so customers can consume their application’s own Timestamp value and avoid conflicts, this might be a breaking change but it is a necessary change. This change is implemented on version 1.0.0.21.

Update 05/22/2017: Since module version 1.0.0.10 it was added a cmdlet (Get-AzureStorageTableTable) and support for Azure Cosmos DB Tables, for more information please refer to Azure RM Storage Tables PowerShell module now includes support for Cosmos DB Tables blog post.

Release Notes: For more details about updates being done in the module, please refer to its release notes on GitHub.

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) based on key-value pairs.

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-AzTableRow	Adds a row/entity to a specified table
Get-AzTableRow	Used to return entities from a table with several options, this replaces all other Get-AzTable cmdlets.
Get-AzTableRowAll	(Deprecated) Returns all rows/entities from a storage table - no Filtering
Get-AzTableRowByColumnName	(Deprecated) Returns one or more rows/entities based on a specified column and its value
Get-AzTableRowByCustomFilter	(Deprecated) Returns one or more rows/entities based on custom Filter
Get-AzTableRowByPartitionKey	(Deprecated) Returns one or more rows/entities based on Partition Key
Get-AzTableRowByPartitionKeyRowKey	(Deprecated) Returns one entity based on Partition Key and RowKey
Get-AzTableTable	Gets a Table object to be used in all other cmdlets
Remove-AzTableRow	Removes a specified table row
Update-AzTableRow	Updates a table entity

Note: For compatibility purposes, aliases were created for the old noun style AzureStorageTable. New noun, AzTable, is the replacement that will allow you to automatically load the AzTable. If you wish to continue to use the old names, make sure you explicitly load the module before using them.

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 5.1 or greater (this is also supported on Linux PowerShell) This module requires the new Az Modules:

• Az.Storage (1.1.0 or greater)
• Az.Resources (1.2.0 or greater)

You can get this information by issuing the following command line from PowerShell:

Installation/Source Code

Since this module is published on PowerShell Gallery, you can install this module directly from PowerShell 5.1 or greater and Windows 10 / Windows Server 2016 / Linux by executing the following cmdlet in an elevated (Windows) PowerShell command prompt window:

As an alternative, you can manually download the module from my GitHub repository here and extract to C:\Program Files\WindowsPowerShell\Modules for PowerShell 5.1 or C:\Program Files\PowerShell\Modules for PowerShell 6.2 , or greater, and rename the folder to AzureRmStorageTable. Please remember to unblock the zip file before extracting it.

Using 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:

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

You should see the following cmdlets:

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 an storage account already created 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 partitions.

Get a reference for your table with the following cmdlet:

You can check details of your table by executing typing $table:

Optionally, you can obtain the table reference by using the Az.Storage cmdlets:

Note: With release of Az.Storage 1.1.0, you need to reference the CloudTable property of the table returned from Get-AzStorageTable, that was not needed in the previous versions, ideally you will use Get-AzTableTable cmdlet instead but there might be cases where you need the Get-AzStorageTable.

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

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

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

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

Retrieving Rows/Entities

When retrieving rows using the functions described below, they will return a PSObject instead of a DynamicTableEntity and since they will give you some extra work to manipulate/access the properties I decided to make the Get cmdlets return PSObject instead.

Example a DynamicTableEntity object:

Example of PSObject when it is returned from the functions exposed in this module:

Note: when working with custom queries, the properties to be used are the ones that are shown in the DynamicTableEntity. If you’re bringing more rows and then will be using PowerShell features for filtering (e.g. Where-Object), then you must refer to the PSObject properties instead.

Retrieving all rows/entities

Result

Getting rows/entities by partition key

Result

Getting rows/entities by specific column

Result

Example using a guid entity type:

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

The custom filter is an important option since we are not covering all data types from the module, for example datetime types.

Note: When working with logical operators (and, or, not), they are case-sensitive and must be used as lower case since the underlying SDK uses OData v3 specification. Here you can find more information.

Simple filter

Result

Combined filter

Result

String filter

Result

Updating an entity

This process requires three steps:

• Retrieve the row/entity to update
• Perform the change on this retrieved item
• Commit the change

Note: Update-AzTableRow function will accept one entry at a time, don’t pass an array of entities or pipe an array of entities to the function.

Example:

Result

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

Result

Deleting a single row/entity passing entity as argument

Result

Deleting an entry by using PartitionKey and RowKey directly

Result

Deleting everything

Azure Automation

In order to run this module from within Azure Automation Runbooks, please make sure you follow the steps outlined at Az module support in Azure Automation.

Troubleshooting

This section helps you troubleshoot some of the most common issues:

• Method invocation failed because [Microsoft.WindowsAzure.Commands.Common.Storage.ResourceModel.AzureStorageTable] does not contain a method named ‘ExecuteQuerySegmentedAsync’.

  Please refer to this issue discussion on Github repo.

• New-Object : Cannot find type [Microsoft.Azure.Cosmos.Table.TableQuery]: verify that the assembly containing this type is loaded.At C:\Program Files\WindowsPowerShell\Modules\AzureRmStorageTable\2.0.1\AzureRmStorageTableCoreHelper.psm1

  Make sure you have Az.Storage (1.1.0 or greater) installed, be aware that in case of duplicated modules installed you also may have the same issue, this discussion highlight another way this may happen and how to fix it (solution 2 that I presented).

• The term ‘Update-AzureStorageTableRow’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

  This error can happen with with other cmdlets as well, e.g. Get-AzureStorageTableTable, this happens because all cmdlets got renamed as previously mentioned in this article, aliases were created to keep compatibility but you need to perform an Import-Module AzureRmStorageTable or Import-Module AzTable in order to load the aliases since the module auto-load only happens for the cmdlets itself and not the aliases. I strongly advise that you rename your cmdlets as soon as possible to avoid future issues.

References

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

• Perform Azure Table storage operations with Azure PowerShell
• 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