Effective December 3, NetApp adopts Microsoft’s Business-to-Customer (B2C) identity management to simplify and provide secure access to NetApp resources.
For accounts that did not pre-register (prior to Dec 3), access to your NetApp data may take up to 1 hour as your legacy NSS ID is synchronized to the new B2C identity.
To learn more, read the FAQ and watch the video.
Need assistance? Complete this form and select “Registration Issue” as the Feedback Category.

Active IQ Unified Manager Discussions

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

1 ACCEPTED SOLUTION

Sebastiaan

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

Sebastiaan

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

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)',...

 

 

Announcements
NetApp on Discord Image

We're on Discord, are you?

Live Chat, Watch Parties, and More!

Explore Banner

Meet Explore, NetApp’s digital sales platform

Engage digitally throughout the sales process, from product discovery to configuration, and handle all your post-purchase needs.

NetApp Insights to Action
I2A Banner
Public