List Aggregate with size deatils in workflow

[SURESH_SENNIAPPAN]

Hi ,

Can any one share the sql query to list the all aggregate with size details ?

when i am creating a new NFS volume through workflow i would like to find the aggregate in the drop down list with size details

Please help..

Thanks in advance...

[hill]

Hello,

You didn't specify the mode, so I'll assume you're working with Clustered Data ONTAP.

Here is an example of User Input SQL that might help:

===========================

SELECT

    CONCAT(node.name,

    ':',

    aggregate.name) AS 'Aggregate',

    aggregate.size_mb AS 'Total Size (MB)',

    aggregate.available_size_mb AS 'Available Size (MB)',

    COUNT(volume.id ) AS 'Volume Count'

FROM

    cm_storage.aggregate aggregate

LEFT JOIN

    cm_storage.volume volume

        ON volume.aggregate_id = aggregate.id

JOIN

    cm_storage.node node

        ON aggregate.node_id = node.id

JOIN

    cm_storage.cluster cluster

        ON node.cluster_id = cluster.id

WHERE

    cluster.name = '${ClusterName}'

    AND aggregate.name NOT LIKE '%aggr0%'

GROUP BY

    aggregate.id

ORDER BY

    aggregate.name ASC LIMIT 1000

========================

Note that we include the Cluster Node as part of the aggregate display name... totally up to you if you want to do it this way, include the Node as a separatecolumn... elect to not show it at all.

Hope this helps.

Best,

Kevin

[cscott]

Since Kevin provided the Clustered ONTAP version, here is a 7 mode.  Note that I require the source frame to be entered and I remove anything around root aggregates named aggr0 or vol0 and any aggregate with the "backup" in the name.  This ensures that I only see aggregates specific to one frame.  Removing my WHERE clause would open it up to any aggregate.

SELECT DISTINCT

    aggregate.name,

    ROUND(aggregate.available_size_mb /1024/1024,

    2) AS 'Available TB',

    ROUND (( aggregate.used_size_mb/aggregate.total_size_mb)*100,

    2) AS '% Used'

FROM

    storage.aggregate

JOIN

    storage.array AS array

        ON array.id = aggregate.array_id

WHERE

    array.name = '${src_array}'

    AND aggregate.name not like 'aggr0%'

    AND aggregate.name not like 'vol0'

    AND aggregate.name not like 'backup%'

ORDER BY

aggregate.name

- Scott    

[hill]

Great stuff as always Scott!

-Kevin

[SURESH_SENNIAPPAN]

Thanks for your effort scott

i m running with 7 mode and workflow version 2.2.0.2.4RC1.

The above query giving me a empty list. but when i am using below query it is listing aggregates but not size details

SELECT

    aggr.name

FROM

    storage.aggregate AS aggr,

    storage.array AS array

WHERE

    aggr.array_id = array.id

    AND (

        array.ip = '${ArrayIP}'

        OR array.name = '${ArrayIP}'

    )

If i add aggregate.available_size_mb,aggregate , used_size_mb in the SELECT column also it's not giving me anything.

Any Idea ?

[cscott]

Hi Suresh,

     In the create volume command, you enter the aggregate to use, I have mine set to "Automatically searched", with filter/finder set to "Filter aggregates by name in array".  The parameters I use are Array IP or Name*: $src_array and Aggregate Name *: $src_aggr.  This is where the '${src_array}' in my WHERE clause comes from, it is simply the syntax change for the SQL query on the same variable.  So whatever variable you are using in the Array IP or Name*: field is what you would use to filter the aggregates by the selected array.

Now I am running WFA 2.1, but I would expect this would remain the same.

- Scott

[SURESH_SENNIAPPAN]

Yes Scott you are right.

Variable is fine. but i would like to list the same aggregates with size details .

But when i using aggregate.available_size_mb,aggregate , used_size_mb in the SELECT column also it's not giving me anything .

[cscott]

My apologies Suresh,

     I completely misread the issue.  The one thing to note is that you are using aggr defined as an alias(storage.aggregate AS aggr.) I have seen where mixing alias and explicit names caused me some issues, and I had to stick with the same all the way through.

Does using this solve the issue?

    ROUND(aggr.available_size_mb /1024/1024,

    2) AS 'Available TB',

    ROUND (( aggr.used_size_mb/aggr.total_size_mb)*100,

    2) AS '% Used'