15 Replies Latest reply on Sep 15, 2015 11:04 AM by scottfraley

    In SWQL Studio, can I query the Orion.NPM.Interfaces table with a custom property field in the WHERE ?

    scottfraley

      The answer is, apparently, No.

       

      This query works just fine. All columns are there/exist. Cool.

       

      SELECT
          IF.InterfaceID, IF.Caption, IF.NodeID, IF.Node.Caption,
          IF.CustomProperties.ConnectedIP,
          IF.CustomProperties.ConnectedMAC,
          IF.CustomProperties.ConnectedVLAN
      FROM
          Orion.NPM.Interfaces IF
      WHERE
          IF.InterfaceID = 204

       

      Now, I try the following query, and I get the error shown after.

       

      SELECT
          IF.InterfaceID, IF.Caption, IF.NodeID, IF.Node.Caption,
          IF.CustomProperties.ConnectedIP,
          IF.CustomProperties.ConnectedMAC,
          IF.CustomProperties.ConnectedVLAN
      FROM
          Orion.NPM.Interfaces IF
      WHERE
          IF.InterfaceID = 204
          AND IF.CustomProperties.ConnectedMAC = 'AB.CD.0e.e0.cd.ab'

       

      And now the error:


      2015-09-14 21:40:57,703 [42] ERROR SolarWinds.InformationService.Core.InformationService - Exception running query:
      SELECT
          IF.InterfaceID, IF.Caption, IF.NodeID, IF.Node.Caption,
          IF.CustomProperties.ConnectedIP,
          IF.CustomProperties.ConnectedMAC,
          IF.CustomProperties.ConnectedVLAN
      FROM
          Orion.NPM.Interfaces IF
      WHERE
          IF.InterfaceID = 204
          AND IF.CustomProperties.ConnectedMAC = 'AB.CD.0e.e0.cd.ab' RETURN XML RAW
      2015-09-14 21:40:57,703 [42] ERROR SolarWinds.InformationService.Core.InformationService - Exception for Operation: <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
        <s:Header>
          <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">
            <ApplicationTag>SWQL Studio</ApplicationTag>
            <DataProviderTimeout>PT30S</DataProviderTimeout>
          </SwisSettings>
          <To s:mustUnderstand="1" xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none">https://lsdorionwest:17778/SolarWinds/InformationService/v3/OrionBasic</To>
          <Action s:mustUnderstand="1" xmlns="http://schemas.microsoft.com/ws/2005/05/addressing/none">http://schemas.solarwinds.com/2007/08/informationservice/InformationService/QueryXml</Action>
        </s:Header>
        <s:Body>
          <QueryXml xmlns="http://schemas.solarwinds.com/2007/08/informationservice">
            <query>SELECT
          IF.InterfaceID, IF.Caption, IF.NodeID, IF.Node.Caption,
          IF.CustomProperties.ConnectedIP,
          IF.CustomProperties.ConnectedMAC,
          IF.CustomProperties.ConnectedVLAN
      FROM
          Orion.NPM.Interfaces IF
      WHERE
          IF.InterfaceID = 204
          AND IF.CustomProperties.ConnectedMAC = 'AB.CD.0e.e0.cd.ab' RETURN XML RAW</query>
            <parameters />
          </QueryXml>
        </s:Body>
      </s:Envelope>
      2015-09-14 21:40:57,703 [42] ERROR SolarWinds.InformationService.Core.InformationService - Exception caught in method RunQuery
      SolarWinds.Data.Query.ParserException: Entity Orion.NPM.InterfacesCustomProperties does not contain requested property ConnectedMAC of navigation property IF.CustomProperties
         at SolarWinds.Data.Query.Processor.SemanticChecker.HandleNavigationProperty(EntityExpression entityExpression, String srcEntityName, String[] navigationPropertyNames)
         at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(EntityExpression entityExpression)
         at SolarWinds.Data.SWQL.AST.SwqlExpressionVisitorOfT`1.SolarWinds.Data.SWQL.ISwqlExpressionVisitorOfT<T>.Visit(IEntityExpression entity)
         at SolarWinds.Data.SWQL.AST.EntityExpression.Accept[T](ISwqlExpressionVisitorOfT`1 visitor)
         at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(BinaryExpression binary)
         at SolarWinds.Data.SWQL.AST.SwqlExpressionVisitorOfT`1.SolarWinds.Data.SWQL.ISwqlExpressionVisitorOfT<T>.Visit(IBinaryExpression binary)
         at SolarWinds.Data.SWQL.AST.BinaryExpression.Accept[T](ISwqlExpressionVisitorOfT`1 visitor)
         at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(BinaryExpression binary)
         at SolarWinds.Data.SWQL.AST.SwqlExpressionVisitorOfT`1.SolarWinds.Data.SWQL.ISwqlExpressionVisitorOfT<T>.Visit(IBinaryExpression binary)
         at SolarWinds.Data.SWQL.AST.BinaryExpression.Accept[T](ISwqlExpressionVisitorOfT`1 visitor)
         at SolarWinds.Data.Query.Swql.ParseTreeDuplicatorNoState.Visit(WhereClause where)
         at SolarWinds.Data.SWQL.AST.SwqlExpressionVisitorOfT`1.SolarWinds.Data.SWQL.ISwqlExpressionVisitorOfT<T>.Visit(IWhereClause where)
         at SolarWinds.Data.SWQL.AST.WhereClause.Accept[T](ISwqlExpressionVisitorOfT`1 visitor)
         at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SelectStatement statement)
         at SolarWinds.Data.SWQL.AST.SwqlExpressionVisitorOfT`1.SolarWinds.Data.SWQL.ISwqlExpressionVisitorOfT<T>.Visit(ISelectStatement statement)
         at SolarWinds.Data.SWQL.AST.SelectStatement.Accept[T](ISwqlExpressionVisitorOfT`1 visitor)
         at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, String replaceStatementTagWith)
         at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement)
         at SolarWinds.Data.Query.Processor.PreProcessor.ProcessInternal(PreProcessorState& preProcessorState)
         at SolarWinds.Data.Query.Processor.PreProcessor.Process(SelectStatement statement, ISchema schema, IDataProviderCatalog catalog, IPreProcessorStepFactory stepsFactory, ILimitationProvider limitationProvider, IIdentity identity, IAccessControlResolver accessControlResolver, PreProcessorState& preProcessorState)
         at SolarWinds.Data.Query.Engine.QueryProcessor.ProcessInternal(String query, IQueryExecutionContext context)
         at SolarWinds.Data.Query.Engine.QueryProcessor.ProcessQueryWithDeferredReaderCreation(String query, IQueryExecutionContext context)
         at SolarWinds.InformationService.Core.InformationService.<>c__DisplayClass21.<RunQuery>b__1e()
         at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query, String username, Func`1 buildQueryPlan)
         at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

       

      I can't imagine how IF.CustomProperties.ConnectedMAC doesn't exist when it and it's value(s) are displayed via the first query with no problem at all!

       

      What gives?!  (Hopefully it's just something silly I'm unaware of, because I really need the query that includes said field in the where!

       

       

      Thanks,

      Scott