Subject : Log analytics workspace - add CPU + Memory usage per each query on analysis

%3CLINGO-SUB%20id%3D%22lingo-sub-1768728%22%20slang%3D%22en-US%22%3ESubject%20%3A%20Log%20analytics%20workspace%20-%20add%20CPU%20%2B%20Memory%20usage%20per%20each%20query%20on%20analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768728%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%2C%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20goal%20is%20to%20retrieve%20from%20log%26nbsp%3Banalytics%26nbsp%3B%20all%20queries%20being%20fired%20against%20analysis%20server.%3C%2FP%3E%3CP%3Eso%20that%20is%20being%20done%20with%20the%20following%20rows%26nbsp%3B%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBUT%20my%20question%20is%20-%20how%20can%20i%20add%20per%20each%20query%20that%20is%20being%20fired%20-%20it's%20CPU%20and%20memory%20usage%20%3F%3C%2FP%3E%3CP%3EPlease%20advise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20window%20%3D%20AzureDiagnostics%0A%20%20%20%7C%20where%20ResourceProvider%20%3D%3D%20%22MICROSOFT.ANALYSISSERVICES%22%20and%20Resource%20%3D~%20%22Server%20Name%22%3B%0Awindow%0A%7C%20where%20OperationName%20has%20%22QueryEnd%22%20or%20(OperationName%20has%20%22CommandEnd%22%20and%20EventSubclass_s%20%3D%3D%2038)%0A%7C%20where%20EffectiveUsername_s%20!%3D%20%22NT%20AUTHORITY%5C%5CSYSTEM%22%20%0A%7C%20where%20toint(Duration_s)%20%26gt%3B%20toint(5000)%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(2d)%0A%7C%20project%20%20EventSubclass_s%2CTimeGenerated%2CStartTime_t%2CEndTime_t%2CServerName_s%2COperationName%2CRootActivityId_g%2CTextData_s%2CDatabaseName_s%2CApplicationName_s%2CDuration_s%2CEffectiveUsername_s%2CUser_s%0A%7C%20order%20by%20StartTime_t%20asc%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello , 

my goal is to retrieve from log analytics  all queries being fired against analysis server.

so that is being done with the following rows  below.

 

BUT my question is - how can i add per each query that is being fired - it's CPU and memory usage ?

Please advise.

 

 

let window = AzureDiagnostics
   | where ResourceProvider == "MICROSOFT.ANALYSISSERVICES" and Resource =~ "Server Name";
window
| where OperationName has "QueryEnd" or (OperationName has "CommandEnd" and EventSubclass_s == 38)
| where EffectiveUsername_s != "NT AUTHORITY\\SYSTEM" 
| where toint(Duration_s) > toint(5000)
| where TimeGenerated > ago(2d)
| project  EventSubclass_s,TimeGenerated,StartTime_t,EndTime_t,ServerName_s,OperationName,RootActivityId_g,TextData_s,DatabaseName_s,ApplicationName_s,Duration_s,EffectiveUsername_s,User_s
| order by StartTime_t asc

 

 

 

1 Reply
You're right! You won't be able to get this info from Log Analytics. What you need to do is to use Azure SQL diagnostic settings to tell you what are the most running queries and other diagnostic info.

Hope this helps!