Working with Azure Tables from PowerShell - AzureRmStorageTable/AzTable PS Module v2.0
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:
- Installation/Source Code
- Using Azure Storage Table PowerShell Module
- Azure Automation
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 18.104.22.168.
Update 05/22/2017: Since module version 22.214.171.124 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.
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:
|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
|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||Returns one or more rows/entities based on custom Filter|
|Get-AzTableRowByPartitionKey||Returns one or more rows/entities based on Partition Key|
|Get-AzTableRowByPartitionKeyRowKey||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 compabilitiy 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
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:
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.
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
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 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.
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
Getting rows/entities by partition key
Getting rows/entities by specific column
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.
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.
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:
- Retrieve the entity
- Delete the entity passing the retrieved one as argument
Deleting a single row/entity by piping the entity
Deleting a single row/entity passing entity as argument
Deleting an entry by using PartitionKey and RowKey directly
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.
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 AzTablein 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.
For more information about Azure Storage Tables, please refer to the following documents: