wfa query

[Sebastiaan]

Hi,

I want the number of luns on a volume presented in a table my current query is below but it only show one volume with all luns in the vserver instead of the luns per volume. Can some one help with the correct query.

Thanks,

Sebastiaan van den Berg

SELECT
    volume.name as Name,
    volume.size_mb as 'Total Size (MB)',
    volume.available_size_mb as 'Available Size (MB)',
    count (lun.id) AS luncount
FROM
    cm_storage.vserver,
    cm_storage.cluster,
    cm_storage.volume,
    cm_storage.lun
WHERE
    volume.vserver_id = vserver.id
    AND vserver.cluster_id = cluster.id
    AND vserver.name = '${SVMNameDCA}'
    AND cluster.name = '${ClusterNameDCA}'
    AND volume.name NOT LIKE '%root%'
    AND lun.volume_id = volume.id

ORDER BY
    volume.available_size_mb DESC

[geringer]

I am not a SQL person, but try this below

SELECT
    volume.name as Name,
    volume.size_mb as 'Total Size (MB)',
    volume.available_size_mb as 'Available Size (MB)',
    count (lun.id WHERE lun.volume_id = volume.id ) AS luncount
FROM
    cm_storage.vserver,
    cm_storage.cluster,
    cm_storage.volume,
    cm_storage.lun
WHERE
    volume.vserver_id = vserver.id
    AND vserver.cluster_id = cluster.id
    AND vserver.name = '${SVMNameDCA}'
    AND cluster.name = '${ClusterNameDCA}'
    AND volume.name NOT LIKE '%root%'

ORDER BY
    volume.available_size_mb DESC

Mike

[Sebastiaan]

Hi Mike,

Thanks for the reply i'm not an sql person to.

It gives an error

Failed to execute query for user input '$VolNameDCA':
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE lun.volume_id = volume.id ) AS luncount
FROM
cm_storage.vserver,
c' at line 4
Query is:
SELECT
volume.name as Name,
volume.size_mb as 'Total Size (MB)',
volume.available_size_mb as 'Available Size (MB)',...

[Sebastiaan · Accepted solution]

Hi,

I've contacted an sql colleague and the query had to contain an group by see below.

SELECT
    volume.name as Name,
    volume.size_mb as 'Total Size (MB)',
    volume.available_size_mb as 'Available Size (MB)',
    count (lun.id) as 'Current no LUN(s)'
FROM
    cm_storage.vserver,
    cm_storage.cluster,
    cm_storage.volume,
    cm_storage.lun
WHERE
    volume.vserver_id = vserver.id
    AND vserver.cluster_id = cluster.id
    AND vserver.name = '${SVMNameDCA}'
    AND cluster.name = '${ClusterNameDCA}'
    AND volume.name NOT LIKE '%root%'
    AND lun.volume_id = volume.id
GROUP BY
    volume.name
ORDER BY
    volume.available_size_mb DESC