Leverage Log Analytics to Optimize Synapse Analytics Notebook Usage

vikranth-0706 200 Reputation points
2024-06-11T12:38:18.39+00:00

I want to analyze Synapse Analytics notebook execution data in Log Analytics. Specifically, I'm interested in understanding notebook usage patterns. Can I use a built-in Log Analytics table with KQL to find metrics like:

Who runs the most notebooks?

How long do notebooks typically take to execute?

Are there any other relevant metrics captured for notebook runs?

By default, is there a table containing this information?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,658 questions
0 comments No comments
{count} votes

Accepted answer
  1. Smaran Thoomu 12,355 Reputation points Microsoft Vendor
    2024-06-11T13:31:11.9833333+00:00

    Hi @vikranth-0706

    Thanks for the question and using MS Q&A platform.

    After looking into your issue, I found that there isn't a default table for Synapse Analytics notebook execution metrics in Log Analytics.

    Here's a workaround you can use to get the logs:

    1. Add diagnostic settings to your Synapse workspace to send logs to Log Analytics. enter image description here
    2. Once configured, you can query custom logs using the following sample query in the
         AzureSynapseAnalyticsWorkspaces table:
         AzureSynapseAnalyticsWorkspaces 
         | where OperationName == "<required operation>" 
         | summarize count() by Caller 
         | top 20 by count_
         
         
      
    3. Go to the logs section, then to Synapse workspace logs, and use a KQL query to search the AzureSynapseAnalyticsWorkspacesNotebookLogs.

    Hope this helps. Do let us know if you any further queries.


    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Vinodh247 13,066 Reputation points
    2024-06-11T13:47:23.4633333+00:00

    Hi vikranth-0706,

    Thanks for reaching out to Microsoft Q&A.

    Synapse can integrate with azure monitor and log analytics to collect and analyze the metrics and logs. The relevant data about notebook execution is typically captured in the SynapseAnalyticsOperationalLogs table within log analytics. Once the data is collected into this table you can use KQL to query the required details.

    https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-azure-log-analytics

    For example:

    • Time taken for the notebook to complete the execution:
    SynapseAnalyticsOperationalLogs
    | where OperationName == "NotebookExecution"
    | extend StartTime = todatetime(parse_json(parse_json(Properties).startTime))
    | extend EndTime = todatetime(parse_json(parse_json(Properties).endTime))
    | extend Duration = datetime_diff('second', EndTime, StartTime)
    | summarize AvgDuration = avg(Duration), MinDuration = min(Duration), MaxDuration = max(Duration) by NotebookName
    | order by AvgDuration desc
    
    
    • Whch user runs the most number of notebook executions:
    SynapseAnalyticsOperationalLogs
    | where OperationName == "NotebookExecution"
    | extend StartTime = todatetime(parse_json(parse_json(Properties).startTime))
    | extend EndTime = todatetime(parse_json(parse_json(Properties).endTime))
    | extend Duration = datetime_diff('second', EndTime, StartTime)
    | summarize AvgDuration = avg(Duration), MinDuration = min(Duration), MaxDuration = max(Duration) by NotebookName
    | order by AvgDuration desc
    
    

    The SynapseAnalyticsOperationalLogs table captures several other metrics useful for analyzing notebook execution. These queries can be further customized based on your requirements and the structure of your log data.

    Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

    0 comments No comments