Need help with SWQL script to display all the Cisco switches VLAN 512 interface IP and its subnet mask.

PeterJulian

Need help with SWQL script to display all the Cisco switches VLAN 512 interface IP and its subnet mask. I used the below script but that just displays only few of the switches in the report

SELECT
ncm.NodeCaption AS [Hostname],
ncm.Vendor AS [Vendor],
ncm.DeviceType AS [Device Type],
i.InterfaceName AS [Interface],
i.VlanID AS [VLAN ID],
i.InterfaceAlias AS [Description],
ip.IPAddress AS [IP Address],
ip.SubnetMask AS [SubnetMask]
FROM
NCM.Interfaces AS i
JOIN
NCM.IPAddresses AS ip ON i.InterfaceID = ip.InterfaceID
JOIN
NCM.Nodes AS ncm ON i.NodeID = ncm.NodeID
WHERE
i.InterfaceName LIKE 'Vlan512'
AND ncm.Vendor = 'Cisco'
AND ncm.DeviceType = 'Switch'

stuartd

I had to change your custom property to match one of ours.

Then as a test I rem'd out the WHERE i.interfaceName LIKE 'Vlan48' and got a lot of results. So, at least we know the base script works. 

Now, I am no S(W)QL expert by any stretch, but I wonder if the use of LIKE and no %'s is somehow confusing the logic. You could try instead = 'Vlan512'. I then tried substituting your 512 with one of our VLans and got  hits, so it isn't that. However, it might be worth trying.

I think initially, your first check should be to re-write the WHERE lines to either:

WHERE ncm.Vendor = 'Cisco'
AND ncm.Hardware_Type = 'Switch'

OR

WHERE
--i.InterfaceName LIKE 'Vlan48'
--AND 
ncm.Vendor = 'Cisco'
AND ncm.Hardware_Type = 'Switch'

Then see if your script runs [it does here] and adjust backwards from there.

Final thought: I note that a number of ours report back as either Vl512 or Vlan512 - so if you run without the LIKE/= line on 512 and check that you actually have entries showing up as Vlan512 first.

HTH?