Retrieving Last Modified Dates for All Tables in Azure Storage Account

Question

Retrieving Last Modified Dates for All Tables in Azure Storage Account

Niket Kumar Singh 455

obtaining the last modified dates for all tables within our Azure Storage account. The objective is to monitor and manage data effectively without making any modifications to the existing data.

e have attempted to use Azure PowerShell cmdlets to achieve this. Our approach included:

Setting the Azure Subscription Context:


Set-AzContext -SubscriptionId "<Your Subscription ID>"

Retrieving the Storage Account Key:


$storageAccountKey = (Get-AzStorageAccountKey -ResourceGroupName "<Your Resource Group>" -Name "<Your Storage Account>").Value[0]

Creating a Storage Context:


$storageContext = New-AzStorageContext -StorageAccountName "<Your Storage Account>" -StorageAccountKey $storageAccountKey

Iterating Over Each Table to Fetch Entities and Determine the Last Modified Date:


$tables = Get-AzStorageTable -Context $storageContext
foreach ($table in $tables) {
    $entities = Get-AzTableRow -TableName $table.Name -Context $storageContext | Sort-Object Timestamp -Descending
    $latestTimestamp = $entities[0].Timestamp.DateTime.ToString("yyyy-MM-dd HH:mm:ss")
    Write-Output "Table: $($table.Name) | Last Modified: $latestTimestamp"
}

Challenges Encountered:

The Get-AzTable cmdlet is not recognized, indicating that the AzTable module may not be installed or imported correctly.
Errors related to parameter sets not being recognized, leading to execution failures.

Objective:

We seek guidance on:

Module Installation and Import: How can we ensure that the AzTable module is installed and imported correctly in our PowerShell environment?

Retrieving Last Modified Dates: What is the recommended approach to retrieve the last modified dates for all tables in an Azure Storage account using PowerShell?

Ensuring Read-Only Operations: How can we confirm that the script performs only read-only operations to prevent any unintended data modifications?

Error Handling: What are the best practices for handling errors and exceptions in this context to ensure the script runs smoothly?

Additional Information:

We have already defined the storage account name, resource group name, and subscription ID.
We are operating within the Azure Cloud Shell environment, which should have the necessary modules pre-installed.

Any insights, code snippets, or references to relevant documentation would be greatly appreciated.

Thank you for your assistance.e have attempted to use Azure PowerShell cmdlets to achieve this.

Keshavulu Dasari 4,280 Reputation points Microsoft External Staff

2025-03-10T22:25:19.6+00:00
Hi Niket Kumar Singh,

To retrieve the last modified dates for all tables in your Azure Storage account using PowerShell, Ensure that the AzTable module is installed. You can install it using the following command:

Install-Module AzTable

After installation, import the module if necessary:

Import-Module AzTable

Your approach to retrieve the last modified dates is mostly correct. Ensure that you are using the correct cmdlets. The Get-AzTableRow cmdlet is used to fetch entities from a table, and you can sort them by the Timestamp property to find the last modified date. refined version of your script:

$tables = Get-AzStorageTable -Context $storageContext foreach ($table in $tables) { $entities = Get-AzTableRow -Table $table.CloudTable | Sort-Object Timestamp -Descending if ($entities.Count -gt 0) { $latestTimestamp = $entities[0].Timestamp.DateTime.ToString("yyyy-MM-dd HH:mm:ss") Write-Output "Table: $($table.Name) | Last Modified: $latestTimestamp" } else { Write-Output "Table: $($table.Name) | No entities found." } }

The operations you are performing (retrieving entities and their timestamps) are read-only. To confirm this, ensure that you are not using any cmdlets that modify data, such as Add-AzTableRow or Remove-AzTableRow.

Implement error handling using try-catch blocks to manage exceptions gracefully. Example:

try { # Your code to retrieve tables and entities } catch { Write-Error "An error occurred: $_" }

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be glad to assist you.
Niket Kumar Singh 455 Reputation points

2025-03-11T07:52:14.1533333+00:00
Issue: "Forbidden" Error When Querying Azure Table Storage via PowerShell (AzTable & REST API)

Scenario : I'm trying to query specific Azure Table Storage tables using PowerShell in Azure Cloud Shell. The storage account is of General Purpose v1, and authentication is attempted via Storage Account Key.

Steps Taken & Mechanism Tried:

✅ 1. Set Subscription Context

Set-AzContext -SubscriptionId $subscriptionId -ErrorAction Stop

Succeeded: Subscription set correctly.

✅ 2. Retrieve Storage Account Key

$storageKey = (Get-AzStorageAccountKey -ResourceGroupName $resourceGroup -Name $storageAccount -ErrorAction Stop).Value[0]

Succeeded: Storage Key was retrieved successfully.

✅ 3. Create Storage Context

$storageContext = New-AzStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageKey -ErrorAction Stop

Succeeded: Storage Context created.

✅ 4. Install & Import AzTable Module

Install-Module -Name AzTable -Force -AllowClobber Import-Module AzTable

Succeeded: Module imported.

❌ 5. Query Tables Using AzTable

$table = Get-AzStorageTable -Name "LinuxCpuVer2v0" -Context $storageContext $entities = Get-AzTableRow -Table $table.CloudTable | Sort-Object Timestamp -Descending

Failed with: Forbidden Error

❌ 6. Check RBAC Permissions

Get-AzRoleAssignment -ObjectId "<User Object ID>" | Select-Object RoleDefinitionName, Scope

Roles assigned to the user:

Storage Account Contributor

Storage Table Data Contributor

Despite having the correct roles, access is still blocked.

❌ 7. Retry with REST API (Shared Key Authentication)

$headers = @{ "x-ms-version" = "2019-07-07" "Authorization" = "SharedKey $storageAccount:$storageKey" } $url = "https://$storageAccount.table.core.windows.net/LinuxCpuVer2v0()" $response = Invoke-RestMethod -Uri $url -Method GET -Headers $headers

Failed with: Forbidden Error

❌ 8. Verified Shared Key Access

az storage account show --name $storageAccount --resource-group $resourceGroup --query allowSharedKeyAccess

Returned: true (indicating Shared Key Authentication is enabled).

Issue Summary

Even with correct RBAC roles, Storage Key authentication, and REST API, all attempts to query the table fail with Forbidden.

This suggests Azure Table Storage (General Purpose v1) might require an additional setting or different authentication mechanism.

Question for the Community

Has anyone encountered this issue when using PowerShell to query Table Storage in a General Purpose v1 account? Is there an undocumented setting or workaround needed to allow access?

Any help would be appreciated!
Keshavulu Dasari 4,280 Reputation points Microsoft External Staff

2025-03-11T19:04:08.66+00:00

Hi Niket Kumar Singh ,

I understand the Forbidde error when trying to query Azure Table Storage using PowerShell and the REST API, despite having the correct RBAC roles and enabling Shared Key authentication. I Suggest few things to consider that might help resolve your issue:

Ensure that your storage account is indeed configured to allow Table Storage operations. The General Purpose v1 accounts should support Table Storage, but double-check the configuration.

You mentioned having the "Storage Account Contributor" and "Storage Table Data Contributor" roles. Ensure that these roles are assigned at the correct scope (i.e., the specific storage account) and that there are no deny assignments that could be affecting access.

Since you verified that Shared Key access is enabled, ensure that the key you are using is the primary key and that there are no issues with the key itself.

Make sure that the API version specified in your REST API call is supported by your storage account. You are using "2019-07-07," which should generally work, but you might want to try a more recent version if available.

Check if there are any networking rules or firewalls set up on the storage account that could be blocking access from your current environment. If possible, use Azure Storage Explorer to try accessing the table. This can help determine if the issue is specific to your PowerShell script or a broader access issue.

For more information:

https://learn.microsoft.com/en-us/azure/storage/tables/table-storage-how-to-use-powershell#authorizing-table-data-operations

https://learn.microsoft.com/en-us/azure/storage/common/shared-key-authorization-prevent?tabs=portal#verify-that-shared-key-access-is-not-allowed.

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be glad to assist you.

Accepted answer

0 additional answers

Your answer

Keshavulu Dasari 4,280 Reputation points Microsoft External Staff

2025-03-10T22:25:19.6+00:00

Hi Niket Kumar Singh,

To retrieve the last modified dates for all tables in your Azure Storage account using PowerShell, Ensure that the AzTable module is installed. You can install it using the following command:

Install-Module AzTable

After installation, import the module if necessary:

Import-Module AzTable

Your approach to retrieve the last modified dates is mostly correct. Ensure that you are using the correct cmdlets. The Get-AzTableRow cmdlet is used to fetch entities from a table, and you can sort them by the Timestamp property to find the last modified date. refined version of your script:

$tables = Get-AzStorageTable -Context $storageContext foreach ($table in $tables) { $entities = Get-AzTableRow -Table $table.CloudTable | Sort-Object Timestamp -Descending if ($entities.Count -gt 0) { $latestTimestamp = $entities[0].Timestamp.DateTime.ToString("yyyy-MM-dd HH:mm:ss") Write-Output "Table: $($table.Name) | Last Modified: $latestTimestamp" } else { Write-Output "Table: $($table.Name) | No entities found." } }

The operations you are performing (retrieving entities and their timestamps) are read-only. To confirm this, ensure that you are not using any cmdlets that modify data, such as Add-AzTableRow or Remove-AzTableRow.

Implement error handling using try-catch blocks to manage exceptions gracefully. Example:

try { # Your code to retrieve tables and entities } catch { Write-Error "An error occurred: $_" }

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be glad to assist you.
Keshavulu Dasari 4,280 Reputation points Microsoft External Staff

2025-03-11T19:04:08.66+00:00

Hi Niket Kumar Singh ,

I understand the Forbidde error when trying to query Azure Table Storage using PowerShell and the REST API, despite having the correct RBAC roles and enabling Shared Key authentication. I Suggest few things to consider that might help resolve your issue:

Ensure that your storage account is indeed configured to allow Table Storage operations. The General Purpose v1 accounts should support Table Storage, but double-check the configuration.

You mentioned having the "Storage Account Contributor" and "Storage Table Data Contributor" roles. Ensure that these roles are assigned at the correct scope (i.e., the specific storage account) and that there are no deny assignments that could be affecting access.

Since you verified that Shared Key access is enabled, ensure that the key you are using is the primary key and that there are no issues with the key itself.

Make sure that the API version specified in your REST API call is supported by your storage account. You are using "2019-07-07," which should generally work, but you might want to try a more recent version if available.

Check if there are any networking rules or firewalls set up on the storage account that could be blocking access from your current environment. If possible, use Azure Storage Explorer to try accessing the table. This can help determine if the issue is specific to your PowerShell script or a broader access issue.

For more information:

https://learn.microsoft.com/en-us/azure/storage/tables/table-storage-how-to-use-powershell#authorizing-table-data-operations

https://learn.microsoft.com/en-us/azure/storage/common/shared-key-authorization-prevent?tabs=portal#verify-that-shared-key-access-is-not-allowed.

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be glad to assist you.

Answer 1

Hi @Niket Kumar Singh

Retrieving Last Modified Dates for All Tables in Azure Storage Account

You can use the below PowerShell code that retrieves the Azure table storage entities with last modified table.

Make sure you have assigned the Storage Table Data Contributor role to your user for the storage account.

Script:

Connect-Azaccount -Subscription "xxx"

$resourceGroup = "xxxx"
$storageAccount = "xxxxx"

try {
    # Get the storage account key
    $storageKey = (Get-AzStorageAccountKey -ResourceGroupName $resourceGroup -Name $storageAccount -ErrorAction Stop).Value[0]
    
    # Create a storage context
    $storageContext = New-AzStorageContext -StorageAccountName $storageAccount -StorageAccountKey $storageKey -ErrorAction Stop
    
    # Get all table names in the storage account
    $tables = Get-AzStorageTable -Context $storageContext

    # Iterate through each table and fetch rows
    foreach ($table in $tables) {
        $tableName = $table.CloudTable.Name
        $CloudTable = $table.CloudTable

        Write-Host "Fetching rows from table: $tableName"

        # Get all rows from the table and sort by Timestamp in descending order
        $entities = Get-AzTableRow -Table $CloudTable | 
                    Select-Object @{Name="TableName"; Expression={$tableName}}, PartitionKey, RowKey, TableTimestamp, Property1, Property2, ETag |
                    Sort-Object TableTimestamp -Descending

        # Output results
        if ($entities) {
            $entities | Format-Table -AutoSize
        } else {
            Write-Host "No data found in table: $tableName"
        }
    }
}
catch {
    Write-Error "An error occurred: $_"
}

Output:


Fetching rows from table: demo

TableName PartitionKey RowKey TableTimestamp             Property1 Property2 Etag                                          
--------- ------------ ------ --------------             --------- --------- ----                                          
demo      test         venkat 12-03-2025 19:45:04 +05:30                     W/"datetime'2025-03-12T14%3A15%3A04.5351661Z'"


Fetching rows from table: table1

TableName PartitionKey RowKey     TableTimestamp             Property1 Property2 Etag                                          
--------- ------------ ------     --------------             --------- --------- ----                                          
table1    name         venkat890  12-03-2025 19:18:51 +05:30                     W/"datetime'2025-03-12T13%3A48%3A51.2879744Z'"
table1    name         venkat     17-10-2024 12:03:37 +05:30 test      sample    W/"datetime'2024-10-17T06%3A33%3A37.2273905Z'"
table1    name         venkatesan 19-08-2024 11:07:21 +05:30 test      key       W/"datetime'2024-08-19T05%3A37%3A21.1385156Z'"

enter image description here

The above script:

Fetches all tables in the storage account.
Iterates through each table.
Retrieves all rows from each table and sorts them by TableTimestamp in descending order.
Displays the results in a table format.

REST API (Shared Key Authentication):

You have used account key directly in authorization that could cause the error here.

You can use the below script which could be useful to fetch the table entities using REST API.

Script:

$storageAccountName = "xxxx"
$storageKey = "xxxxx"

# Table name
$tableName = "xxxx"

# Construct the request URL
$url = "https://$storageAccountName.table.core.windows.net/$tableName()"

# Headers
$version = "2021-02-12"
$date = (Get-Date).ToUniversalTime().ToString("R")  # RFC1123 format

# Canonicalized resources and headers
$canonicalizedResources = "/$storageAccountName/$tableName()"
$canonicalizedHeaders = "x-ms-date:$date"

# String to sign
$stringToSign = "$date`n$canonicalizedResources"

# Compute the HMAC-SHA256 signature
$keyBytes = [Convert]::FromBase64String($storageKey)
$stringToSignBytes = [Text.Encoding]::UTF8.GetBytes($stringToSign)
$hmacsha256 = New-Object System.Security.Cryptography.HMACSHA256
$hmacsha256.Key = $keyBytes
$signatureBytes = $hmacsha256.ComputeHash($stringToSignBytes)
$signature = [Convert]::ToBase64String($signatureBytes)

# Authorization header
$authHeader = "SharedKeyLite $storageAccountName`:$signature"

# Make the request
$headers = @{
    "x-ms-date" = $date
    "x-ms-version" = $version
    "Authorization" = $authHeader
    "Accept" = "application/json;odata=nometadata"
}

$response = Invoke-RestMethod -Uri $url -Method Get -Headers $headers
$response | ConvertTo-Json -Depth 10  # Pretty-print the response

Output:

{
    "value":  [
                  {
                      "PartitionKey":  "name",
                      "RowKey":  "venkat",
                      "Timestamp":  "2024-10-17T06:33:37.2273905Z",
                      "Property1":  "test",
                      "Property2":  "sample"
                  },
                  {
                      "PartitionKey":  "name",
                      "RowKey":  "venkat890",
                      "Timestamp":  "2025-03-12T13:48:51.2879744Z"
                  },
                  {
                      "PartitionKey":  "name",
                      "RowKey":  "venkatesan",
                      "Timestamp":  "2024-08-19T05:37:21.1385156Z",
                      "Property1":  "test",
                      "Property2":  "key"
                  }
              ]
}

enter image description here

Reference:

Hope the above answer helps! Please let us know do you have any further queries.

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Share via

Retrieving Last Modified Dates for All Tables in Azure Storage Account

0 additional answers

Your answer