Using custom SWQL to search for subnets

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.

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

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.

Thanks very much!