This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

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

| 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

  • 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:

    001.JPG

  • 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 emoticons_happy.png

    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)

    001.JPG

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

    pastedImage_1.png

  • 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 emoticons_happy.png

    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