[NetApp OCI DWH] Matching ESXi Host Name to vCenter and OCI Server

If you need to see which vCenter and OCI Server (where you have multiple NetApp OnCommand Insight servers) for a specified list of ESXi hosts, you can do something like this.

SELECT
h.`name`,
hvs.`victualCenterIP` AS 'vCenter',
CASE
  WHEN h.`url` LIKE '%{some string 1}%' THEN '{OCI 1}'
  WHEN h.`url` LIKE '%{some string 2}%' THEN '{OCI 2}'
  -- ETC --
  ELSE 'TBC'
END AS 'OCI Server'
FROM(
SELECT '{ESXi host 1}' AS `host`
UNION SELECT '{ESXi host 2}'
UNION SELECT '{ESXi host 3}'
-- ETC --
) AS t0
JOIN dwh_inventory.`host` AS h ON h.`name` = t0.`host`
JOIN dwh_inventory.`hv_server` AS hvs ON hvs.`hostId` = h.`id`