Create a pie chart Dashboard in Azure for patch compliance

Question

Hello Team,
I hope you're doing fine,
I want to create a pie dashboard for patch compliance on an Azure workbook,
I'm working with a template, and i have the following chart :

I'm looking for something like this, but in a pie format, here is the kusto query for the above chart :

//It will join with resources table and patchassessmentresources
((resources //join of virtual machines, you can play with params as you see fit.
| where type =~ "microsoft.compute/virtualmachines"
| extend conf = iff(properties.storageProfile.osDisk.osType =~ "windows", properties.osProfile.windowsConfiguration.patchSettings.patchMode, properties.osProfile.linuxConfiguration.patchSettings.patchMode)
| where conf in~ ('AutomaticByOS','AutomaticByPlatform','ImageDefault','Manual')
| extend os = tolower(tostring(properties.storageProfile.osDisk.osType))
| extend id=tolower(id)
| extend status=properties.extended.instanceView.powerState.displayStatus
| project id, name, os, conf, status, resourceProperties=properties)
| union
(resources //union with arc servers, you can play with params as you see fit.
| where type =~ "microsoft.hybridcompute/machines"
| extend id=tolower(id)
| extend os=tolower(coalesce(tostring(properties.osName), tostring(properties.osType)))
| extend status=properties.status
| project id, name, os, status, resourceProperties=properties))
| join kind=leftouter //finally, making a left outer join to fetch updates details from patchassessment
((patchassessmentresources
| where type in~ ("microsoft.compute/virtualmachines/patchassessmentresults", "microsoft.hybridcompute/machines/patchassessmentresults")
| where properties.status == "Succeeded"
| where properties.rebootPending in~ ('true', 'false')
| parse id with resourceId "/patchAssessmentResults" *
| extend resourceId=tolower(resourceId)
| project resourceId, assessProperties=properties))
on $left.id == $right.resourceId //join on resources id & patchassessment id that is parsed.
| summarize
total = countif(1 == 1),
nodata = countif(isnull(assessProperties) == true),
pendingReboot = countif(isnotnull(assessProperties) and assessProperties.rebootPending == "true"),
//pendingUpdates - when any classification has > 0 updates
pendingUpdatesWindows = countif(isnotnull(assessProperties) and assessProperties.osType =~ "Windows" and (assessProperties.availablePatchCountByClassification.critical>0 or assessProperties.availablePatchCountByClassification.security>0)),
pendingUpdatesLinux = countif(isnotnull(assessProperties) and assessProperties.osType =~ "Linux" and (assessProperties.availablePatchCountByClassification.security>0 or assessProperties.availablePatchCountByClassification.other>0)),
//noPendingUpdates - when all classifications has 0 updates
noPendingUpdatesWindows = countif(isnotnull(assessProperties) and assessProperties.osType =~ "Windows" and (assessProperties.availablePatchCountByClassification.critical==0 and assessProperties.availablePatchCountByClassification.security==0)),
noPendingUpdatesLinux = countif(isnotnull(assessProperties) and assessProperties.osType =~ "Linux" and (assessProperties.availablePatchCountByClassification.security==0 and assessProperties.availablePatchCountByClassification.other==0))
| project pendingUpdatesWindows,noPendingUpdatesWindows,pendingUpdatesLinux,noPendingUpdatesLinux

Transforming the result from grid format to pie chart isn't possible (I think the query must be changed), as i'm not comfortable with kusto queries, i couldn't do it
Anyone knows how can i change the above query so that i can have an output in a pie chart? or if you have another query, or other templates that i can use to have this chart, i would be thankfull
Thanks

Answer 1

Did you resolve this? The reason it doesnt work is that a Pie Chart expects two Columns of data, one the name and the other the value, on multiple rows.
In your case you have one row of data and 4 columns as the result of the summarize command. If this was a Logs query rather than ARG you would just use this:

| evaluate pivot()

To pivot the data - however in ARG that isn't supported.