Unable to access statistics and messages from alerts

I'm using a powershell component monitor to track uptime of our Azure Devops agents. The script returns the following statistics and messages:

Message.Health: N/A

Statistic.Health: XXX.XX

Message.OfflineAgents: N/A

Statistic.OfflineAgents: XXX.XX

Message.DisabledAgents: N/A

Statistic.DisabledAgents: XXX.XX

Message.AgentQueue: N/A

Statistic.AgentQueue: XXX.XX

I also have an alert that sends messages to MS Teams based on various criteria. I want the messages to include the value for "Message.DisabledAgents" from the script output. Is there a way to extract that specific message for use in my alert? According to the documentation, I can get ALL messages with this:

${N=SwisEntity;M=ComponentAlert.MultiValueMessages}

...or get the LAST message with this:

${N=SwisEntity;M=MultipleStatisticData.StringData}

Is there a way to JUST target the message I want?

Find more posts tagged with

Component

SWQL

Alerts

Accepted answers

Lofstrand

Hi @dustinwoodhousemela

There is always the option to create the variables ourselves. By "Define SQL/SWQL Variable".

I have tested below query in my lab, on a component that gets a value called "Expiration". Replace that string with "DisabledAgents" in your example.

${N=SWQL;M=SELECT
    DE.StringData AS [Message]
FROM Orion.APM.DynamicEvidenceCurrent AS DE
INNER JOIN Orion.APM.DynamicEvidenceColumnSchema AS S ON DE.ComponentID=S.ComponentID AND DE.ColumnSchemaID=S.ColumnSchemaID
WHERE 
    DE.ComponentID =${N=SwisEntity;M=ComponentID}
    AND DE.StringData IS NOT NULL
    AND S.Label='Expiration'
}

Lofstrand

Here is the same query but I have change it on two places to get the NumericData instead of StringData

SELECT
    --DE.StringData AS [Message]
    DE.NumericData
FROM Orion.APM.DynamicEvidenceCurrent AS DE -- ON CS.ComponentID=DE.ComponentID AND DE.StringData IS NOT NULL
INNER JOIN Orion.APM.DynamicEvidenceColumnSchema AS S ON DE.ComponentID=S.ComponentID AND DE.ColumnSchemaID=S.ColumnSchemaID
WHERE 
    de.ComponentID =5577
    --AND DE.StringData IS NOT NULL
    AND DE.NumericData IS NOT NULL
    AND S.Label='Expiration'

All comments

Lofstrand

Hi @dustinwoodhousemela

There is always the option to create the variables ourselves. By "Define SQL/SWQL Variable".

I have tested below query in my lab, on a component that gets a value called "Expiration". Replace that string with "DisabledAgents" in your example.

${N=SWQL;M=SELECT
    DE.StringData AS [Message]
FROM Orion.APM.DynamicEvidenceCurrent AS DE
INNER JOIN Orion.APM.DynamicEvidenceColumnSchema AS S ON DE.ComponentID=S.ComponentID AND DE.ColumnSchemaID=S.ColumnSchemaID
WHERE 
    DE.ComponentID =${N=SwisEntity;M=ComponentID}
    AND DE.StringData IS NOT NULL
    AND S.Label='Expiration'
}

zennifer

Nice!! Thanks for sharing! I really like having you embedded in the community! Your contributions have made a difference!

dustinwoodhousemela

Beautiful, simple, and worked like a charm! Thank you for your help with this. I looked all over and couldn't find documentation that helped with it. Is there reference documentation somewhere for the db schema?

dustinwoodhousemela

Also, how would this query change if I wanted the statistic, rather than the message?

Lofstrand

Here is the same query but I have change it on two places to get the NumericData instead of StringData

SELECT
    --DE.StringData AS [Message]
    DE.NumericData
FROM Orion.APM.DynamicEvidenceCurrent AS DE -- ON CS.ComponentID=DE.ComponentID AND DE.StringData IS NOT NULL
INNER JOIN Orion.APM.DynamicEvidenceColumnSchema AS S ON DE.ComponentID=S.ComponentID AND DE.ColumnSchemaID=S.ColumnSchemaID
WHERE 
    de.ComponentID =5577
    --AND DE.StringData IS NOT NULL
    AND DE.NumericData IS NOT NULL
    AND S.Label='Expiration'

Lofstrand

No, no documentation on the db schema. But if you download and use the free SWQL studio you can see all the tables and partly their relationship.

Having worked with this database for 20 years also helps some....

tobyw_loop1

Beg to differ, there is this documentation https://solarwinds.github.io/OrionSDK/ which at least gives you an overview of all the relations!

dustinwoodhousemela

Thank you!