Skip to content

Instantly share code, notes, and snippets.

@rverrips
Created July 7, 2014 16:33
Show Gist options
  • Save rverrips/0db42a9ae11ff0c0c52b to your computer and use it in GitHub Desktop.
Save rverrips/0db42a9ae11ff0c0c52b to your computer and use it in GitHub Desktop.
HP IMC Database query for VLANs
/** Pulls data from various tables in HP IMC 7.0 Database to:
Show all details for a specific VLAN across a campus.
Useful for sizing a migration of that VLAN to another VLAN, etc.
Change the VLan PVID on the "Where" section .. the example listes uas pv=6.
**/
SELECT [report_db].[report].[plat_v_dev].[symbol_name] AS Name,
[report_db].[report].[plat_v_dev].[dev_ip] AS IP,
[report_db].[report].[plat_v_dev].[dev_series_name] AS Series,
[report_db].[report].[plat_v_phy_if_info].[ifoptstatus] AS Status,
[report_db].[report].[plat_v_phy_if_info].[ifdesc] AS InterfaceID,
[vlanm_db].[vlan].[tbl_vlan_access_info].[pv_id] AS VLanID,
[config_db].[imc_config].[tbl_if_info].[iflastchangetime] AS LastChange
FROM [report_db].[report].[plat_v_phy_if_info]
RIGHT JOIN [config_db].[imc_config].[tbl_if_info]
ON [report_db].[report].[plat_v_phy_if_info].[dev_id] =
[config_db].[imc_config].[tbl_if_info].[dev_id]
AND [report_db].[report].[plat_v_phy_if_info].[ifindex] =
[config_db].[imc_config].[tbl_if_info].[ifindex]
LEFT JOIN [report_db].[report].[plat_v_dev]
ON [report_db].[report].[plat_v_dev].[dev_id] =
[report_db].[report].[plat_v_phy_if_info].[dev_id]
LEFT JOIN [vlanm_db].[vlan].[tbl_vlan_access_info]
ON [report_db].[report].[plat_v_phy_if_info].[dev_id] =
[vlanm_db].[vlan].[tbl_vlan_access_info].[dev_id]
AND [report_db].[report].[plat_v_phy_if_info].[ifindex] =
[vlanm_db].[vlan].[tbl_vlan_access_info].[if_index]
WHERE [vlanm_db].[vlan].[tbl_vlan_access_info].[pv_id] = 6
ORDER BY [report_db].[report].[plat_v_phy_if_info].[ifoptstatus],
[report_db].[report].[plat_v_phy_if_info].[downday],
[report_db].[report].[plat_v_phy_if_info].[dev_id],
[report_db].[report].[plat_v_phy_if_info].[ifindex]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment