Percentage of Arc Machines by Domain

%3CLINGO-SUB%20id%3D%22lingo-sub-2587153%22%20slang%3D%22en-US%22%3EPercentage%20of%20Arc%20Machines%20by%20Domain%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2587153%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20Azure%20Resource%20Graph%20to%20get%203%20columns%20of%20data%3A%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EList%20of%20Domain%20names%3C%2FLI%3E%3CLI%3ECount%20of%20Arc%20Machines%20by%20Domain%3C%2FLI%3E%3CLI%3EPercentage%20of%20Arc%20Machines%20by%20Domain%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eresources%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btype%26nbsp%3B%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22microsoft.hybridcompute%2Fmachines%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BDomain%3Dproperties%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3EdomainName%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3EArcMachines%3Dname%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTotalResources%3D%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BMachinesByDomain%3D%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EArcMachines%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EDomain%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BPercentage%26nbsp%3B%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btoreal%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Esum%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EMachinesByDomain%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%2F%26nbsp%3Btoreal%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3Esum%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ETotalResources%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B*%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E100%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BDomain%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3EMachinesByDomain%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3EPercentage%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3EThis%20is%20the%20result%20I%20am%20getting%3A%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%20class%3D%22ext-hubs-argquery-resultmessage%22%3E%22Please%20provide%20below%20info%20when%20asking%20for%20support%3A%20timestamp%20%3D%202021-07-26T14%3A38%3A35.7800277Z%2C%20correlationId%20%3D%20d2b015fa-2584-4f11-99c4-eb0e4f1a5096.%20(Code%3ABadRequest)%3C%2FDIV%3E%3CDIV%20class%3D%22ext-hubs-argquery-resultmessage%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22ext-hubs-argquery-resultmessage%22%3EDetails%3A%20Query%20is%20invalid.%20Please%20refer%20to%20the%20documentation%20for%20the%20Azure%20Resource%20Graph%20service%20and%20fix%20the%20error%20before%20retrying.%20(Code%3AInvalidQuery)%20'summarize'%20operator%3A%20Failed%20to%20resolve%20scalar%20expression%20named%20'TotalResources'%20(Code%3AOperator_FailedToResolveEntity)%22%3C%2FDIV%3E%3CDIV%20class%3D%22ext-hubs-argquery-resultmessage%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22ext-hubs-argquery-resultmessage%22%3EPlease%20let%20me%20know%20if%20you%20have%20any%20suggestions%20to%20achieve%20the%20results%20I%20am%20looking%20for.%20Thank%20you!%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to use Azure Resource Graph to get 3 columns of data: 

  • List of Domain names
  • Count of Arc Machines by Domain
  • Percentage of Arc Machines by Domain

 

resources

where type == "microsoft.hybridcompute/machines"
extend Domain=properties.domainName,ArcMachines=name
summarize TotalResources=count()
summarize MachinesByDomain=count(ArcMachines) by tostring(Domain)
summarize Percentage = ( toreal(sum(MachinesByDomain)) / toreal(sum(TotalResources)) ) * 100
project Domain,MachinesByDomain,Percentage
 
 
This is the result I am getting:
"Please provide below info when asking for support: timestamp = 2021-07-26T14:38:35.7800277Z, correlationId = d2b015fa-2584-4f11-99c4-eb0e4f1a5096. (Code:BadRequest)
 
Details: Query is invalid. Please refer to the documentation for the Azure Resource Graph service and fix the error before retrying. (Code:InvalidQuery) 'summarize' operator: Failed to resolve scalar expression named 'TotalResources' (Code:Operator_FailedToResolveEntity)"
 
Please let me know if you have any suggestions to achieve the results I am looking for. Thank you!
1 Reply

@Josh_Washburn 

 

One way to do this is to use a join 

// get the Total count, store it and create a column to join on
resources
| where type == "microsoft.hybridcompute/machines"
| summarize Total_ = dcount(name), fake_join = "join_" 
// Join data on the fake column name = left
| join  //right
(
	resources
	| where type == "microsoft.hybridcompute/machines"
	| extend Domain=properties.domainName,ArcMachines=name
	| summarize MachinesByDomain=count(ArcMachines)  by tostring(Domain), ArcMachines, fake_join ="join_"
) on fake_join
// now calculate the Total from left with the data from the right  
| summarize MachinesByDomain=count(ArcMachines), Percentage = ( toreal(sum(MachinesByDomain)) / toreal(max(Total_)) * 100 ) by tostring(Domain)
| order by MachinesByDomain desc

 

Screenshot 2021-07-27 102156.png