We capture the average memory usage of Azure VM through kql, but the KQL statements of our Windows and Linux are different, so they cannot be generated into a hash table, and finally cannot be imported into a sheet of excel. Is there a way to Combining two hash tables into one
Connect-AzAccount
Set-AzContext -Subscription ""
$workspaceName = ""
$workspaceRG = ""
$WorkspaceID = (Get-AzOperationalInsightsWorkspace -Name $workspaceName -ResourceGroupName $workspaceRG).CustomerID
# vm_windows_memory_begin
$windows_memoryquery = "
Perf
| where TimeGenerated > ago(30min) and ObjectName == 'Memory'
| where CounterName contains 'Available Bytes'
| summarize Free=avg(CounterValue) by Computer,bin(TimeGenerated, 30m)
|join kind= inner
(
Perf
| where ObjectName == 'Memory'
| where CounterName == 'Committed Bytes'
| summarize Used=avg(CounterValue) by Computer,bin(TimeGenerated, 30m)
| summarize arg_max(TimeGenerated, *) by Computer
)
on Computer
| project TimeGenerated, UsedPCT=round(Used * 100 /(Free + Used),2), Computer
| render timechart"
$vm_memorykqlQuery = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $windows_memoryquery
$vm_memorykql = foreach ($a in $vm_memorykqlQuery.Results) {
$props = @{
'Computer' = $a.Computer;
'UsedPCT' = $a.UsedPCT;
}
New-Object -TypeName PSObject -Property $props
}
$WindowsMemory = foreach ($a in $vm_memorykql.Computer | Select-Object -Unique) {
$props = @{
'Hostname' = $a;
'avg_CounterValue' = ("{0:N2}" -f (($vm_memorykql | Where-Object { $_.Computer -eq $a }).UsedPCT | Measure-Object -Average).Average);
}
New-Object -TypeName PSObject -Property $props
}
$WindowsMemory | Export-Excel -Path C:\temp\test.xlsx -WorksheetName WindowsMemory
# vm_windows_memory_end
# vm_linux_memory_begin
$linux_memoryquery = "
Perf
| where TimeGenerated > ago(7d)
| where CounterName == '% Used Memory'
| where ObjectName == 'Memory'
| summarize avg(CounterValue) by bin(TimeGenerated, 30min), Computer
| render timechart"
$linux_memorykqlQuery = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $linux_memoryquery
$linux_memorykql = foreach ($a in $linux_memorykqlQuery.Results) {
$props = @{
'Computer' = $a.Computer;
'avg_CounterValue' = $a.avg_CounterValue;
}
New-Object -TypeName PSObject -Property $props
}
$linux_memory = foreach ($a in ($linux_memorykql.Computer | Select-Object -Unique)) {
$props = @{
'Hostname' = $a;
'avg_CounterValue' = ("{0:N2}" -f (($linux_memorykql | Where-Object { $_.Computer -eq $a }).avg_CounterValue | Measure-Object -Average).Average);
}
New-Object -TypeName PSObject -Property $props
}
$linux_memory | Export-Excel -Path C:\temp\test.xlsx -WorksheetName linux_memory
# vm_linux_memory_end