cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Highlighted

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

Jump to solution

I have documented the whole solution here:

The Magic of SWQL: Create "All Components With Problems" Resource With Clickable Links, Status Icons...

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

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:

| NAME | STATUS | STATISTIC | MESSAGE |

SELECT

ComponentName AS NAME,

StatusDescription AS STATUS

FROM

Orion.APM.Component

WHERE

ApplicationID IN ('995','996') AND

StatusDescription IN ('Warning','Critical')

Thank You,

Alex

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Level 13

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

Jump to solution

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

)

View solution in original post

0 Kudos
11 Replies
Highlighted
Level 12

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

Jump to solution

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)

Highlighted

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

Jump to solution

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

0 Kudos
Highlighted

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

Jump to solution

also,

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

001.JPG

0 Kudos
Highlighted
Level 13

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

Jump to solution

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

0 Kudos
Highlighted

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

Jump to solution

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?

0 Kudos
Highlighted
Level 13

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

Jump to solution

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

)

View solution in original post

0 Kudos
Highlighted

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

Jump to solution

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)

001.JPG

Any thoughts what I can do next?

0 Kudos
Highlighted

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

Jump to solution

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?

0 Kudos
Highlighted
Level 13

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

Jump to solution

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)"?

pastedImage_1.png