CODEWITHSUNDEEP
azureazure-data-explorerazure-log-analyticskql
TyrannosaurusDave
TyrannosaurusDave

Reputation: 11

Azure OPDG System Aggregation Logs to TimeChart in KQL

I am ingesting Azure OPDG System Aggregation logs from an Azure Monitoring Agent to an Azure Log Analytics Workspace. Each log is collected by timestamp and I am using Custom filters to separate out the CounterMin, CounterMax, CounterAverage and CounterName. The 4 types of CounterName are GatewayCPUPercent, SystemCPUPercent, SystemMEMUsedPercent and GatewayMEMKb. Unfortunately that last value, GatewayMEMKb, is not in a percentage of the Systems Total Memory, so tracking these 4 values on a timechart doesn't work.

I'd like to convert the GatewayMEMKb to a GatewayMEMUsedPercent, and I have the value of the total memory of the server so should be able to do this, but I can't find out how to extract just those values of the CounterAverage column depending on the content of the CounterName column.

I realise this may need to be achieved using a separate column, but can't figure out how to do it.

Initial data:

CounterName CounterAverage
GatewayCPUPercent 15
SystemCPUPercent 45
SystemMEMUsedPercent 30
GatewayMEMKb 327,678
GatewayCPUPercent 14
SystemCPUPercent 48
SystemMEMUsedPercent 31
GatewayMEMKb 356,876

Desired outcome:

CounterName CounterAverage GatewayMEMUsedPercent
GatewayCPUPercent 15
SystemCPUPercent 45
SystemMEMUsedPercent 30
GatewayMEMKb 327,678 2.5
GatewayCPUPercent 14
SystemCPUPercent 48
SystemMEMUsedPercent 31
GatewayMEMKb 356,876 2.8

Upvotes: 1

Views: 37

Answers (1)

Yoni L.
Yoni L.

Reputation: 25995

The following query will transform the input you've provided into the output you've provided.

It uses the iff() function: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ifffunction

let total_memory_bytes = 12.4 * exp2(30);
datatable(CounterName:string, CounterAverage:double)
[
    'GatewayCPUPercent', 15,
    'SystemCPUPercent', 45,
    'SystemMEMUsedPercent', 30,
    'GatewayMEMKb', 327678,
    'GatewayCPUPercent', 14,
    'SystemCPUPercent', 48,
    'SystemMEMUsedPercent', 31,
    'GatewayMEMKb', 356876,
]
| extend GatewayMEMUsedPercent = iff(CounterName == 'GatewayMEMKb', round(100.0 * CounterAverage * 1024/total_memory_bytes, 2), double(null))
CounterName CounterAverage GatewayMEMUsedPercent
GatewayCPUPercent 15
SystemCPUPercent 45
SystemMEMUsedPercent 30
GatewayMEMKb 327678 2.52
GatewayCPUPercent 14
SystemCPUPercent 48
SystemMEMUsedPercent 31
GatewayMEMKb 356876 2.74

Upvotes: 1

Related Questions