SWQL Custom Alert, Triggered Condition

[ThePriceIsRIGHT]

I am changing a currently in use custom alert triggered with the following condition: specific nodes that use the specific custom poller that hit 30c or higher will trigger an alert.

The below query works and is valid but now I want it to be used on all nodes that use the custom poller. The condition needs to change to: all nodes that use the specific custom poller that hit 30c or higher will trigger an alert.

Inner Join Orion.NPM.CustomPollerAssignment cpa on cpa.NodeID = Nodes.NodeID
Inner Join Orion.NPM.CustomPollerStatus cps on cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
Where cpa.CustomPollerID = 'e6a6c65b-8a84-4b9c-a87c-23999fa30b4e' and cps.status >=30 and (cpa.NodeID = 491 or cpa.NodeID = 716 OR cpa.NodeID = 540 OR cpa.NodeID = 473 OR cpa.NodeID = 460)

Trying to Remove "and (cpa.NodeID = 491 or cpa.NodeID = 716 OR cpa.NodeID = 540 OR cpa.NodeID = 473 OR cpa.NodeID = 460)" from the query but keep getting "Query condition is not valid."

Basically, I am trying to use this more broadly all nodes and not on specific nodes.  

[ThePriceIsRIGHT]

I have tried the below but still unable to get the code valid. if there are any suggestions for fixing this or an alternative way to code this I am all ears. Thanks in advance to anyone that helps me figure this out.

[mesverrum]

I would second the suggestion from above to install swql studio on your server and test it there since the error messages in swql studio are much more specific. I can't see any reason why the query is not working for you but has no issues for the rest of us.

[mesverrum]

Maybe a stray character when you edited it or something weird in your browser session that a restart might fix, because i copied your query and it tests as valid for me.

Inner Join Orion.NPM.CustomPollerAssignment cpa on cpa.NodeID = Nodes.NodeID
Inner Join Orion.NPM.CustomPollerStatus cps on cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
Where cpa.CustomPollerID = 'e6a6c65b-8a84-4b9c-a87c-23999fa30b4e' and cps.status >=30

[ThePriceIsRIGHT]

The attached picture is what I am presented. I have tried on different browsers and computers.

[shuth]

That query works for me.

[ThePriceIsRIGHT]

Its still not working, any thoughts?

[sum_giais]

Have you tried running the query manually in the SWQL studio? I think I’ve hit something similar in thenpast but I can’t quite remember the specifics. I’m a stickler for consistency though... first thing I’d do is add in the AS where appropriate, capitalization of statements and proper spacing. Sorry couldn’t help but say it!

I’ll test the query in my lab soon and see what I get too.

[sum_giais]

Welp... your exact query works for me as well. 🤔

Though here's a bit of syntactical cleanup - functionally speaking it doesn't change the query. But when comparing values it saves on typing to use cpa.NodeID IN (x, y, z) etc.

INNER JOIN Orion.NPM.CustomPollerAssignment AS cpa ON cpa.NodeID = Nodes.NodeID
INNER JOIN Orion.NPM.CustomPollerStatus AS cps ON cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
WHERE ( ( cpa.CustomPollerID = 'e6a6c65b-8a84-4b9c-a87c-23999fa30b4e' AND cps.status >= 30 ) 
AND cpa.NodeID IN ( 460, 473, 491, 540, 716 ) )

Also may go without saying but to clarify if you do test in SWQL Studio, you need to prepend the SELECT statement. So it would be below to test since the alert configuration takes care of the SWQL SELECT for the entity.

SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes
INNER JOIN Orion.NPM.CustomPollerAssignment AS cpa ON cpa.NodeID = Nodes.NodeID
INNER JOIN Orion.NPM.CustomPollerStatus AS cps ON cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
WHERE ( ( cpa.CustomPollerID = 'e6a6c65b-8a84-4b9c-a87c-23999fa30b4e' AND cps.status >= 30 ) 
AND cpa.NodeID IN ( 460, 473, 491, 540, 716 ) )

 
Hope this helps and good luck.

[ThePriceIsRIGHT]

Finally got to my desktop with swql studio, and got a new error message below

Log:

2020-10-02 09:20:15,717 [176] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception running query:
SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

Inner Join Orion.NPM.CustomPollerAssignment cpa on cpa.NodeID = Nodes.NodeID
Inner Join Orion.NPM.CustomPollerStatus cps on cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
Where cpa.CustomPollerID = 'e6a6c65b-8a84-4b9c-a87c-23999fa30b4e' and cps.status >=30
RETURN XML RAW
2020-10-02 09:20:15,717 [176] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception for Operation: <s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<s:Header>
<a:Action s:mustUnderstand="1">http://schemas.solarwinds.com/2007/08/informationservice/InformationService/QueryXml</a:Action>
<a:MessageID>urn:uuid:cacc962c-7a68-42c5-aa47-ed4f18eb2863</a:MessageID>
<a:ReplyTo>
<a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
</a:ReplyTo>
<i18n xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<Culture>en-US</Culture>
</i18n>
<SwisSettings xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<AppendErrors>true</AppendErrors>
<ApplicationTag>SWQL Studio</ApplicationTag>
<DataProviderTimeout>PT30S</DataProviderTimeout>
</SwisSettings>
<SwisProtocolVersion xmlns="http://schemas.solarwinds.com/2007/08/informationservice">2</SwisProtocolVersion>
<IsBase64EncodingAccepted xmlns="http://schemas.solarwinds.com/2007/08/informationservice">true</IsBase64EncodingAccepted>
<a:To s:mustUnderstand="1">net.tcp://solarwinds.nde.unlv.edu:17777/SolarWinds/InformationService/v3/Orion/ssl</a:To>
<o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
<u:Timestamp u:Id="_0">
<u:Created>2020-10-02T16:20:15.663Z</u:Created>
<u:Expires>2020-10-02T16:25:15.663Z</u:Expires>
</u:Timestamp>
<c:SecurityContextToken u:Id="uuid-8fba84ee-c1d2-4247-85dc-fae32779cb50-4248" xmlns:c="http://schemas.xmlsoap.org/ws/2005/02/sc">
<c:Identifier>urn:uuid:158cd80d-60e3-43f6-a3bc-b2e24a4ea15d</c:Identifier>
</c:SecurityContextToken>
<Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
<SignedInfo>
<CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
</CanonicalizationMethod>
<SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#hmac-sha1">
</SignatureMethod>
<Reference URI="#_0">
<Transforms>
<Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
</Transform>
</Transforms>
<DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1">
</DigestMethod>
<DigestValue>wXMy/ZwBZhxEPn/2qG7bITYJLyA=</DigestValue>
</Reference>
</SignedInfo>
<SignatureValue>31Z+ceRB+2GtLvl2uFLN7p2TMTA=</SignatureValue>
<KeyInfo>
<o:SecurityTokenReference>
<o:Reference URI="#uuid-8fba84ee-c1d2-4247-85dc-fae32779cb50-4248">
</o:Reference>
</o:SecurityTokenReference>
</KeyInfo>
</Signature>
</o:Security>
</s:Header>
<s:Body>
<QueryXml xmlns="http://schemas.solarwinds.com/2007/08/informationservice">
<query>SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

Inner Join Orion.NPM.CustomPollerAssignment cpa on cpa.NodeID = Nodes.NodeID
Inner Join Orion.NPM.CustomPollerStatus cps on cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
Where cpa.CustomPollerID = 'e6a6c65b-8a84-4b9c-a87c-23999fa30b4e' and cps.status &gt;=30
RETURN XML RAW</query>
<parameters>
</parameters>
</QueryXml>
</s:Body>
</s:Envelope>
2020-10-02 09:20:15,717 [176] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '48.5' to data type int.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at SolarWinds.InformationService.DataProviders.SqlQueryRelation.<GetEnumerator>d__8.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.ProviderPassThroughScanOp.<GetEnumeratorInternal>d__5.MoveNext()
at SolarWinds.Data.Query.QueryStatisticsEnumerator.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.ProjectOp.<GetEnumeratorInternal>d__7.MoveNext()
at SolarWinds.Data.Query.QueryStatisticsEnumerator.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.PhysicalQueryPlan.<GetEnumerator>d__4.MoveNext()
at SolarWinds.InformationService.Core.InformationService.RowsCountingQueryResultReader.MoveNext()
at SolarWinds.InformationService.Serialization.XmlResponseSerializer..ctor(IQueryResultReader reader, IQueryExecutionPlan queryExecutionPlan, Boolean includeQueryPlan, IQueryStatisticsContext statistics, I18n i18N)
at SolarWinds.InformationService.Serialization.XmlSerializer.Serialize(String query, IQueryExecutionPlan queryExecutionPlan, IQueryResultReader reader, Boolean isBase64EncodingAccepted, IMessageFormatterStrategy messageFormatterStrategy)
at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer, IMessageFormatterStrategy messageFormatterStrategy)
ClientConnectionId:b0aadfea-2ae4-413a-af00-0e84b26ec1ab
Error Number:245,State:1,Class:16

[sum_giais · Accepted solution]

Ok that's a conversion issue possibly. Might need to grab the raw status from another field or just put single quotes around the 30, e.g. >= '30' because the value from the database may be a string.

[ThePriceIsRIGHT]

The quotes are what make the made the query valid. Thanks

[shuth]

Agreed. Probably try the RawStatus field. If it's converting it to text (nvarchar), you're trying to see if a text value is bigger than a number.

[ThePriceIsRIGHT]

Finally got to my desktop with SWQL Studio, Tried all the variations for the code provided here now I got a new error message.

"Conversion failed when converting the nvarcher value. '48.5' to data type int."

Log:

2020-10-02 09:00:30,723 [183] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception running query:
SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

Inner Join Orion.NPM.CustomPollerAssignment AS cpa on cpa.NodeID = Nodes.NodeID
Inner Join Orion.NPM.CustomPollerStatus AS cps on cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
Where cpa.CustomPollerID = 'e6a6c65b-8a84-4b9c-a87c-23999fa30b4e' and cps.status >=30 RETURN XML RAW
2020-10-02 09:00:30,723 [183] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception for Operation: <s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<s:Header>
<a:Action s:mustUnderstand="1">http://schemas.solarwinds.com/2007/08/informationservice/InformationService/QueryXml</a:Action>
<a:MessageID>urn:uuid:5b6bafd4-7aa9-46a3-bc6a-f41d95f3d068</a:MessageID>
<a:ReplyTo>
<a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
</a:ReplyTo>
<i18n xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<Culture>en-US</Culture>
</i18n>
<SwisSettings xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<AppendErrors>true</AppendErrors>
<ApplicationTag>SWQL Studio</ApplicationTag>
<DataProviderTimeout>PT30S</DataProviderTimeout>
</SwisSettings>
<SwisProtocolVersion xmlns="http://schemas.solarwinds.com/2007/08/informationservice">2</SwisProtocolVersion>
<IsBase64EncodingAccepted xmlns="http://schemas.solarwinds.com/2007/08/informationservice">true</IsBase64EncodingAccepted>
<a:To s:mustUnderstand="1">net.tcp://solarwinds.nde.unlv.edu:17777/SolarWinds/InformationService/v3/Orion/ssl</a:To>
<o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
<u:Timestamp u:Id="_0">
<u:Created>2020-10-02T16:00:30.697Z</u:Created>
<u:Expires>2020-10-02T16:05:30.697Z</u:Expires>
</u:Timestamp>
<c:SecurityContextToken u:Id="uuid-8fba84ee-c1d2-4247-85dc-fae32779cb50-4248" xmlns:c="http://schemas.xmlsoap.org/ws/2005/02/sc">
<c:Identifier>urn:uuid:158cd80d-60e3-43f6-a3bc-b2e24a4ea15d</c:Identifier>
</c:SecurityContextToken>
<Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
<SignedInfo>
<CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
</CanonicalizationMethod>
<SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#hmac-sha1">
</SignatureMethod>
<Reference URI="#_0">
<Transforms>
<Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
</Transform>
</Transforms>
<DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1">
</DigestMethod>
<DigestValue>z6aWWChSLWdAumbLXsmQz8DKqls=</DigestValue>
</Reference>
</SignedInfo>
<SignatureValue>DIUa2aMT5l+LXjXfLSctJP8o060=</SignatureValue>
<KeyInfo>
<o:SecurityTokenReference>
<o:Reference URI="#uuid-8fba84ee-c1d2-4247-85dc-fae32779cb50-4248">
</o:Reference>
</o:SecurityTokenReference>
</KeyInfo>
</Signature>
</o:Security>
</s:Header>
<s:Body>
<QueryXml xmlns="http://schemas.solarwinds.com/2007/08/informationservice">
<query>SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

Inner Join Orion.NPM.CustomPollerAssignment AS cpa on cpa.NodeID = Nodes.NodeID
Inner Join Orion.NPM.CustomPollerStatus AS cps on cpa.CustomPollerAssignmentID = cps.CustomPollerAssignmentID
Where cpa.CustomPollerID = 'e6a6c65b-8a84-4b9c-a87c-23999fa30b4e' and cps.status &gt;=30 RETURN XML RAW</query>
<parameters>
</parameters>
</QueryXml>
</s:Body>
</s:Envelope>
2020-10-02 09:00:30,723 [183] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '48.5' to data type int.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at SolarWinds.InformationService.DataProviders.SqlQueryRelation.<GetEnumerator>d__8.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.ProviderPassThroughScanOp.<GetEnumeratorInternal>d__5.MoveNext()
at SolarWinds.Data.Query.QueryStatisticsEnumerator.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.ProjectOp.<GetEnumeratorInternal>d__7.MoveNext()
at SolarWinds.Data.Query.QueryStatisticsEnumerator.MoveNext()
at SolarWinds.Data.Query.PhysicalQueryPlan.PhysicalQueryPlan.<GetEnumerator>d__4.MoveNext()
at SolarWinds.InformationService.Core.InformationService.RowsCountingQueryResultReader.MoveNext()
at SolarWinds.InformationService.Serialization.XmlResponseSerializer..ctor(IQueryResultReader reader, IQueryExecutionPlan queryExecutionPlan, Boolean includeQueryPlan, IQueryStatisticsContext statistics, I18n i18N)
at SolarWinds.InformationService.Serialization.XmlSerializer.Serialize(String query, IQueryExecutionPlan queryExecutionPlan, IQueryResultReader reader, Boolean isBase64EncodingAccepted, IMessageFormatterStrategy messageFormatterStrategy)
at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer, IMessageFormatterStrategy messageFormatterStrategy)
ClientConnectionId:dcda4311-a68d-4ac3-8f07-dab4bb18bef9
Error Number:245,State:1,Class:16