[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)

Hi lukas.belza,

Unfortunately this didn't work.

Yes, I do need data which is being produced by VBScript (both message and statistic) for a given component. Would oyu please be able to point me to the right direction where do I dig for this data. I really need this to setup regardless of complexity

Thank you,

Alex

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?

I have found both STATUS and MESSAGE entries for both VBScript and SQL USer Experience monitor in [Orion.APM.ChartEvidence] table.

Can I use this table instead of [Orion.APM.DynamicEvidence]? What are implications of doing so?

Ok, next attempt:

SELECT    
c.ComponentName AS NAME,    
c.StatusDescription AS STATUS,    
'' AS ColumnLabel,    
pe.AvgStatisticData AS STATISTIC,
pe.ErrorMessage AS MESSAGE  
FROM Orion.APM.Component(nolock=true) c    
JOIN Orion.APM.CurrentComponentStatus(nolock=true) ccs ON c.ComponentID = ccs.ComponentID  
JOIN Orion.APM.PortEvidence(nolock=true) pe ON pe.ComponentStatusID = ccs.ComponentStatusID
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  
)

I think that getting MESSAGE for script (dynamicevidence) components from ChartEvidence would not be possible and using it only for portevidence components would mean unnecessary overhead (as it internally joins dynamic evidence data).

Btw, what version of SAM are you running? Columns you are missing seem to be included in SAM 6.0. Also, are you using recent version of Orion SDK and connecting to "Orion (v3)"?

Hi Petr Vilem,

I think we have cracked it. I was using SDK with Orion (v2) Server Type, that's why I could not see those fields. Your answer above was absolutely spot on

I really appreciate your help on this.

By the way, I actually CAN get "message" for script components from ChartEvidence table. I can get both STATISTIC and both MESSAGEs for both SQL user experience and VBScript components. Still not sure if this can be used as a bases for what I want to achieve, as SQL query looks much more compact in this way. Would you please advise which option is better to stick with - UNION between DynamicEvidence and CurrentStatistic (your option above) or ChartEvidence (my option below)

SELECT  n.Caption AS NODE, a.Name AS APP, c.ComponentName AS CMPNT,  ce.AvgStatisticData AS STAT,  ce.ErrorMessage AS MSG FROM Orion.APM.Component(nolock=true) c  JOIN Orion.APM.CurrentComponentStatus(nolock=true) ccs ON c.ComponentID = ccs.ComponentID  JOIN Orion.APM.ChartEvidence(nolock=true) ce ON ce.ComponentStatusID = ccs.ComponentStatusID JOIN Orion.APM.Application(nolock=true) a ON c.ApplicationID = a.ApplicationID JOIN Orion.Nodes(nolock=true) n ON a.NodeID = n.NodeID WHERE ce.AvgStatisticData IS NOT NULL