Who's the Man Blog

One of the issues in documentation is being able to leverage existing data stored in management systems. A good example of this is in the HP IMC product it can use SQL as a backend which allows for data to be pulled out is you know how to formulate the queries. Below is a example of the query to export all the VLANs IMC is aware of using two SQL views. The first view is a custom query specific to your environment and requires knowing you core L3 routing devices so that you can generate a report of only those devices Layer 3 routing ports in my case this is the four switches that have the device id’s of 1153, 1174, 1179, and 1278 in the database config_db and table tbl_dev.

—————————–

CREATE
VIEW [dbo].[vlaninfo_L3]

AS

SELECT
TOP (100) PERCENT vlan.tbl_vlan_virtual_if_info.vlan_id, vlan.tbl_vlan_overall_vlan.vlan_name, config_db.imc_config.tbl_snmp_sysinfo.sys_name,

vlan.tbl_vlan_virtual_if_info.ip_addr, vlan.tbl_vlan_virtual_if_info.ip_mask, vlan.tbl_vlan_overall_vlan.has_routing_port

FROM vlan.tbl_vlan_overall_vlan LEFT
OUTER
JOIN

vlan.tbl_vlan_virtual_if_info INNER
JOIN

config_db.imc_config.tbl_dev ON vlan.tbl_vlan_virtual_if_info.dev_id = config_db.imc_config.tbl_dev.dev_id INNER
JOIN

config_db.imc_config.tbl_snmp_sysinfo ON vlan.tbl_vlan_virtual_if_info.dev_id = config_db.imc_config.tbl_snmp_sysinfo.dev_id ON

vlan.tbl_vlan_overall_vlan.vlan_id = vlan.tbl_vlan_virtual_if_info.vlan_id

WHERE (config_db.imc_config.tbl_dev.dev_id = 1153)
OR

(config_db.imc_config.tbl_dev.dev_id = 1174)
OR

(config_db.imc_config.tbl_dev.dev_id = 1179)
OR

(config_db.imc_config.tbl_dev.dev_id = 1278)

ORDER
BY vlan.tbl_vlan_virtual_if_info.vlan_id

——————

 

 

The second SQL view is a union of all vlan information and the Layer 3 (routed vlan) information.

 

——————

SELECT vlan.tbl_vlan_overall_vlan.vlan_id AS Number, vlan.tbl_vlan_overall_vlan.vlan_name AS Name, vlan.tbl_vlan_overall_vlan.has_routing_port AS [Layer 3],

dbo.vlaninfo_L3.sys_name AS Switch, dbo.vlaninfo_L3.ip_addr AS [Default Gateway], dbo.vlaninfo_L3.ip_mask AS [Subnet Mask]

FROM dbo.vlaninfo_L3 RIGHT
OUTER
JOIN

vlan.tbl_vlan_overall_vlan ON dbo.vlaninfo_L3.vlan_id = vlan.tbl_vlan_overall_vlan.vlan_id

——————

 

 

 

 

 

 

For vlans with multiple default gateways it will show as multiple entries with the same vlan number.