SQL Error Log Messages on the Summary Dashboard

bmakhmutov

Hi,

I have dashboard of type Summary. I administer SQL Servers. I have group as 'SQL-DEV' which contains 20 dev sql servers.

I want to add report on SQL Errors from all servers in that group to the main page (INFO, WARN , ERROR - not decided yet) with paging , limiting to 20 rows. All that info is contained in each SQL error log.

How is it possible to do? Could you advice? 

With regards!

Seashore

Hi, I see one way directly with two options. Both involves monitoring the SQL Instances with "AppInsight For SQL" that is a template within SAM. So apply that one first to the SQL instances you are interested in.

Then there are two options. The first one though, only gives you event that are critical. It seems. The second option gives you all posts, but no info in the severity.

Add a "Custom query" widget and add below queries and try them out. You can build more on them if needed:

SELECT 
   -- E.EventType ,Seems to only collect critical events
    E.EventCode AS [Event ID]
    ,ToLocal(E.TimeGeneratedUtc) AS [Event Time]
    ,E.ComputerName AS [SQL Server]
    ,E.SourceName AS [SQL Instance]
    ,E.User
    ,E.Message
FROM Orion.APM.WindowsEvent AS E
INNER JOIN Orion.APM.Component AS C ON E.ComponentID=C.ComponentID
INNER JOIN Orion.APM.Application AS A ON C.ApplicationID=A.ID
WHERE
    C.Application.Template.DisplayName='AppInsight for SQL'
    AND E.TimeGeneratedUtc> ADDDAY(-1,GETUTCDATE())

SELECT 
    ToLocal(S.TimeStamp) AS [Event Time]
    ,S.Message
    ,A.Node.Caption AS [SQL Server]
    ,A.DisplayName AS [SQL Instance]
FROM Orion.APM.SqlServerErrorLog as s
INNER JOIN Orion.APM.Application AS A ON S.ApplicationID=A.ID
WHERE 
    S.TimeStamp>ADDHOUR(-1,GETUTCDATE())
ORDER BY S.TimeStamp DESC    

Hope above can help you forward!