Purging Table Data from a log Analytics Workspace

I was working on a solution recently that uses Log Analytics to store data so I can easily chart the data changes over time, but on one particular date I got some bad data added and this caused my time chart to looked skewed:

So I looked into how I could remove the data from that particular timepoint in the Log Analytics table. It’s certainly not as straight-forward as deleting data from a database, for example, but it can be done.

Microsoft’s official advice on purging data from Log Analytics can be found here, and there are the expected cautions around doing this – it’s not something you want to be doing too regularly.

The basic process is:

Grant yourself the Data Purger role in the workspace
Send the purge request
Monitor the purge status until completed

As the Data Purger role is ‘highly privileged’ it is recommended to just assign yourself this role temporarily until the purge job is complete. A purge job is not quick to execute – expect to wait several hours or even days as it’s apparently an expensive operation. Real world experience – I issued some purge requests late one afternoon and by next morning they were completed.

In this post, I’ll document different ways to send and monitor a data purge request using PowerShell. We’ll use the Az.OperationalInsights module for this, so get that installed first.

Define parameters

First we need to define some parameters from your Azure environment and tenant:

# Az parameters
$TenantId = "1234567-87c5-40e3-9783-320d0334b3cc"
$Subscription = "MySubscriptionName"
$subscriptionId = "abcdefg-8a8f-4b0c-913b-a08ccd060d9a"
$ResourceGroupName = "MyResourceGroupName"
$WorkspaceName = "MyLAWorkspaceName"
$AccountId = (whoami /upn)

Note: If you’re using an AD domain joined device, you may need to explicitly provide your UPN or email address for the $AccountId parameter instead of using whoami.

Next we need to define some parameters for the purge operation itself. This is where it gets interesting. You need to provide a table as well as a column, operator and value (or values) by which you can filter the specific data you want to purge, eg logically something like this:

TableA, Column2 where Fruit == Bananas

# Purge parameters
$table = "SU_ClientComplianceStatus_CL"
$column = "SummarizationTime_t"
# operator: for datetime, ==,=~,in not working. Can use between,>,>=,<,<=. Between must be an array of two, can be same dates.
$operator = "between" 
# single value
#$value = "2021-12-30T11:01:53Z" 
# multiple values, eg for between,in operators
$values = @("2021-12-30T11:01:53Z","2021-12-30T11:01:53Z")

table: this is the name of the table in the workspace that contains the data you want to purge
column: this is the name of the column in the table you want to use to filter the data to purge
operator: this is one of the supported operator values such as ==, >=, in etc
value: this is the value to search for. It can be a single value or an array of values depending which operator you are using

In my example, I want to delete all data from a table that has a specific timestamp in the SummarizationTime_t column. To achieve this, however, I initially tried to use the == operator with a string of the datetime that I wanted to delete, but this would not work and I kept getting an Unsupported type error message. After trial and error, I found the only way I could get this to work was using the between operator and passing the same date twice in an array, as in my example above.

Authenticate

Alright, next we need to authenticate our user account with Azure (make sure you have the Data Purger role first). Here I’m getting an access token which I can use later in the REST API examples, but I also use it in the Connect-AzAccount cmdlet for the Az.OperationalInsights cmdlets we will use.

# Authenticate
$Token = Get-AzAccessToken -TenantId $TenantId -ErrorAction Stop
If ($Subscription)
{
    $null = Connect-AzAccount -AccessToken $Token.Token -Subscription $Subscription -AccountId $AccountId -ErrorAction Stop 
}
else 
{
    $null = Connect-AzAccount -AccessToken $Token.Token -AccountId $AccountId -ErrorAction Stop 
}

Create the purge request

Using New-AzOperationalInsightsPurgeWorkspace (method 1)

The easiest way to create the purge request is to use the New-AzOperationalInsightsPurgeWorkspace cmdlet. This actually has two parameter sets and I’ll demonstrate both.

The first parameter set takes the table, column, operator and value properties explicitly.

$Params = @{
    ResourceGroupName = $ResourceGroupName
    WorkspaceName = $WorkspaceName 
    Table = $table 
    Column = $column 
    OperatorProperty = $operator 
    Value = $values 
}
$Response = New-AzOperationalInsightsPurgeWorkspace @Params
$operationId = $Response.OperationId

Important: a successful purge request will return an operationId and you need to make a note of it or save it to a variable so you can check the status of the request later.

Using New-AzOperationalInsightsPurgeWorkspace (method 2)

You can also pass a ‘purgebody’ to the cmdlet. It achieves the same thing, but it’s more complicated code:

$purgebodyfilters = [Microsoft.Azure.Management.OperationalInsights.Models.WorkspacePurgeBodyFilters]::new($column,$operator,$values,$null)
[System.Collections.Generic.IList`1[Microsoft.Azure.Management.OperationalInsights.Models.WorkspacePurgeBodyFilters]]$iList = [System.Collections.Generic.List[Microsoft.Azure.Management.OperationalInsights.Models.WorkspacePurgeBodyFilters]]::new()
$iList.Add($purgebodyfilters)
$purgebody = [Microsoft.Azure.Management.OperationalInsights.Models.WorkspacePurgeBody]::new($table,$iList)
try {
    $purgebody.Validate()
}
catch {
    throw $_
}
$Response = New-AzOperationalInsightsPurgeWorkspace -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName -PurgeBody $purgebody
$operationId = $Response.OperationId

Using the REST API (method 3)

You can also call the REST API directly using a POST request and passing the purge request parameters in JSON format. Note there are two version of the JSON body below, the first is for a single value, the second is for passing multiple values in an array, such as I need to do in my example. We can also use the access token we obtained earlier in the headers for this request.

Function Purge-LogAnalyticsData($resourceGroup, $workspaceName, $subscriptionId, $body, $token)
{
    $URI = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.OperationalInsights/workspaces/$workspaceName/purge?api-version=2020-08-01"

    $headers = @{
        "Content-type"="application/json"
        "Authorization" = "Bearer $($token.token)"
    }

    try {
        $response = Invoke-WebRequest -Uri $URI -Method "POST" -Headers $headers -Body $body -UseBasicParsing
    }
    catch {
        $response = $_
    }
    
    return $response
}

# Single value
$body = @"
{
    "table": "$table",
    "filters": [
        {
        "column": "$column",
        "operator": "$operator",
        "value": "$value"
        }
    ]
}
"@

# multiple values
$body = @"
{
    "table": "$table",
    "filters": [
        {
        "column": "$column",
        "operator": "$operator",
        "value": ["$($values -join '","')"]
        }
    ]
}
"@

$Result = Purge-LogAnalyticsData -resourceGroup $ResourceGroupName -workspaceName $WorkspaceName -subscriptionId $subscriptionId -body $body -token $token
If ($Result.StatusCode -eq 202)
{
    $operationId = ($Result.Content | ConvertFrom-Json).operationId
}
else 
{
    $Result
}

Tip 1: Should you get an error when sending the purge request using the Az cmdlet, try the REST option instead as this will return a more detailed error response to help you understand what is failing.

Tip 2: You can check the Activity log on the workspace to see the purge request. You can find the JSON output here as well, along with any error codes if it failed.

Yet another way…

Just for reference, you can also send a purge request from the MS docs page here using the Try it option.

This will open a browser frame where you can sign in, manually provide the required details, send the request and view the response.

Monitor the status of the purge request

Because the data will not be purged immediately, you need to periodically check the status of the purge request. Again, we can do this either with the Az cmdlets or the REST API directly. You need to pass the operationId you obtained earlier from the purge request.

Using Get-AzOperationalInsightsPurgeWorkspaceStatus (method 1)

This is the simplest way. It will return a status ‘pending’ after sending the initial request and ‘completed’ when its done.

$Params = @{
    ResourceGroupName = $ResourceGroupName
    WorkspaceName = $WorkspaceName 
    purgeId = $operationId 
}
Get-AzOperationalInsightsPurgeWorkspaceStatus @params

Using the REST API (method 2)

Function Get-LogAnalyticsDataPurgeStatus($resourceGroup, $workspaceName, $subscriptionId, $operationId, $token)
{
    $URI = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.OperationalInsights/workspaces/$workspaceName/operations/$($operationId)?api-version=2020-08-01"

    $headers = @{
        "Content-type"="application/json"
        "Authorization" = "Bearer $($token.token)"
    }

    try {
        $response = Invoke-WebRequest -Uri $URI -Method "GET" -Headers $headers -UseBasicParsing
    }
    catch {
        $response = $_
    }

    If ($Response.StatusCode -eq 200)
    {
        return $response.Content | ConvertFrom-Json
    }
    else 
    {
        return $response 
    } 
}

$Result = Get-LogAnalyticsDataPurgeStatus -resourceGroup $ResourceGroupName -workspaceName $WorkspaceName -subscriptionId $subscriptionId -operationId $operationId -token $token
$Result