SWQL wildcard - Custom report

ssams 1 month ago

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'

0 marcrobinson 1 month ago

Hello ssams

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.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 ssams 1 month ago in reply to marcrobinson

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
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel