Querying Azure activities with subscription names

Question

Hello,

I'm trying to list Azure activities in my Log Analytics workspace, and need the subscription name to be displayed.

With my current KQL request:

AzureActivity
| extend subscriptionId = SubscriptionId
| join kind=inner (
    arg("").ResourceContainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name
  ) on subscriptionId

Azure responds with the following answer: Some aspects of the query had errors so the results are not complete If the issue persists, please open a support ticket. Request id: 23323d56-22e4-490c-b15f-3398a513054f

As I currently only have a basic support plan, I'm asking here...

Answer

Hi @Julien Allaix There are a few things I would change to make this run, but also to be aware of.

Start with getting your subscriptions. This should be on the left side of the join as it will be the smaller data set. I've used a project to only pass the columns we want, but also to rename them for ease and clarity at the same time. As you are using the arg() operator, it will return data based on your read permissions

arg("").ResourceContainers
| where type == "microsoft.resources/subscriptions"
| project SubscriptionName = name, SubscriptionId = subscriptionId

Now get your activity logs. Ensure you put in any relevant filters, such as TimeGenerated, OperationNameValue, ActivityStatusValue etc. This will optimize the query speed.

AzureActivity
| where TimeGenerated >ago(7d)

Now join it all together.

arg("").ResourceContainers
| where type == "microsoft.resources/subscriptions"
| project SubscriptionName = name, SubscriptionId = subscriptionId
| join kind=inner (
    AzureActivity
    | where TimeGenerated >ago(7d)
) on SubscriptionId
| project-away SubscriptionId1

Just be aware of the join flavour, as you have used an inner join, you will only return results for the subscriptions you have read access to, but the workspace can contain data from workspaces that you cannot read. Here I've used a rightouter join to return all activity logs, regardless of what subscriptions I can read, and I've used a little logic to populate the name with the id if it doesn't exist due to my permissions to read it.

arg("").ResourceContainers
| where type == "microsoft.resources/subscriptions"
| project SubscriptionName = name, SubscriptionId = subscriptionId
| join kind=rightouter   (
    AzureActivity
    | where TimeGenerated >ago(7d)
) on SubscriptionId
| extend SubscriptionId = SubscriptionId1
| project-away SubscriptionId1
| extend SubscriptionName = iff(isempty(SubscriptionName), SubscriptionId, SubscriptionName)

Just a little extra, I'm a big fan of using let statements for either side of my joins for clarity, but also for testing

let Subscriptions = 
    arg("").ResourceContainers
    | where type == "microsoft.resources/subscriptions"
    | project SubscriptionName = name, SubscriptionId = subscriptionId
;
let Logs = 
    AzureActivity
    | where TimeGenerated >ago(1d)
    | summarize arg_max(TimeGenerated, * ) by SubscriptionId
;
Subscriptions
| join kind = rightouter (
    Logs
) on SubscriptionId
| extend SubscriptionId = SubscriptionId1
| project-away SubscriptionId1
| extend SubscriptionName = iff(isempty(SubscriptionName), SubscriptionId, SubscriptionName)

Hope this helps

Alistair

Share via

Querying Azure activities with subscription names

1 answer

Your answer