Active IQ Unified Manager Discussions

wfa query

Sebastiaan
3,616 Views

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

1 ACCEPTED SOLUTION

Sebastiaan
3,559 Views

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

View solution in original post

3 REPLIES 3

geringer
3,603 Views

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
3,571 Views

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
3,560 Views

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

Public