[SOLVED] SWQL Query: How Do I Output "Statistic" and "Message" Values For The Component?

Hello,

Please try following query:

SELECT

c.ComponentName AS NAME,

c.StatusDescription AS STATUS,

cs.ComponentStatisticData AS STATISTIC,

cs.ErrorMessage AS MESSAGE

FROM Orion.APM.Component c

JOIN ORION.APM.CurrentStatistics cs ON c.ComponentID = cs.ComponentID

If you need data for script monitors then the query is more complicated because multi-value script monitors store data in separate tables.

Lukas Belza

(SolarWinds development)

also,

I do not have [ComponentStatisticData] nor [ErrorMessage] fields in Orion.APM.CurrentStatistics table:

Try this one:

SELECT
c.ComponentName AS NAME,
c.StatusDescription AS STATUS,
de.ColumnLabel,
de.ColumnName,
de.AvgNumericData AS STATISTIC,
de2.StringData AS MESSAGE,
ccs.ErrorMessage AS ErrorMesage
FROM Orion.APM.Component(nolock=true) c
JOIN Orion.APM.CurrentComponentStatus(nolock=true) ccs ON c.ComponentID = ccs.ComponentID
JOIN Orion.APM.DynamicEvidence(nolock=true) de ON de.ComponentStatusID = ccs.ComponentStatusID AND de.ColumnType = 1
LEFT JOIN Orion.APM.DynamicEvidence(nolock=true) de2 ON de2.ComponentStatusID = ccs.ComponentStatusID AND de2.ColumnType = 0

Petr Vilem, you are a STAR. It works as a charm (had to remove line 8 though for it to work). This gives me plenty of ideas now

Just one more thing I could not figure out - this doesn't seems to pick any data for SQL Server User Experience Monitor component type, would you be able to help with this one?

For SQL User Experience Monitors first solution recommended by lukas.belza applies, so if you need both types in single query you will need to do union like:

SELECT
c.ComponentName AS NAME,
c.StatusDescription AS STATUS,
'' AS ColumnLabel,
cs.ComponentStatisticData AS STATISTIC,
cs.ErrorMessage AS MESSAGE
FROM Orion.APM.Component c
JOIN ORION.APM.CurrentStatistics cs ON c.ComponentID = cs.ComponentID
UNION
(
SELECT  
c.ComponentName AS NAME,  
c.StatusDescription AS STATUS,  
de.ColumnLabel,  
de.AvgNumericData AS STATISTIC,  
de2.StringData AS MESSAGE
FROM Orion.APM.Component(nolock=true) c  
JOIN Orion.APM.CurrentComponentStatus(nolock=true) ccs ON c.ComponentID = ccs.ComponentID
JOIN Orion.APM.DynamicEvidence(nolock=true) de ON de.ComponentStatusID = ccs.ComponentStatusID AND de.ColumnType = 1
LEFT JOIN Orion.APM.DynamicEvidence(nolock=true) de2 ON de2.ComponentStatusID = ccs.ComponentStatusID AND de2.ColumnType = 0
)

Hi Petr Vilem and lukas.belza, I really appreciate your help.

As per your example above I do not have "ComponentStatisticData" nor ErrorMessage fields in Orion.APM.CurrentStatistics table (see screenshot of all fields I have in this table above)

Any thoughts what I can do next?

Hi Petr Vilem and lukas.belza, I really appreciate your help.

As per your example above I do not have "ComponentStatisticData" nor ErrorMessage fields in Orion.APM.CurrentStatistics table (see screenshot of all fields I have in this table above)

Any thoughts what I can do next?