Forecast Alert based on Node Custom Property

twomonkeysayoyo

Hi all,

I'm having a lot of trouble figuring out how to use the VolumesForecastCapacity to alert only on nodes with a specific custom property.  Any help figuring out the logic would be much appreciated.

Currently I'm just trying to adjust the out-of-the-box alert (below):

SELECT VolumesForecastCapacity.Uri, VolumesForecastCapacity.DisplayName FROM Orion.VolumesForecastCapacity AS VolumesForecastCapacity

LEFT JOIN Orion.Nodes nodes on nodes.NodeID = VolumesForecastCapacity.NodeID

    LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

WHERE

  VolumesForecastCapacity.DaysToCriticalAvg <= 5

  AND VolumesForecastCapacity.MetricName = 'Forecast.Metric.PercentDiskUsed'

  AND DayDiff(VolumesForecastCapacity.MinDateTime, VolumesForecastCapacity.MaxDateTime) >= ISNULL(s.CurrentValue, 7)

  AND nodes.Unmanaged = FALSE

by simply adding a

  AND nodes.n_production = TRUE

at the end of the "WHERE".

If I'm in the database the following works fine:

SELECT TOP 100 * FROM [dbo].[nodes]

WHERE nodes.n_production = 'true'

AND nodes.unmanaged = 'false'

Please help me understand what I am doing wrong.

THE ERROR I RECEIVE:

2019-02-01 09:56:14,565 [12] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null)  Exception running query:

SELECT VolumesForecastCapacity.Uri, VolumesForecastCapacity.DisplayName FROM Orion.VolumesForecastCapacity AS VolumesForecastCapacity

LEFT JOIN Orion.Nodes nodes on nodes.NodeID = VolumesForecastCapacity.NodeID

    LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

WHERE

  VolumesForecastCapacity.DaysToCriticalAvg <= 5

  AND VolumesForecastCapacity.MetricName = 'Forecast.Metric.PercentDiskUsed'

  AND DayDiff(VolumesForecastCapacity.MinDateTime, VolumesForecastCapacity.MaxDateTime) >= ISNULL(s.CurrentValue, 7)

  AND nodes.Unmanaged = FALSE

  AND nodes.n_production = TRUE RETURN XML RAW

2019-02-01 09:56:14,565 [12] 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:9732e380-cb7b-4c50-8dd6-dbaddad9414f</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://REDACTED: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>2019-02-01T14:56:14.455Z</u:Created>

        <u:Expires>2019-02-01T15:01:14.455Z</u:Expires>

      </u:Timestamp>

      <c:SecurityContextToken u:Id="uuid-c17235b9-0272-4416-ba43-fb0ff2e22c8a-379" xmlns:c="http://schemas.xmlsoap.org/ws/2005/02/sc">

        <c:Identifier>urn:uuid:42ff7b93-86ca-4b7e-91eb-fa4242389119</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>QR9Go1SbAsY3ZPs1CTIA2VpSa7I=</DigestValue>

          </Reference>

        </SignedInfo>

        <SignatureValue>cwAT0fwKPzoqvIYW+tKbb/NZ748=</SignatureValue>

        <KeyInfo>

          <o:SecurityTokenReference>

            <o:Reference URI="#uuid-c17235b9-0272-4416-ba43-fb0ff2e22c8a-379">

            </o:Reference>

          </o:SecurityTokenReference>

        </KeyInfo>

      </Signature>

    </o:Security>

  </s:Header>

  <s:Body>

    <QueryXml xmlns="http://schemas.solarwinds.com/2007/08/informationservice">

      <query> SELECT VolumesForecastCapacity.Uri, VolumesForecastCapacity.DisplayName FROM Orion.VolumesForecastCapacity AS VolumesForecastCapacity

LEFT JOIN Orion.Nodes nodes on nodes.NodeID = VolumesForecastCapacity.NodeID

    LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

WHERE

  VolumesForecastCapacity.DaysToCriticalAvg &lt;= 5

  AND VolumesForecastCapacity.MetricName = 'Forecast.Metric.PercentDiskUsed'

  AND DayDiff(VolumesForecastCapacity.MinDateTime, VolumesForecastCapacity.MaxDateTime) &gt;= ISNULL(s.CurrentValue, 7)

  AND nodes.Unmanaged = FALSE

  AND nodes.n_production = TRUE RETURN XML RAW</query>

      <parameters>

      </parameters>

    </QueryXml>

  </s:Body>

</s:Envelope>

2019-02-01 09:56:14,565 [12] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null)  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

SolarWinds.Data.Query.ParserException: Entity Orion.Nodes does not contain requested property n_production

   at SolarWinds.Data.Query.Processor.SemanticChecker.HandleQualifiedPropertyName(EntityExpression entityExpression)

   at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(BinaryExpression binary)

   at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(BinaryExpression binary)

   at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(BinaryExpression binary)

   at SolarWinds.Data.Query.Swql.ParseTreeDuplicatorNoState.Visit(WhereClause where)

   at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SelectStatement statement)

   at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state, String replaceStatementTagWith)

   at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state)

   at SolarWinds.Data.Query.Processor.PreProcessor.Process(SelectStatement selectStatement, PreProcessorState& preProcessorState)

   at SolarWinds.Data.Query.Engine.QueryProcessor.ProcessInternal(String query, IQueryExecutionContext context)

   at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query, String username, Boolean federationEnabled, Func`1 buildQueryPlan)

   at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

Thanks,

twomonkeysayoyo

twomonkeysayoyo

Ok, figured it out.  Was dumb.

For some reason I don't understand the line needed to be:

  AND nodes.CustomProperties.n_production = TRUE