OCI DWH Query Count of LUNs Per Host Server

 A NetApp OnCommand Insight DataWarehouse MySQL query for when you want to find out the number of LUNs per host server. It takes into account that host servers will have multiple paths on the Fibre Channel Fabric.

SELECT
    h.id,
    h.name,
    h.identifier,
    h.ip,
    h.os,
    count(*)
FROM
    ( -- WE HAVE MULTIPLE PATHS PER LUNS --
    SELECT hostid,lun
    FROM physical_path AS p
    WHERE hostid IS NOT NULL
    GROUP BY hostid,lun
    ) AS t0
JOIN HOST AS h ON h.id = t0.hostid
GROUP BY h.id;