How to write the query on Azure to get the information of particular SPN linked with all the folders present in containers..

Saurabh Parmar 0

Hi,

As there are multiple folders are preset into Azure storage containers, each folders of containers linked with some SPN. I am looking for the script to create to find, which SPN associate with which all folders?

Nandamuri Pranay Teja 330 Reputation points Microsoft Vendor

2025-01-13T16:39:49.3366667+00:00

Hello Saurabh Parmar

Welcome to Microsoft Q&A Forum. Thanks for posting your query here!

We are checking internally with our team on this case once we get an update we will reach you back with the response as soon as possible.

Thank you for your patience
Nandamuri Pranay Teja 330 Reputation points Microsoft Vendor

2025-01-16T07:04:52.8+00:00

Hello Saurabh Parmar

Just checking to see that the above suggestion helped you to solve your query. If you have any further queries, please let us know we are here at your service!

Awaiting for your response.
Saurabh Parmar 0 Reputation points

2025-01-20T12:56:28.0133333+00:00

Hi @Nandamuri Pranay Teja :

Thanks a lot for your suggestion. Would you please provide one sample query to get some information from "Azure portal-> Storage account -> data storage->container ->sub folders", which can be run from "Monitoring-> logs -> Query"?
Nandamuri Pranay Teja 330 Reputation points Microsoft Vendor

2025-01-20T15:15:31.2066667+00:00

Hello Saurabh Parmar,

Thank you for the kind response!

Sure, here's an example query that you can use to get information from a subfolder in an Azure Storage account container.

// Replace <StorageAccountName>, <ContainerName>, and <SubfolderName> with your own values AzureDiagnostics | where ResourceType == "CONTAINER" | where Resource == "/subscriptions/<SubscriptionID>/resourceGroups/<ResourceGroupName>/providers/Microsoft.Storage/storageAccounts/<StorageAccountName>/blobServices/default/containers/<ContainerName>/<SubfolderName>" | where TimeGenerated > ago(1d) | project TimeGenerated, Level, Message

This query retrieves logs from the Azure Diagnostics table for a specific subfolder in a container. You will need to replace <StorageAccountName>, <ContainerName>, <SubfolderName>, <SubscriptionID>, and <ResourceGroupName> with your own values.

The where ResourceType == "CONTAINER" clause filters the logs to only include container resources. The where Resource == ... clause filters the logs to only include the specific subfolder that you are interested in. The where TimeGenerated > ago(1d) clause filters the logs to only include logs generated within the last day. Finally, the project operator selects the TimeGenerated, Level, and Message columns to display in the query results.

Please be informed that this KQL query retrieves logs related to read, write, or other operations performed on blobs within a specific subfolder of an Azure Storage container, focusing on logs generated in the last day and extracting the timestamp, log level, and message details.

I hope this helps!

Let me know if you have any further questions we are here at your service.

If my previous comment answers your question, please Accept the answer or Upvote for the same which helped you. Which might be beneficial to other community members reading this thread.
Nandamuri Pranay Teja 330 Reputation points Microsoft Vendor

2025-01-21T13:07:00.4733333+00:00

Hello Saurabh Parmar,

I apologize for the interruption, but I would like to inquire whether you have had the chance to review the above provided answer to resolve your query. If this response addresses your question, please consider clicking "Accept Answer" and selecting "Yes" to indicate that this answer was helpful, as it may also benefit other community members who are following this discussion.

Let us know if you have any questions or concerns, we are here at your service!

Awaiting for your response!
Saurabh Parmar 0 Reputation points

2025-01-21T16:58:26.14+00:00

Thanks @Nandamuri Pranay Teja :

For me issue is, AzureDiagnostics table is not available.

It shows -> Error: The name 'AzureDiagnostics' does not refer to any known column, table, variable or function.

May I know how to enable/activite it?
Nandamuri Pranay Teja 330 Reputation points Microsoft Vendor

2025-01-22T11:18:17.61+00:00
Hello Saurabh Parmar,

Thank you for the kind response!

If the Azure Diagnostics table is absent, it may indicate that diagnostics logging has not been activated for your storage account.

To enable diagnostics logging for your storage account, you can follow these steps:

Go to the Azure portal and navigate to your storage account.

Click on "Monitoring" in the left-hand menu.

Click on "Diagnostic settings" in the Monitoring menu.

Click on "Add diagnostic setting" to create a new diagnostic setting.

Give the diagnostic setting a name and select the storage account that you want to enable diagnostics logging for.

Under "Logs", select the "StorageLogs" category and choose the retention period that you want to use.

Click "Save" to create the diagnostic setting.

Once you have enabled diagnostics logging for your storage account, you should be able to query the Azure Diagnostics table in the Log Analytics workspace.

Additional information:

If you are still unable to query the Azure Diagnostics table after enabling diagnostics logging, it is possible that there may be other issues with your Log Analytics workspace or storage account configuration.

Documentation on Diagnostic settings: https://learn.microsoft.com/en-us/azure/azure-monitor/essentials/diagnostic-settings

Let me know if you have questions or concerns, we are here at your service.
Nandamuri Pranay Teja 330 Reputation points Microsoft Vendor

2025-01-23T08:59:40.71+00:00

Hello Saurabh Parmar,

Just checking to see that the above suggestion helped you to solve your query. Let me know if you have any questions or concerns, we are here at your service.
Nandamuri Pranay Teja 330 Reputation points Microsoft Vendor

2025-01-24T05:26:09.4433333+00:00

Hello Saurabh Parmar,

I apologize for the interruption, but I would like to inquire whether you have had the chance to review the above provided answer to resolve your query.

Let us know if you have any questions or concerns, we are here at your service!

Awaiting for your response!

1 answer

Nandamuri Pranay Teja 330 Reputation points Microsoft Vendor

2025-01-15T07:39:19.63+00:00
Hello Saurabh Parmar

Welcome to Microsoft Q&A Forum. Thanks for posting your query here!

Apologise for the delay in response.

I understand that you would like to know how to write the query on Azure to get the information of particular SPN linked with all the folders present in containers.

Please be informed that Azure Blob Storage itself doesn't inherently store access control information at the folder level. Permissions are typically assigned at the container level or through inheritance from a parent container. Here's an approach to identify the SPN associated with each container in your Azure Blob Storage account.

However please find below Identify Container Access Policies

Go to the Azure portal and navigate to your storage account.

Select the container you're interested in.

Under "Settings," click on "Access control (IAM).

This will show you the list of Shared Access Signatures (SAS) or Azure Active Directory (AAD) identities (like SPNs) that have access to the container and their permission levels.

Post which Write a script (Python, PowerShell, etc.) that iterates through all your containers in the storage account. For each container, use the methods mentioned above to retrieve the access control information (SAS or AAD identities). Store the container name and associated SPN (if applicable) in a data structure (list, dictionary) for further processing or analysis.

Additional information:

If you're using Azure Data Lake Storage Gen2 with hierarchical namespace, you might need to adapt the script to navigate the directory structure and retrieve access control information for individual folders. Tools like Azure Data Explorer or the Azure SDKs might be helpful for working with hierarchical data. The script will need appropriate permissions to access container access control information. You can use an Azure AD application (with the necessary Azure roles) or a storage account access key to authenticate your script.

By following the above steps and adapting the script to your specific needs, you can retrieve the information about the SPN associated with each container in your Azure Blob Storage account.

NOTE: Remember that this approach won't directly provide folder-level SPN associations unless you're using Azure Data Lake Storage Gen2 with hierarchical namespace.

Let me know if you have any further quarries we are here at your service!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to write the query on Azure to get the information of particular SPN linked with all the folders present in containers..

1 answer

Your answer