Subnet filtering - Top XX Subnets by %

Not a SQL guy by any stretch of the imagination, help appreciated. I saw a filter example for limiting the reports to a /24 "Subnet.CIDR = 24". Wondering if there is a way to perform a NOT on /30's? IE - I want to see everything excpet /30's and maybe /29's - can you run 2 filters?

Thanks,

Find more posts tagged with

filter

sql

view

howto

ipam

custom_field

resource

Accepted answers

Peter.Cooper

More Filters!

Only show subnets that do not contain DHCP in the name

Subnet.FriendlyName not like '%dhcp%'

Only show subnets with DHCP in the name

Subnet.FriendlyName like '%dhcp%'

All comments

Peter.Cooper

Hello kbrookov,

I want to see everything excpet /30's and maybe /29's - can you run 2 filters?

Try this filter:

Subnet.CIDR <> 30 AND Subnet.CIDR <> 29

Let me know if this helps!

smcdonald

Similar question - how can I do this for Custom Atttributes?
I've added a bunch of custom attributes like type of network (VPN/Internet/Internal etc) as custom fields for the subnets but can't figure out how to use these filters on the Top 10 page - can you drop me a hint?

Thanks!

Peter.Cooper

The trick is that custom fields are NULL if there has never been a value set.

When using a custom field to include (based on its value), start with:

(Subnet.custom.DatabaseColumn IS NOT NULL) AND

When using a custom field to exclude (based on its value), start with:

(Subnet.custom.DatabaseColumn IS NULL) OR

How to filter subnets out of an IPAM TopXX resource

Click IPAM settings > Manage IPAM custom fields > Add
Database Column: 'AvoidTopXX'
Description: 'Set this value to "true" if this subnet should not be in TopXX resources'
Max String Length: 16
Uncheck 'Add to IP addresses'
Save
Locate and edit your 'Top XX Subnets by % IP Address Used' resource
For Filter, enter this:
(Subnet.custom.AvoidTopXX IS NULL) OR Subnet.custom.AvoidTopXX NOT LIKE '%true%'
Submit
Any subnet you don't want in this resource, set it's AvoidTopXX field to 'true'

How to filter subnets into an IPAM TopXX resource

Click IPAM settings > Manage IPAM custom fields > Add
Database Column: 'VPN'
Uncheck 'Add to IP addresses'
Save
Locate and edit your 'Top XX Subnets by % IP Address Used' resource
For Filter, enter this:
(Subnet.custom.VPN IS NOT NULL) AND Subnet.custom.VPN LIKE '%_%'
Submit
Any subnet you do want in this resource, set a value for VPN field

kbrookov

Is there anyway to edit the actual report?

Add a column to the report - want to see location.

Invert it so you see Least % used?

AlbanyNYMike

Boy, this is getting too deep.
Hopefully someone can help me with this fairly simple filter, because I am having no luck.

I have to types of subnets:

Server and DHCP.

I want my IPAM homepage to list the topX server scopes on the right, and topX DHCP scopes on the left.

Easy enough to add the TopX resource, but I am really struggling trying to filter out the DHCP scopes one the server side, and vise versa on the DHCP side.

All DHCP scopes contain "dhcp" in the scope names, so it should be easy.

For the Server topX resource, It should be "Only show subnets that do not contain DHCP in the name" and for the DHCP topx, the filter should be "Only show subnets with DHCP in the name"

Again, I am struggling with the syntax. PLease help!

thanks

Peter.Cooper

More Filters!

Only show subnets that do not contain DHCP in the name

Subnet.FriendlyName not like '%dhcp%'

Only show subnets with DHCP in the name

Subnet.FriendlyName like '%dhcp%'

kbrookov

Your filter should look something like this:

Subnet.FriendlyName Like '*dhcp'

You may need to move the * depending on how you named the subnet.

Peter.Cooper

Is there anyway to edit the actual report?
Add a column to the report - want to see location.
Invert it so you see Least % used?

Kirk and I are discussing offline that the Top XX resources columns & query are fixed. There is no setting that'll pop in the Location column. Kirk's asked me to fire up a post about Report Writer... so I'll do that shortly.