Search code examples
azurekqlazure-log-analytics

How to find list of all resources created in the last month in Azure using KQL and Log analytics?

I want to get a list of all new resources created in my azure subscription in the last month, I have been trying to get it through Log analytics, but I am having problems as to which specific operation I need to pinpoint on for resource creation in Azure. I am confused about what value in the OperationNameValue column should I use in the AzureActivity table.

When I was exploring Azure Monitor and Log analytics, I found that on creating a resource, The OperationNameValue is coming as "Microsoft.Resource/Deployments/Write". Is this the correct OperationNameValue to focus on? Because I cant find the same in the logs when a new storage account was created. Also, in the activity logs, there are some entries where the OperationNameValue is "MICROSOFT.RESOURCES/DEPLOYMENTS/WRITE" and in some entries its "Microsoft.Resource/Deployments/Write". What is the difference between the two?

I'm new to azure with only a few months of experience so I still have quite a few knowledge gaps in this, but I really need to find this out quickly so any insight or help would be greatly appreciated.

Here is the query I have made for reference

AzureActivity
| where TimeGenerated between (startofday(ago(30d)) ..startofday(now()) )
|where OperationNameValue == "MICROSOFT.RESOURCES/DEPLOYMENTS/WRITE"
| parse tolower(\_ResourceId) with "/subscriptions/" subscriptionId "/resourcegroups/"
resourceGroup "/providers/" provider "/" resourceType "/" resourceName
|where ActivityStatusValue == "Success"
|project TimeGenerated,OperationNameValue,ActivityStatusValue,Caller,resourceName,ResourceGroup
|order by TimeGenerated desc
Solution

  • There are some entries where the OperationNameValue is "MICROSOFT.RESOURCES/DEPLOYMENTS/WRITE" and in some entries its "Microsoft.Resource/Deployments/Write". What is the difference between the two?

    There is not much difference between two. Due to case sensitivity of few operations while querying the Azure activity logs it may appear as "Microsoft.Resource/Deployments/Write".

    I tried the same query as you and it retrieved all the results with the Operation Name value as "MICROSOFT.RESOURCES/DEPLOYMENTS/WRITE".

    AzureActivity
|where OperationNameValue == "MICROSOFT.RESOURCES/DEPLOYMENTS/WRITE"
| parse tolower(_ResourceId) with "/subscriptions/" subscriptionId "/resourcegroups/"
resourceGroup "/providers/" provider "/" resourceType "/" resourceName
|where ActivityStatusValue == "Success"
|project TimeGenerated,OperationNameValue,ActivityStatusValue,Caller,resourceName,ResourceGroup
|order by TimeGenerated desc

    enter image description here

    But to deal with this inconsistent behavior, you can use tolower() to change the operation name to lowercase for comparison to avoid any conflicts.

    AzureActivity
|where tolower(OperationNameValue) == "MICROSOFT.RESOURCES/DEPLOYMENTS/WRITE"
| parse tolower(_ResourceId) with "/subscriptions/" subscriptionId "/resourcegroups/"
resourceGroup "/providers/" provider "/" resourceType "/" resourceName
|where ActivityStatusValue == "Success"
|project TimeGenerated,OperationNameValue,ActivityStatusValue,Caller,resourceName,ResourceGroup
|order by TimeGenerated desc

    enter image description here