Log Analytics Alert Filtered Query

Occasional Visitor

I made this query:

 

AzureDevOpsAuditing
| where ActorUPN != "Azure DevOps Service"
| where Area == "Release"
| summarize Count = count()
    by
    OperationName,
    bin(TimeGenerated, 1440min),
    ActorUPN,
    Details,
    IpAddress,
    ScopeDisplayName
| summarize sum(Count)
| where sum_Count > 0

 

And I made an alert that takes that query, evaluates the results of one day and sends an email.

 

But when the filtered results come out it doesnt show the other columns that im looking for (ActorUPN, details ...)

The query:

 

AzureDevOpsAuditing
| where ActorUPN != "Azure DevOps Service"
| where Area == "Release"
| summarize Count = count()
    by
    OperationName,
    bin_at(TimeGenerated, 1440min, datetime(2022-08-09T20:09:14.0000000Z)),
    ActorUPN,
    Details,
    IpAddress,
    ScopeDisplayName
| summarize sum(Count)
| where sum_Count > 0
| extend TimeGenerated = column_ifexists('TimeGenerated', datetime(2022-08-08T20:09:14.0000000Z))
| summarize AggregatedValue = sum(sum_Count) by bin_at(TimeGenerated, 1440m, datetime(2022-08-09T20:09:14.0000000Z))

 

and show TimeGenerated and AggregatedValue, nothing else.

1 Reply

@Nicolas_Greench 

When you do line #12, you essential drop the other columns, so they are not available for any other lines after #12 to process.  

One way is to union the results, it does mean you process the data twice, once to get the count and once for the sum.   See this simple example, as a place to start.

union
(
Usage
| where DataType =="AzureActivity"
| summarize count() by DataType, IsBillable, Solution, Quantity
),
(
  Usage
  | where DataType =="AzureActivity"
  | summarize count() by IsBillable, Solution, Quantity
  | summarize Total = sum(count_), DataType="This is you Total"
) 

 

Clive_Watson_0-1660688020073.png