Data Infrastructure Management Software Discussions

Re: Compiling quota total commits to volume to then select Volume

Joele,

 

Yes, that would also be helpful.  We will share ours shortly and see what optimizations can be done.

 

We have figured out the reservation cache as well, just need to adjust our query

Re: Compiling quota total commits to volume to then select Volume

Here's the query for the user input variable I've been using:

 

SELECT
    volume.name AS 'volume',
    ROUND (( (volume.size_mb * ((100 - volume.snapshot_reserved_percent) / 100)) / 1024 ),0 ) AS 'Total Volume Size (GB)',
    ROUND ((volume.used_size_mb / 1024),0) AS 'Used Volume Size (GB)',
    ROUND ((volume.available_size_mb / 1024),0) AS 'Available Volume Size (GB)',
    COUNT(qtree.id) AS 'Qtree Count',
    ROUND ((SUM(qtree.disk_limit_mb) / 1024),0) AS 'Quota Allocated (GB)'
FROM
    cm_storage.volume as volume,
    cm_storage.vserver as vserver,
    cm_storage.cluster as cluster,
    cm_storage.qtree as qtree
WHERE
    qtree.volume_id = volume.id
    AND volume.vserver_id = vserver.id
    AND vserver.cluster_id = cluster.id
    AND qtree.name != ''
    AND volume.name NOT LIKE '%_root%'
    AND vserver.name = '${VserverName}'
    AND (
        cluster.name = '${ClusterName}'
        OR cluster.primary_address = '${ClusterName}'
    )
GROUP BY
    volume.id
ORDER BY
    SUM(qtree.disk_limit_mb)

 

 

Curious to see what you've been working on around the cache query.  Have a good one!

Re: Compiling quota total commits to volume to then select Volume

Ok, here is our query the team has wrote, and could use some advice on this.

 

SELECT
    cv.id,
    cv.name AS 'name',
    cv.used_size_mb AS 'used',
    cv.size_mb AS 'total_size',
    vserver.name AS 'vserver.name',
    cluster.primary_address AS 'vserver.cluster.primary_address',
    (((SELECT
        sum(qtree.disk_limit_mb)                                                                                
    FROM
        cm_storage.qtree                                                                                          
    WHERE
        qtree.volume_id = cv.id )/cv.size_mb)*.5 + (cv.used_size_mb/cv.size_mb)) AS 'Averaged'        
FROM
    cm_storage.volume cv,
    cm_storage.vserver,
    cm_storage.cluster                               
WHERE
    cv.security_style = '${security_style}'                                                   
    AND cv.used_size_mb / cv.size_mb < .90                                                      
    AND cv.name LIKE '${VolPrefix}%'                                                   
    AND vserver.id = cv.vserver_id                                                                                     
    AND cluster.id = vserver.cluster_id                                                                                     
    AND vserver.name NOT LIKE '%_dr'          
    AND          (
        cv.size_mb * 2 > (
            SELECT
                sum(qtree.disk_limit_mb)                                                                                                                            
            FROM
                cm_storage.qtree                                                                                                                                      
            WHERE
                qtree.volume_id = cv.id 
        )     
        OR cv.used_size_mb = 0
    ) 
ORDER BY
    Averaged ASC        

We have to do same subquery 2x to reference.  

 

 

For reservations we had to go to commands we defined mandatory variables 

 

  [parameter(Mandatory=$false, HelpMessage="Disk Limit")]
  [long]$disk_limit_mb,

  [parameter(Mandatory=$false, HelpMessage="Disk Soft Limit")]
  [long]$disk_soft_limit_mb

Added parameter defintions, add parameter mappings, and then in reservation tab

added this

'${disk_limit_mb}' AS disk_limit_mb, -- Default value for disk limit, soft disk limit and filelimit is 0
'${disk_soft_limit_mb}' AS disk_soft_limit_mb,

 

Now that updates the reservation table.   

 

Thanks for checking our query

Re: Compiling quota total commits to volume to then select Volume

Hi @joele

 

can you share ther query you used for this table ?

 

G

Gidi Marcus (Linkedin) - Storage and Microsoft technologies consultant - Hydro IT LTD - UK

Re: Compiling quota total commits to volume to then select Volume

Hi Gidon,

 

 

The SQL query syntax is a few posts up for that table, here's a direct link:

 

https://community.netapp.com/t5/OnCommand-Storage-Management-Software-Discussions/Compiling-quota-total-commits-to-volume-to-then-select-Volume/m-p/13...

 

Hope this helps and let me know if you have questions.

 

Joel

Forums