Contact Us
  • Authored Article

Bulk update the total retention of all the Sentinel table: From tedious manual steps to seamless bulk updates

Automate Total Log Retention of all tables in Microsoft Sentinel: From Tedious Manual Steps to Seamless Bulk Updates

In today’s cloud-first security landscape, operational efficiency is just as critical as threat intelligence. According to Forrester’s “State of Security Operations 2024,” nearly 65% of security teams cite time-consuming manual processes as a top roadblock to effective threat response. When compliance and security policies demand multi-year log retention, the task of updating hundreds of Sentinel tables individually can drag skilled analysts into hours of repetitive, error-prone work. This blog introduces a PowerShell script designed to automate this process, freeing up valuable time and resources.

Reality Check for Security Operations

  • Is your SOC spending hours each quarter just setting total data retention policies of new tables or new table setup?
  • Do auditors and compliance teams demand rapid, consistent evidence of log retention across all your workspaces?
  • Ever worry that a missed click or oversight could lead to gaps in your data storage policy?

If any of these concerns hit home, it’s time to rethink your approach.

The Manual Retention Configuration Challenge

  • As regulatory expectations rise and cloud data grows, you might find yourself with 200+ analytic and custom tables in Microsoft Sentinel — each requiring a configured “total retention” period. Doing this by hand in the Azure Portal can:

    • Consume precious staff hours
    • Lead to inconsistent results
    • Cause “change fatigue” in your security team

    A large global bank recently estimated over 15 hours annually wasted on manual retention updates — just in a single workspace.

Automate, Don’t Agonize: Introducing the Bulk Retention PowerShell Script

To empower your SOC and eliminate this manual burden, I’ve developed a robust, interactive PowerShell script. This script streamlines the process of updating total log retention of all the tables across your Microsoft Sentinel environment.

  • Input your Azure subscription, resource group, Log Analytics workspace, and desired retention period — and the script will apply settings across all tables.
  • Skip specific tables—want to retain only certain logs longer (or shorter)? Just supply their names and they’re automatically excluded.
  • Token refresh logic ensures reliability — even for large environments, the script refreshes your Azure credentials in the background, preventing expiry errors.

Key Features & Benefits

  • Bulk Automation: Update hundreds of tables in minutes, not hours.
  • Zero Guesswork: Prompts for all needed information so no edits required.
  • Flexible Exclusion: Easily skip system or custom tables when needed.
  • Secure & Robust: Handles Azure authentication, token refresh, and error messaging.

User-Friendly Output: See exactly which tables were updated, skipped, or failed — for immediate operational clarity.

How It Works: Automated Total Retention of all tables in Action

  • Step 1: Login to Azure Log in with Azure PowerShell for secure authentication and upload the script.
  • Step 2: Run & Provide Details:
  • Step 3: Customize Choose whether to skip any tables (simply enter a comma-separated list).
  • Step 4: Push The script gathers your table list, loops through each, and sets retention — handling token refreshes automatically.
  • Step 5: Review Results Success, skip, and error messages are color-coded and summarized in real-time.

Why This Matters

By blending automation with flexible policy controls, you ensure:

  • Governance: Compliant retention across your entire Sentinel estate.
  • Efficiency: Free up your engineers and analysts for threat hunting, not repetitive clicks.

Reliability: Consistent settings and robust error handling reduce risk of human oversight or missed updates.

Conclusion: Let Your Security Team Focus on What Matters

Manual retention management of all tables is a thing of the past. With this script, you can keep your Microsoft Sentinel deployment compliant, efficient, and reliably configured — with just a few prompts and no risk of missed tables. Empower your analysts to do what humans do best: investigate, respond, and defend — while automation takes care of the heavy lifting.

Ready to transform your Sentinel operations?

Try this script in your next total retention update of all tables and experience the time savings firsthand. 
<#
.TITLE
    Bulk update the Log Analytics table total retention setting.
 
.DESCRIPTION
    This script allows you to bulk-apply a total retention period (in days)
    across all tables in a Log Analytics workspace. 
    You can optionally exclude specific tables by entering their names.
 
.REQUIREMENTS
    – Azure Powershell from portal.azure.com/
    – One should have LAW contributor permission and other Sufficient permissions on the target subscription/workspace
 
.AUTHOR
    Name: Pranjal Verma
Mail: pranjal.verma117@gmail.com or pranjal.verma@inspiraenterprise.com
Company: Inspira Enterprise
#>
 
# ———– User login ———–
az login –use-device-code | Out-Null
 
# ———– Gather inputs ———–
$subscriptionId = Read-Host “Enter your Subscription ID”
$resourceGroup  = Read-Host “Enter your Resource Group Name”
$workspaceName  = Read-Host “Enter your Log Analytics Workspace Name”
$retentionDays  = Read-Host “Enter total retention in days” 
 
# Validation
if (-not ($retentionDays -as [int])) {
    Write-Host “⚠ Retention days must be a valid integer. Exiting.” -ForegroundColor Red
    exit
}
 
# Exclusion option
$excludeChoice = Read-Host “Do you want to exclude specific tables? (yes/no)”
$excludeTables = @()
if ($excludeChoice -eq “yes”) {
    $excludeInput = Read-Host “Enter table names to exclude (comma-separated)”
    $excludeTables = $excludeInput.Split(“,”) | ForEach-Object { $_.Trim() } | Where-Object { $_ -ne “” }
    if ($excludeTables.Count -gt 0) {
        Write-Host “⚠ The following tables will be excluded: $($excludeTables -join ‘, ‘)” -ForegroundColor Yellow
    }
}
 
# ———– Set subscription context ———–
az account set –subscription $subscriptionId
 
# ———– Acquire initial token and initialize token refresh tracker ———–
Write-Host “Requesting Azure access token…” -ForegroundColor Cyan
$token = az account get-access-token –resource https://management.azure.com/ –query accessToken -o tsv
$tokenLastRefreshed = Get-Date
$refreshIntervalMinutes = 30
 
$headers = @{
    Authorization = “Bearer $token”
    “Content-Type” = “application/json”
}
 
$apiVersion = “2021-12-01-preview”
 
# ———– Fetch tables ———–
Write-Host “Fetching tables from workspace ‘$workspaceName’…” -ForegroundColor Cyan
$tablesUrl = “https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.OperationalInsights/workspaces/$workspaceName/tables?api-version=$apiVersion”
 
try {
    $tablesResponse = Invoke-RestMethod -Method Get -Uri $tablesUrl -Headers $headers
    $tables = $tablesResponse.value
    $tableCount = if ($tables) { $tables.Count } else { 0 }
}
catch {
    Write-Host “❌ Failed to fetch tables. Please check your inputs and permissions.” -ForegroundColor Red
    Write-Host $_.Exception.Message
    exit
}
 
Write-Host “$tableCount tables found in workspace ‘$workspaceName’.” -ForegroundColor Green
Write-Host “Applying total retention of $retentionDays days…” -ForegroundColor Cyan
 
# ———– Loop through and update tables, refreshing token every 30 minutes ———–
foreach ($table in $tables) {
    $tableName = $table.name
 
    # Exclusion check
    if ($excludeTables -contains $tableName) {
        Write-Host “⚠ Skipping excluded table ‘$tableName'” -ForegroundColor DarkYellow
        continue
    }
 
    # Refresh token if 30 minutes elapsed since last refresh
    $elapsedMinutes = (Get-Date) – $tokenLastRefreshed
    if ($elapsedMinutes.TotalMinutes -ge $refreshIntervalMinutes) {
        $token = az account get-access-token –resource https://management.azure.com/ –query accessToken -o tsv
        $headers.Authorization = “Bearer $token”
        $tokenLastRefreshed = Get-Date
        Write-Host “ℹ️ Access token refreshed at $(Get-Date -Format ‘HH:mm:ss’)” -ForegroundColor Cyan
    }
 
    # Build API URL with version
    $baseUrl    = “https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.OperationalInsights/workspaces/$workspaceName/tables/$tableName”
    $uriBuilder = [System.UriBuilder]$baseUrl
    $uriBuilder.Query = “api-version=$apiVersion”
    $updateUrl = $uriBuilder.Uri.AbsoluteUri
 
    # JSON body
    $body = @{
        properties = @{
            retentionInDays      = $null
            totalRetentionInDays = [int]$retentionDays
        }
    } | ConvertTo-Json -Depth 3 -Compress
 
    try {
        Invoke-RestMethod -Method Put -Uri $updateUrl -Headers $headers -Body $body | Out-Null
        Write-Host “✔ Total retention set for table ‘$tableName’ to $retentionDays days” -ForegroundColor Green
    }
    catch {
        Write-Host “❌ Could not update table ‘$tableName’.” -ForegroundColor Red
        if ($_.ErrorDetails) {
            $err = ($_.ErrorDetails | ConvertFrom-Json -ErrorAction SilentlyContinue)
            if ($err.error.message) {
                Write-Host ”   Reason: $($err.error.message)” -ForegroundColor DarkRed
            } else {
                Write-Host ”   Details: $($_.ErrorDetails)” -ForegroundColor DarkRed
            }
        } else {
            Write-Host ”   Exception: $($_.Exception.Message)” -ForegroundColor DarkRed
        }
    }
}
 
Write-Host “`n✅ Retention update attempt completed for all tables in workspace ‘$workspaceName’.” -ForegroundColor Cyan
Bulk update the total retention of all the Sentinel table: From tedious manual steps to seamless bulk updates

By: Pranjal Verma, Senior Technology Consultant, Microsoft Security, Inspira Enterprise

Recent Posts

Our Top Services:

CYBERSECURITY SERVICES

Top CyberSecurity Services | Secure your business with Inspira Enterprise’s expert CyberSecurity services.

DATA ANALYTICS SERVICES

Expert Data Analytics Services | Harness the power of data with Inspira Enterprise’s advanced Data Analytics services.

DIGITAL TRANSFORMATION SERVICES

Transform Your Business with Digital Transformation Services | Accelerate your business growth with Inspira Enterprise’s  Digital Transformation services.

Our Top Offerings:

Managed security services

Reliable Managed Security Services | Enhance your business security with Inspira Enterprise’s Managed Security Services.

Identity & access management

Secure Identity & Access Management Services | Ensure robust security and compliance with Inspira Enterprise’s Identity & Access Management services.

Third party risk management

Comprehensive Third Party Risk Management Services | Mitigate risks and ensure compliance with Inspira Enterprise’s  Third Party Risk Management services.