Effective Cloud Governance: Leveraging Azure Activity Logs with Power BI
We all generally accept that governance in the cloud is a continuous journey, not a destination. There's no one-size-fits-all solution and depending on the size of your Azure cloud estate, staying on top of things can be challenging even at the best of times. One way of keeping your finger on the pulse is to closely monitor your Azure Activity Log. This log contains a wealth of information ranging from noise to interesting to actionable data. One could set up alerts for delete and update signals however, that can result in a flood of notifications. To address this challenge, you could develop a Power Bi report, similar to this one, that pulls in the Azure Activity Log and allows you to group and summarize data by various dimensions. You still need someone to review the report regularly however consuming the data this way makes it a whole lot easier. This by no means replaces the need for setting up alerts for key signals, however it does give you a great view of what's happened in your environment. If you're interested, this is the KQL query I'm using in Power Bi let start_time = ago(24h); let end_time = now(); AzureActivity | where TimeGenerated > start_time and TimeGenerated < end_time | where OperationNameValue contains 'WRITE' or OperationNameValue contains 'DELETE' | project TimeGenerated, Properties_d.resource, ResourceGroup, OperationNameValue, Authorization_d.scope, Authorization_d.action, Caller, CallerIpAddress, ActivityStatusValue | order by TimeGenerated asc
KQL- in/has-any usage
For the below query, when I use "contains" for single app its works fine but have bulk AppIDs to check, how can i use "in' here? query fails when I replace contains with in or has-any. please help. thank you. let AppIDList = dynamic(["APPID01", "APPID02", "APPID03"]); resources | where type !in~ ("microsoft.compute/snapshots", "microsoft.compute/virtualmachines/extensions") | project subscriptionId, type, resourceGroup, name,AppID = tostring(['tags']['AppID']) //Here AppID is comma sepeated list os AppIDs | where AppID in (AppIDList) | join kind=inner ( resourcecontainers | where ['type'] == "microsoft.resources/subscriptions" | project subscriptionId, name, subname = name ) on $left.subscriptionId == $right.subscriptionId | project subname, subscriptionId, type, resourceGroup, name
KQL Policy Definition ID to displayName and Description
I'm new to KQL and I have a KQL query (CIS Benchmark). Among other things, the query returns me the policyDefinitionId. Unfortunately, this is not readable. How do I do a join so I can retrieve the policy definition displayname and description? Here is the query: PolicyResources | where type =~ 'Microsoft.PolicyInsights/PolicyStates' and properties.policyAssignmentId =~ '/providers/microsoft.management/managementgroups/xxx/providers/microsoft.authorization/policyassignments/8e0161c630a04095a6f38306' |project subscriptionId, properties,id, resource_id=tolower(tostring(properties.resourceId)) | join kind=leftouter (resources | project resource_id=tolower(tostring(id)),resource_name=name) on resource_id | join kind=inner (resourcecontainers | where type == 'microsoft.resources/subscriptions' | project subscriptionId,subscription_contact=tostring(tags.resourcecontact), sbg=tostring(tags.sbg), management_group=tostring(properties.managementGroupAncestorsChain[0].displayName),subscription_name=name)on subscriptionId | project management_group, subscription_name, subscriptionId, subscription_contact, properties.complianceState, properties.policyDefinitionReferenceId, AssignmentID = tostring(id), properties.resourceType, InstanceID = tostring(properties.resourceId), resource_name
KQL question
AzureActivity | summarize LastActivity = max(TimeGenerated) by ResourceProvider, ResourceGroup | join kind = innerunique( AzureActivity | summarize Operations = count() by ResourceGroup, ResourceProvider) on ResourceGroup, ResourceProvider |project ResourceProvider, ResourceGroup, Operations, LastActivity |sort by Operations The above KQL is used to print 4 columns I need to print the fifth column as well that highlights the percentage of operations per Resource Group and Resource provider. There have to 5 columns in the result Resource Provider, Resource Group,Number of Operations (Activities), Last activity time, Percentage Can someone help me with this?
