Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

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

I have documented the whole solution here:

The Magic of SWQL: Create "All Components With Problems" Resource With Clickable Links, Status Icons and Even Hovers As …

=============================================================

Hi All,

This is pretty basic question but I could not find the answer. I need to be able to output in SWQL MESSAGE and STATISTIC values for the component. Can anyone help to modify below SWQL query to do so?

Desirable Output:

SELECT
ComponentName AS NAME,
StatusDescription AS STATUS
FROM
Orion.APM.Component
WHERE
ApplicationID IN ('995','996') AND
StatusDescription IN ('Warning','Critical')

Thank You,

Alex

Find more posts tagged with

SWQL

Accepted answers

Petr.Vilem

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:

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

All comments

lukas.belza

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)

AlexSoul

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

AlexSoul

also,

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

Petr.Vilem

Try this one:

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

AlexSoul

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?