Configuring total retention period for log analytics workspace tables at scale

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

A Log Analytics workspace retains data in two states:

Interactive retention: In this state, data is available for monitoring, troubleshooting, and near-real-time analytics.
Long-term retention: In this low-cost state, data isn’t available for table plan features, but can be accessed through search jobs.

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

After the interactive retention period, the data remains in the table for the remainder of the total retention period you configure provided you’ve configured archive tier for the log analytics table. You can configure total retention to up to 12 years which includes interactive retention and archive period.

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

To restore archived log data in Microsoft Sentinel, specify the table and time range for the data you want to restore. Typically, within a few minutes, the log data is available within the Log Analytics workspace. Then you can use the data in high-performance queries that support full Kusto Query Language (KQL). Please refer our public documentation for restoring archived data for more details.

I would recommend reviewing our public documentation on restore job in Azure Monitor to get more information on limitations and pricing model for executing restore operation.

Note: My focus would be on providing a scalable approach to set total retention period for tables in log analytics workspace.

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

There might be several tables where you might want to set total retention period to x days, one way to implement this change is by manually changing the total retention period for each and every table.

Log Analytics Workspace > Settings > Tables > Choose the table of your interest > Manage Table and set the retention as shown below.

As you can see, we’re setting total retention period of 220 days here which means data will be available for interactive retention for 90 days (retention configuration at my workspace level) and in archive tier for 130 days.

As we understand, this is a manual approach, and you might end up spending quite some time configuring it for multiple tables in the log analytics workspace.

For automate this process, I’ve created a PowerShell script which can update the total retention period for multiple tables (comma separated) at once. Script is hosted in my GitHub repository: LogAnalyticsTableMgmt/SetTableRetentionAtScale_v1.3.ps1 at main · Abhishek-Sharan/LogAnalyticsTableMgmt

Note: We highly recommend you should review the PowerShell script thoroughly and do proper testing before executing it in production. We don’t take any responsibility for the script.

Posting the 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."
    }
}

Let’s see it in action now

I hope you’ve downloaded the script locally at this point. This an interactive script and it will ask for following details:

Resource Group Name

Log Analytics Workspace Name

Name of tables separated by comma where total retention needs to be updated

Total retention period to set

Enter the details as shown below:

Here we are setting total retention period to 250 days for 5 tables:

AADManagedIdentitySignInLogs
AADNonInteractiveUserSignInLogs
SecurityAlert
SecurityIncident
Usage

Execute the script and the result will be something like this:

Here you can see that Total Retention Period for SecurityAlert table has been updated to 250 days.

Let’s validate in the log analytics workspace blade

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

As we can see, the total retention period for all 5 tables have been updated to 250 days.

Which also means that we’ve configured 90 days of interactive retention and 160 days of archive period (shown below)

Reference Article: Manage data retention in a Log Analytics workspace – Azure Monitor | Microsoft Learn

Configuring total retention period for log analytics workspace tables at scale Microsoft Tech Community – Latest Blogs –Read More

Cart

Cart