Configuring total retention period for log analytics workspace tables at scale

In this blog, we will discuss an automated approach to configure the total retention period for log analytics workspace tables. Before we dive into the solution, let’s learn more about retention options for log analytics tables.

A Log Analytics workspace maintains data in two states:

• Interactive Retention: In this state, data is available for monitoring, troubleshooting, and near real-time analysis.
• Long-term retention: In this low-cost state, the data is not available for table plan functions, but is accessible through retrieval operations.

By default, all tables in a Log Analytics workspace retain data for 30 days, except log tables, which have a default retention period of 90 days. The interactive retention period can be extended up to 730 days (2 years). During this time, queries can retrieve data from the table, and that data is available for visualizations, alerts, and other features and services, depending on the table plan.

If you have configured a retention tier for your log analytics table, after the interactive retention period has passed, the data will remain in the table for the remainder of the total retention period you configured. You can configure the total retention period, including interactive retention and retention period, up to 12 years.

During a long-term retention period, you can run a search job to retrieve the specific data you need from a table and make it available for interactive queries in the search results table.

To restore archived log data from Microsoft Sentinel, specify the table and time range from which you want to restore data. Log data is typically available within a Log Analytics workspace within a few minutes. You can then use the data in high-performance queries with full Kusto Query Language (KQL) support. see us public document For more information, see Restoring archived data.

We encourage you to review our public documentation for: Restore operations in Azure Monitor Learn more about limitations and pricing models for running restore operations.

memo: My focus is to provide a scalable approach to setting the total retention period for tables in a log analytics workspace..

Let’s understand why a scalable approach to setting total retention period is important.

You may have multiple tables for which you want to set the total retention period to x days. One way to implement this change is to manually change the total retention period for each table.

Select Log Analytics workspace > Settings > Tables > Select the table of interest > Manage tables and set retention as shown below.

As you can see, we’re setting the total retention period to 220 days. This means that your data is available for interactive retention for 90 days (configure retention at the My Workspace level) and for the archive tier for 130 days.

As you can see, this is a manual approach and configuring it for multiple tables in the Log Analysis workspace can take quite a bit of time.

To automate this process, I created a PowerShell script that can update the total retention period of multiple tables (separated by commas) at once. The script is hosted on my GitHub repository. Main LogAnalyticsTableMgmt/SetTableRetentionAtScale_v1.3.ps1 · Abhishek-Sharan/LogAnalyticsTableM…

memo: We recommend that you thoroughly review your PowerShell scripts and perform appropriate testing before running them in a production environment. We take no responsibility for the script..

Please post your PowerShell script here as well.

$disclaimer = @"
**Disclaimer:**
The author of this script provides it "as is" without any guarantees or warranties of any kind. 
By using this script, you acknowledge that you are solely responsible for any damage, data loss, or other issues that may arise from its execution. 
It is your responsibility to thoroughly test the script in a controlled environment before deploying it in a production setting. 
The author will not be held liable for any consequences resulting from the use of this script. Use at your own risk.
"@

Write-Host $disclaimer

# Function to set retention for a Log Analytics table
function Set-LogAnalyticsRetention {
    param (
        [string]$ResourceGroupName,
        [string]$WorkspaceName,
        [string]$TableName,
        [int]$TotalRetentionInDays
    )

    # Check if the resource group exists
    $resourceGroup = Get-AzResourceGroup -Name $ResourceGroupName -ErrorAction SilentlyContinue
    if (-not $resourceGroup) {
        Write-Error "Resource Group '$ResourceGroupName' does not exist."
        exit 1
    }

    # Check if the workspace exists
    $workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $ResourceGroupName -Name $WorkspaceName -ErrorAction SilentlyContinue
    if (-not $workspace) {
        Write-Error "Workspace '$WorkspaceName' does not exist in Resource Group '$ResourceGroupName'."
        exit 1
    }

    # Check if the table exists
    $table = Get-AzOperationalInsightsTable -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName -TableName $TableName -ErrorAction SilentlyContinue
    if (-not $table) {
        Write-Error "Table '$TableName' does not exist in Workspace '$WorkspaceName'."
        return $false
    }

    # Update the retention period
    Update-AzOperationalInsightsTable -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName -TableName $TableName -TotalRetentionInDays $TotalRetentionInDays
    return $true
}

# Prompt for user input
$resourceGroupName = Read-Host "Enter the Resource Group Name"
$workspaceName = Read-Host "Enter the Workspace Name"
# Prompt for multiple table names
$TableName = Read-Host "Enter the Table Names (comma-separated)"

# Split the table names into an array
$TableNameArray = $TableName -split ","

$TotalRetentionInDays = Read-Host "Enter the Total Retention Period in Days"

# Loop through each table name and set the retention period
foreach ($TableName in $TableNameArray) {
    $result = Set-LogAnalyticsRetention -ResourceGroupName $resourceGroupName -WorkspaceName $workspaceName -TableName $TableName.Trim() -TotalRetentionInDays $TotalRetentionInDays
    if ($result) {
        Write-Host "Retention period set successfully for table $TableName in workspace $workspaceName."
    } else {
        Write-Host "Failed to set retention period for table $TableName in workspace $workspaceName."
    }
}

Now let’s see it in action

At this point, we hope you have downloaded the script locally. This is an interactive script and will request the following details:

• Log Analytics workspace name

• Comma separated table names whose total retention period needs to be updated

• Total retention period to set

Sign in to the Azure portal, run Azure Shell, and upload the PowerShell script.

Enter the details as below.

Here we set the total retention period for 5 tables to 250 days.

• AADManagedIdentitySignInLogs
• AADNOnInteractiveUserSignInLogs
• security warning
• security incident
• usage

When I run the script, the result is:

Here you can see the total retention period for: security warning The table has been updated to 250 days.

Let’s validate it in the Log Analytics workspace blade.

To review the total retention period, go to Log Analytics workspace > Settings > Tables.

As you can see, the total retention period for all five tables has been updated to 250 days.

This also means that we have configured an interactive retention period of 90 days and a retention period of 160 days (see below).

Reference articles: Manage data retention in Log Analytics workspace – Azure Monitor | microsoft run