How to run a purge command in ADX using ADF?

Sailesh Choyal 0

Approach 1

Created an ADX activity in ADF, and in the command field, I wrote the purge command (which I copied from Azure Docs): .purge table MyTable in database DBName allrecords

I tried running the purge command but it keeps returning syntax error: BadRequest_SyntaxError: Request is invalid and cannot be executed

Approach 2

Created a Web Activity in ADF, where I used a Linked Service, and set the following config:

Base URL as the URI of the Kusto cluster: https://<clusterName>.<region>.kusto.windows.net Authentication: System Assigned Managed Identity
Resource: https://<clusterName>.<region>.kusto.windows.net (URI of the Kusto cluster)
Relative URL: v1/rest/mgmt
Method: POST
Body:
{
"db" : "DBName",
"csl" : ".purge table MyTable in database DBName allrecords"
}

But it also returns the same error: BadRequest_SyntaxError: Request is invalid and cannot be executed

The user/role has Admin privielges to the cluster and Purge is enabled from the ADX config.

Note that I'm not using the ingest- endpoint here to run purge because I couldn't get it to work and there isn't info available in the docs as to where to put the ingest- endpoint URL.

Sander van de Velde | MVP 32,736 Reputation points MVP

2024-06-12T22:43:12.84+00:00

Hello @Sailesh Choyal,

welcome to this moderated Azure community forum.

Better questions with more information lead to better answers from our community members.

Please provide information about the steps you have taken, the guide you follow, and the errors you receive so fellow members can understand your question better.

Have you checked the access rights to the ADX database already?
Wilko van de Velde 2,226 Reputation points

2024-06-13T06:07:27.1+00:00

Hi @Sailesh Choyal ,

Did you enable purge on your Azure Data Explorer cluster?

Kind Regards,

Wilko

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".
Sailesh Choyal 0 Reputation points

2024-06-13T07:47:44.52+00:00

Yes it is already enabled
Sailesh Choyal 0 Reputation points

2024-06-13T08:20:19.2233333+00:00

@Sander van de Velde | MVP Thanks, I've added more info. Yes, the role has Admin privileges to the Kusto cluster.
Sander van de Velde | MVP 32,736 Reputation points MVP

2024-06-13T08:37:14.74+00:00

Thanks.

.purge table MyTable in database DBName allrecords

I expect these are placeholders, not actual table and database names?
Sailesh Choyal 0 Reputation points

2024-06-13T09:56:03.02+00:00

Absolutely. These are placeholders.

I tried changing the Base URL from the Kusto URI to the Ingest URI, with everything else the same. And then tried running the same command, but now it gives this permission error:
"Response": "Forbidden: Caller is not authorized to perform this action\r\nError details:\r\nClientRequestId='unspecified;
But the 'app' permissions configured for ADF, in the ADX Security + Networking > Permissons, already has "Cluster AllDatabasesAdmin" Role. So what permission is missing here?

Whereas the .show purges in database DBName" command is working with the ingest- endpoint as base URL.
Sailesh Choyal 0 Reputation points

2024-06-13T10:01:17.0833333+00:00
Yes, absolutely, those are placeholders.

But here's what I tried:

Changed the Base URL in Web Activity to the ingest- endpoint for ADX(it was the ADX Cluster URI earlier).

Ran a .show purges in database DBName command which worked.

Tried the above .purge table MyTable in database DBName allrecords and I get the error : Forbidden: Caller is not authorized to perform this action Error details: ClientRequestId='unspecified; .

Under the Security + Networking > Permissions section in ADX, I see that the app for ADF already has the role 'Cluster AllDatabasesAdmin'. So I'm not sure what permission is missing now.
Wilko van de Velde 2,226 Reputation points

2024-06-13T10:43:57.8033333+00:00

When using a managed identity you need to set the managed identity policy in Azure Data Explorer

See instruction: https://learn.microsoft.com/en-us/azure/data-explorer/ingest-data-managed-identity#set-the-managed-identity-policy-in-azure-data-explorer
PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-06-18T05:50:07.61+00:00

@Sailesh Choyal - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Sailesh Choyal 0

@Wilko van de Velde
I had enabled the Ingestion role (it already had Admin All Access role) for ADF from the Identity config. After that I'm trying to run this command from the Web Activity in ADF (using a POST request).

.alter-merge cluster policy managed_identity "[ { 'ObjectId' : 'ObjectId', 'AllowedUsages' : 'NativeIngestion' }]"

And it throws this error:

InternalServiceError (520-UnknownError): {
    "error": {
        "code": "Internal service error",
        "message": "Request aborted due to an internal service error.",
        "@type": "Kusto.Cloud.Platform.Utils.UtilsInvalidOperationException",
        "@message": "Error assigning managed identity policy. Please verify the managed identity is assigned to your cluster. If so, retry again in a few moments.",

How do I get past this?

PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-06-24T07:29:01.68+00:00
@Sailesh Choyal - The error message suggests that there might be an issue with the managed identity policy assignment. Please verify that the managed identity is assigned to your cluster. If it is, then you can try again in a few moments.

Also, please make sure that you have provided the correct ObjectId in the command. The ObjectId should be the Object ID of the managed identity that you have assigned to your cluster.

You can use the following command to get the Object ID of the managed identity: https://learn.microsoft.com/en-us/cli/azure/identity?view=azure-cli-latest#az-identity-show

az identity show

Once you have the Object ID, you can replace the 'ObjectId' in your command with the actual Object ID of the managed identity.

If the issue persists, please provide more information about your setup and the steps you have followed so that I can assist you better.
Sailesh Choyal 0 Reputation points

2024-06-25T13:35:57.1533333+00:00

@PRADEEPCHEEKATLA-MSFT

The ADF instance has a system-assigned managed identity, which we're using. But isn't az identity only for a user identity? (correct me if I'm wrong). Do we need to use a user identity instead of system assigned?
PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-06-30T14:49:40.8766667+00:00
@Sailesh Choyal - It seems like you are trying to run the .alter-merge cluster policy managed_identity command in ADX using ADF, but it is throwing an InternalServiceError. This error might occur due to various reasons, such as network issues, server issues, or incorrect syntax of the command.

To troubleshoot this issue, you can try the following steps:

Check the syntax of the command: Make sure that you are using the correct syntax for the .alter-merge cluster policy managed_identity command. Here is an example of the correct syntax:

.alter-merge cluster policy managed_identity "[ { 'ObjectId' : '<object-id>', 'AllowedUsages' : 'NativeIngestion' }]"

Check the network connectivity: Make sure that there are no network issues between ADF and ADX. You can try to ping the ADX cluster from the ADF environment to check the network connectivity.

Check the server status: Make sure that the ADX cluster is up and running. You can check the status of the ADX cluster in the Azure portal.

Retry the command: If the above steps do not work, you can try to retry the command after some time. Sometimes, the InternalServiceError might occur due to temporary server issues.

I hope this helps you. Let me know if you have any further questions.
Sailesh Choyal 0 Reputation points

2024-07-01T09:21:08.46+00:00

@PRADEEPCHEEKATLA-MSFT Please refer to my earlier comment it has the exact syntax which I'm using and there is no issue with the syntax here.
Second, on the connectivity side, I'm clearly able to run other commands to that Kusto cluster from ADF, and it is working fine.
Third, yes, the Kusto cluster is up and running, so there is no issue with that as well. I've tried re-running the command multiple times on multiple days. But I get the same error.

Can you please open an internal ticket with Azure and get back with the cause of this?
Sailesh Choyal 0 Reputation points

2024-07-01T09:24:34.17+00:00

@PRADEEPCHEEKATLA-MSFT
Syntax is fine (you can check my earlier comment in this thread where I have used the syntax). The cluster is working fine and connection is working because all the commands apart from this I'm running, are working fine.

So can you please open an internal ticket with Azure and get back on what needs to be done. Because there is not a single resource on the internet which helps resolve this issue, and Azure's documentation doesn't cover it either.
PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-07-02T04:35:22.7066667+00:00

@Sailesh Choyal - We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
Sailesh Choyal 0 Reputation points

2024-07-02T10:01:58.4033333+00:00

Can you please add me to that email thread?
Sailesh Choyal 0 Reputation points

2024-07-11T16:37:01.89+00:00

@PRADEEPCHEEKATLA-MSFT Have you not been able to find a solution from the MSFT team yet? Why is this feature available without proper documentation/demo?
PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-07-12T05:20:12.8433333+00:00

@Sailesh Choyal - We are waiting for internal team to get more information related to your query and will get back to you as soon as we have an update.

Meanwhile, if you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us?

Share via

How to run a purge command in ADX using ADF?

Approach 1

Approach 2

Your answer