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-AzureStorageTableTable Gets an Azure Storage or Azure Cosmos DB 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:

Screen Shot 2017-09-28 at 15.05.01.png

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.

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:

Screen Shot 2017-09-28 at 15.05.51.png

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

Screen Shot 2017-09-28 at 15.06.24.png

You should see the following cmdlets

image_thumb318

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.

Screen Shot 2017-09-28 at 15.06.56.png

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:

Screen Shot 2017-09-28 at 15.07.11.png

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.

Screen Shot 2017-09-28 at 15.07.45.png

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.

Screen Shot 2017-09-28 at 15.08.09.png

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

Screen Shot 2017-09-28 at 15.09.01.png

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

$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

Screen Shot 2017-09-28 at 15.10.02.png

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

Screen Shot 2017-09-28 at 15.10.23.png

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

Screen Shot 2017-09-28 at 15.11.06.pngResult

Screen Shot 2017-09-28 at 15.11.30.png

Getting rows/entities by partition key

Screen Shot 2017-09-28 at 15.11.50.pngResult

Screen Shot 2017-09-28 at 15.12.14.png

Getting rows/entities by specific column

Screen Shot 2017-09-28 at 15.12.39.png

Result

Screen Shot 2017-09-28 at 15.12.59.png

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

Screen Shot 2017-09-28 at 15.13.32.png

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

Screen Shot 2017-09-28 at 15.14.11.png

String filter

Screen Shot 2017-09-28 at 15.14.33.png

Result

Screen Shot 2017-09-28 at 15.15.29.png

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

$computer | Update-AzureStorageTableRow -table $table

# Getting the entity again to check the changes

Get-AzureStorageTableRowByCustomFilter -table $table -customFilter $filter

Result

Screen Shot 2017-09-28 at 15.16.08

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

Screen Shot 2017-09-28 at 15.17.19.png

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

Screen Shot 2017-09-28 at 15.17.47

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:

  1. On your Azure Automation account, open “Assets” and click on “Modules”
  2. 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
    AzureRM.Profile
    Azure.Storage
    AzureRM.Storage
    AzureRmStorageTable

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

}

}

 

 

 

 

Leave a Reply