azure_migrate-to-azure_migrate-to-azureAzure 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


Courtesey of Paulo Marques (MSFT)

Leave a Reply