SWQL wildcard - Custom report

I was working with support to create a custom report that queries the NCM Route Table so I can report on a few routers and the routes they advertise.  I was given this statement but then told to contact THWACK for further assistance.

I am needing to EXCLUDE RFC 1918 IP space from this report.  Can someone help me with that?  I tried 10.% and that did not work.  I am not an SQL guy.  Its the very last line

where NcmRouteTable.destination not in ('10.10.0.0','10.11.0.0','add-more destination ip here') and OrionNodes.Caption Like '%INET-RT1'

thanks!

SELECT OrionNodes.Caption, OrionNodes.DetailsUrl, OrionNodes.Status, OrionNodes.ChildStatus,

NcmInterfaces.InterfaceName, NcmInterfaces.InterfaceDescription, NcmInterfaces.InterfaceIndex, NcmRouteTable.Destination, NcmRouteTable.Mask,

NcmRouteTable.NextHop,NcmRouteTable.RouteType,NcmRouteTable.RouteProtocol,NcmRouteTable.Metric1, NcmRouteTable.Metric2

FROM NCM.NodeProperties AS NcmNodeProperties

INNER JOIN Orion.Nodes AS OrionNodes ON NcmNodeProperties.CoreNodeID=OrionNodes.NodeID

INNER JOIN NCM.RouteTable AS NcmRouteTable ON NcmNodeProperties.NodeID=NcmRouteTable.NodeID

LEFT JOIN NCM.Interfaces AS NcmInterfaces ON NcmRouteTable.InterfaceID=NcmInterfaces.InterfaceID

where NcmRouteTable.destination not in ('10.10.0.0','10.11.0.0','add-more destination ip here') and OrionNodes.Caption Like '%INET-RT1'

Parents
  • Hello  

    in the Where statement, you tried where NcmRouteTable.destination not in ('10.%') or did you use NcmRouteTable.destination not like '10.%' 

    I do not have access to a system for testing, but wonder if that might be the issue.  The wildcard matching might throw that 10 in the second or even third octet also...  I wish swql would support REGEX more directly sometimes. That would simplify things. 

    Ok, I am putting this out there, as I cannot test it. But suspect a substring match on first two digits would work. This could be a subquery actually. If no one jumps in, please reply back to me and I will see what I can come up with or if I can get someone to respond.

  • they gave me not in (Solarwinds tech support).  One of our SQL admins here told me to use not like.  It worked on the first one but when adding a second one, it actually ignored the first and filtered the second.  He had me try two queries and use union all but SWQL did not like that so I made a bit of progress but now sure where to go next

Reply Children
No Data