Forum Discussion

MarkSteben's avatar
MarkSteben
Copper Contributor
Jun 15, 2020

azure portal logging - no results found

Hello - I am trying to access the azure logging facility for my servers for the first time.  Within the database I wish to post logs to and read logs from I have set up 'diagnostic settings'. Within diagnostic settings I have initialized storage accounts and log analytics.  But so far When I go into the 'logs' facility within that database I see 'No results found' for any of the sample queries set up in log analytics.  It seems that nothing is being written to the logs yet. Is there something else I missed that I need to do?  We have created errors and run queries so I know there is activity on that database.  Any help appreciated.  Thank you.

  • MarkSteben's avatar
    MarkSteben
    Copper Contributor

    MarkSteben

    Hello,  I am still trying to get log information from some of the sample queries in log analytics.  Here is what I have done so far:

     Server: server1.database.windows.net (NOT a vm)

     database within server:  db1

    1. Within server1.db1:  built a diagnostic settings instance

       a. had my IT department define a storage account workspace and connected that workspace to diagnostic settings.  

       b had my IT department define a log analytics workspace and connected that workspace to diagnostic settings

      c. Within diagnostic settings I clicked on the following to be logged:

      (under 'log')

         - SQLInsights

         - AutomaticTuning

         - QueryStoreRuntimeStatistics

         - QueryStoreWaitStatistics

         - Errors (the purpose of this effort)

         - DatabaseWaitStatistics

         - Timeouts

         - Blocks

         - Deadlocks

         (under 'metric')

         - basic

         - InstanceAndAppAdvanced

         - Workload Management

    2.  Then in the loganalytics workspace I downloaded and installed Windows Agent (64 bit).  This automatically connected my machine to the workspace

    3. Also within the loganalytics workspace I ensured that Under Azure Storage my storagte account was connected.

    (I have NOT yet connected any Data sources (options include Windows Event Logs, Windows Performance Counters, IIS Logs among other options)

    So when I go back to server1.db1.logs and click on any of the sample queries I get the message

     NO RESULTS FOUND (0 records matched)

    Also, my IT department gave me: monitoring contributor permission.  

     

    Any insights/comments welcomed.  Thank you!

      

    • JK_UK's avatar
      JK_UK
      Brass Contributor

      MarkSteben 

      On the Log Analytics Workspace, what happens if you run the following:

      AzureDiagnostics
      | where LogicalServerName_s contains 'server1'
      • MarkSteben's avatar
        MarkSteben
        Copper Contributor

        JK_UK When I run this query I do get records - 2,763 to be exact.

        Here is the first row.  madevsql01 is the actual server name. adscheduler is the database name on this particular record. Many different categories, including DatabaseWaitStatistics, QueryStoreRuntimeStatistics to name two.

        6/18/2020, 9:58:09.753 AM/SUBSCRIPTIONS/E1F843CE-5BAC-42BD-B3C2-A9EA02672DAB/RESOURCEGROUPS/OTTOBASE-CAMPAIGNS/PROVIDERS/MICROSOFT.SQL/SERVERS/MADEVSQL01/DATABASES/ADSCHEDULERDatabaseWaitStatisticsOTTOBASE-CAMPAIGNSe1f843ce-5bac-42bd-b3c2-a9ea02672dabMICROSOFT.SQLADSCHEDULERSERVERS/DATABASESDatabaseWaitStatistcsEventmadevsql01 AdScheduler
  • JK_UK's avatar
    JK_UK
    Brass Contributor

    MarkSteben I'm not exactly sure what you mean but there is no option to enable Log Analytics from the diagnostics settings for your VMs. Diagnostics are used to send metrics to storage accounts.

    You need to install the OMS agent extension on your VM to send your logs to Log Analytics or look under your LA Workspace Adv Settings / Connected Sources / Windows / Linux for more info on installing the agent and the settings you'll need to use.

     

    • MarkSteben's avatar
      MarkSteben
      Copper Contributor

      JK_UK thank you for responding. I am sorry to infer that 'my servers' referred to a VM.  These are native azure servers running azure sql server.  Does that change your answer re/ installing the OEM agent?  

      I will look at the advanced settings as you suggest.  Thanks again.

Resources