Subscribe

List Aggregate with size deatils in workflow

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...

Re: List Aggregate with size deatils in workflow

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

Re: List Aggregate with size deatils in workflow

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    

Re: List Aggregate with size deatils in workflow

Great stuff as always Scott!

-Kevin

Re: List Aggregate with size deatils in workflow

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 ?

Re: List Aggregate with size deatils in workflow

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

Re: List Aggregate with size deatils in workflow

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 .

Re: List Aggregate with size deatils in workflow

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'