Statistic AND Message from an SQL query

FormerMember

I was sure I once requested this but I can't find my post, so here I go again..

Would it be possible to allow a named column (or column number) to be used as the Message part of the component?

As it is now SQL Monitors only make use of Statistic (the value returned from the query), but very very often we need to pull other information out of the query too that helps understand the Statistic value, which in turn can be placed in Email/Logging/Reporting.

My suggestion would be by default (for backwards compatibility) to set Statistic to be either the only returned column, or the first column.  This then allows the second column to be used as the Message, or alternatively if aliases are used in the query and are named Statistic or Message then they are set appropriately.

e.g.
SELECT foo FROM MyTable WHERE SomeColumn > 0   (this would set Statistic = foo, like it is now)
SELECT foo, bar FROM MyTable WHERE SomeColumn > 0   (Statistic = foo, Message = bar)
SELECT foo AS Statistic, bar AS Message FROM MyTable WHERE SomeColumn > 0 (named aliases that show which columns should be used for Statistic/Message)

I could have sworn this had been asked previously by others too, and seems such a minor change I'd hoped it would be in APM 4.2, but no such luck

Thanks.

lukas.belza

Hello AndyCoates,

This functionality is already in APM 4.0.2 for all SQL monitors (SQL Server/ODBC/Oracle User Experience Monitor). The Message can be returned in the second column of the SQL query result. For example:

SELECT 1.15, 'This is message returned from SQL query.'

You can see the Message in the "Component Details" resource on "APM Component Details" page:

Also this message is available in alerts using ${StatusOrErrorDescription} variable. Unfortunately there is no predefined variable in reports, but you can use Custom SQL report where you can use SQL dbo.apm_GetComponentStatusErrorDescription(@ComponentID) function to get this message. For example:

 SELECT     
    cs.NodeID as NodeId, 
    cs.ApplicationId as ApplicationId,
    cs.ComponentId as ComponentId,
    cs.ComponentName as ComponentName,
    cs.ComponentType as ComponentType,
    cs.ApplicationName as ApplicationName,
    cs.ComponentStatisticData as StatisticData,
    dbo.apm_GetComponentStatusErrorDescription(cs.ComponentID) AS StatusOrErrorDescription,
FROM APM_CurrentStatistics AS cs

Please let us know if you have any questions.

I'll create a new Customer Feature Request to add this variable also to Reporting.

 

Thanks,

Lukas Belza (SolarWinds development)