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?
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- 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, nameRecent Logic Apps Failures with Defender ATP Steps – "TimeGenerated" No Longer Recognized
Hi everyone, I’ve recently encountered an issue with Logic Apps failing on Defender ATP steps. Requests containing the TimeGenerated parameter no longer work—the column seems to be unrecognized. My code hasn’t changed at all, and the same queries run successfully in Defender 365’s Advanced Hunting. For example, this basic KQL query: DeviceLogonEvents | where TimeGenerated >= ago(30d) | where LogonType != "Local" | where DeviceName !contains ".fr" | where DeviceName !contains "shared-" | where DeviceName !contains "gdc-" | where DeviceName !contains "mon-" | distinct DeviceName Now throws the error: Failed to resolve column or scalar expression named 'TimeGenerated'. Fix semantic errors in your query. Removing TimeGenerated makes the query work again, but this isn’t a viable solution. Notably, the identical query still functions in Defender 365’s Advanced Hunting UI. This issue started affecting a Logic App that runs weekly—it worked on May 11th but failed on May 18th. Questions: Has there been a recent schema change or deprecation of TimeGenerated in Defender ATP's KQL for Logic Apps? Is there an alternative column or syntax we should use now? Are others experiencing this? Any insights or workarounds would be greatly appreciated!
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
