Log Analytics Query Language


The new and improved Azure Log Analytics announced recently provides a powerful query language with built-in Smart Analytics.

To make the best use of the enhancements, we have provided few queries to make sense of your assessments data using the new query language.

Try the new query language:

  1. Ramp-Up in 5 minutes with our query language cheat sheet.
  2. Visit Getting Started with Queries to learn how to write new queries.
  3. Use the Query Language Reference for details on functions, operators, and types.
  4. Check out our tutorials on Working with String, and Date and Time Operations to learn about data types.
  5. Use aggregations to get insights on your data.

All New Query Language queries can be tried in the Demo Portal.


Relevant Queries:

1. Getting the Raw Recommendation Data for a particular Assessment

SQLAssessmentRecommendation
Try It


2. Get Assessment Log Data for a Particular Computer

Operation | where Computer == "ContosoADDS1.ContosoRetail.com" |
      summarize arg_max(TimeGenerated, *) by OperationCategory, Solution, Detail

Try It

Note: arg_max is used to return the latest data. Details about how arg_max works are here.



3. Check the Status of a Solution for Various Operation Categories on All Computers
Check whether Operations such as Assessment Target Check, .NET Check, Is
      Local Administrator Check etc. were successful or not.
 Operation | where Solution =="SQLAssessment" | summarize
    arg_max(TimeGenerated, *) by Computer, OperationCategory | sort by
    TimeGenerated desc , OperationStatus asc
Try It



4. For an Assessment, get all Affected Objects and when they were Accessed
SQLAssessmentRecommendation | summarize AggregatedValue =
      max(TimeGenerated) by AffectedObjectName | sort by AggregatedValue desc

Try It



  1. Check for Recommendation Data Available for an Assessment or Not
SQLAssessmentRecommendation | summarize Result = count() > 0(returns a
    true or false value based on whether data is available or not)

Try It



  1. To Get Details for a Particular Recommendation Id that Failed
    SQLAssessmentRecommendation | where RecommendationId
        =="7821eda2-c420-4920-bc0c-ca8cd1d48482" and RecommendationResult=="Failed"
    

    Try It



  2. Get Prioritized list of Failed Recommendations

    This lists the failed recommendations for unique combinations of RecommendationId and AffectedObjectUniqueName for the latest run of an Assessment.

ADAssessmentRecommendation | summarize arg_max(TimeGenerated, *) by
      RecommendationId, AffectedObjectUniqueName |where RecommendationResult ==
      "Failed" | sort by RecommendationScore desc, TimeGenerated desc

Refer use of arg_max as mentioned in query 2 above.

Try It



8. Get Details Related to Recommendations that Failed for a Particular Affected Object

SQLAssessmentRecommendation | where AffectedObjectName ==
      "ContosoMABSVM1.CONTOSORETAIL.COM" | summarize arg_max(TimeGenerated, *)
      by RecommendationId | where RecommendationResult == "Failed"
Try It



For general feedback on the Resource Center or content, please submit your response to UserVoice. For specific requests and content updates regarding the Services Hub, contact our Support Team to submit a case.