Using custom SWQL to search for subnets

Hi there,

I notice after using the IPAM 4.3.2, the custom SWQL query is returning multiple results (repeated results). I did a subnet name search (refer to capture 1). It will display 181 results with the same subnet name. In the IP search, it will only return 1 search (refer to capture 2). I the SWQL queries are as below

Custom SWQL Query:

SELECT Address, CIDR, FriendlyName as DisplayName, VLAN FROM IPAM.Subnet WHERE CIDR > 0 AND GroupTypeText = 'Subnet';

Search SWQL Query:

SELECT Address, CIDR, FriendlyName as DisplayName, VLAN FROM IPAM.Subnet WHERE (Address LIKE '%${SEARCH_STRING}%' OR FriendlyName LIKE '%${SEARCH_STRING}%') AND CIDR > 0 AND GroupTypeText = 'Subnet';

Anyone encounter similar issue while using the custom SWQL and how can we solve this issue?

Thank you.

attachments.zip

Top Replies

  • Hi  lauminshen

    have you tried using the "distinct" keyword?

    SELECT distinct Address, CIDR, FriendlyName as DisplayName, VLAN FROM IPAM.Subnet WHERE CIDR > 0 AND GroupTypeText = 'Subnet';
  • Hi Michael,

    Yes, I tested it and it is working. Thanks for the response.

    Is this a known issue in IPAM?

  • I am having the same problem to make up for IPAM's shortcomings of subnet searching. I am really new to SQL and completely new to SWQL. I tried the exact same Query: SELECT distinct Address, CIDR, FriendlyName as DisplayName, VLAN FROM IPAM.Subnet WHERE CIDR > 0 AND GroupTypeText = 'Subnet'; and I receive hundreds of results still. Would you mind assisting me? I copied the query for a Custom Query exactly as shown in the Custom and Search above. Any help is appreciated. Thank you.

    Correction - I added distinct to both the query and the search and Order By to end and it now works. Thank you bothlauminshen Awesome!

  • Is anyone still using the swql successfully?  I just tried it in a custom query widget and got a non specific error.  Once I setup the sdk on this laptop I can test there and hopefully get more info but wondering if anyone else is seeing the same.  On v 2019.4

    SELECT distinct Address, CIDR, FriendlyName as DisplayName, VLAN FROM IPAM.Subnet WHERE CIDR > 0 AND GroupTypeText = 'Subnet'

  • I tested with SWQL Studio on a 2019.4 installation, and the query runs fine.

    pastedImage_0.png

    When I add that query in a Custom Query widget, I do see the error:

    pastedImage_2.png

    I checked the SWIS log file at C:\ProgramData\SolarWinds\InformationService\v3.0\Orion.InformationService.log.  I see this:

    2019-12-04 14:16:13,393 [97] ERROR SolarWinds.InformationService.Core.InformationService - w3wp| /LM/W3SVC/2/ROOT-1-13...62| Information.DoQueryInternal()| /Orion/Services/Information.asmx/QueryWithPartialErrors (null)  Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

    SolarWinds.Data.SWQL.SWQLSyntaxException: no viable alternative at input 'distinct' in Order By clause ---> Antlr.Runtime.NoViableAltException: A recognition error occurred.

      at SolarWinds.Data.SWQL.Parser.SWQLParser.negatedExpression()

      --- End of inner exception stack trace ---

      at SolarWinds.Data.SWQL.Parser.SWQLParser.negatedExpression()

      ...

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

    If I remove the DISTINCT keyword, then it runs without error.  I did some looking around, and this issue is tracked as internal issue OADP-2454.  There is a problem in the JavaScript that handles custom queries.  This has been fixed in internal development builds, so it will resolved in the first product release of 2020.