Custom Query code (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN56') OR

galenwollenberg

Hi I have a custom script that works great.

I need to edit it to only alert on certain vlan subnets.

can someone point me in the right direction about how to filter out a few certain DHCP subnets?

working alert query code:

*************************************************************************

SELECT IPAM_GroupReportView.FriendlyName, IPAM_GroupReportView.GroupId FROM IPAM_GroupReportView

WHERE

(

(IPAM_GroupReportView.PercentUsed >= 90) AND

(IPAM_GroupReportView.GroupType = 'DHCP Scope')

)

**************************************************************************

I am trying to filter out some DHCP subnets from the alert, something like this:

Code not working:

************************************************

SELECT IPAM_GroupReportView.FriendlyName, IPAM_GroupReportView.GroupId FROM IPAM_GroupReportView

WHERE

(

(IPAM_GroupReportView.PercentUsed >= 90) AND

(IPAM_GroupReportView.GroupType = 'DHCP Scope') AND

(IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN56') OR

(IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN57') OR

(IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN58') OR

(IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN59') OR

(IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN60') OR

(IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN61') OR

(IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN62') OR

(IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN63') OR

)

So clearly, IPAM_GroupReportView.FriendlyName is incorrect, what should I use instead?

thanks for your time.

dgsmith80

Looks like some issues with your SQL - I'm going to assume you just want those VLAN's listed, but if you are trying to exclude those VLAN's you can change the = to != which means Does Not Equal

SELECT IPAM_GroupReportView.FriendlyName, IPAM_GroupReportView.GroupId FROM IPAM_GroupReportViewWHERE( ( IPAM_GroupReportView.GroupType = 'DHCP Scope' ) AND  ( IPAM_GroupReportView.PercentUsed >= '90' ) AND   (    (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN56') OR    (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN57') OR    (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN58') OR    (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN59') OR    (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN60') OR    (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN61') OR    (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN62') OR    (IPAM_GroupReportView.FriendlyName = 'somecorp wireless VLAN63') OR   ))