topic Re: List Aggregate with size deatils in workflow in Active IQ Unified Manager Discussions

List Aggregate with size deatils in workflow

SURESH_SENNIAPPAN — Thu, 05 Jun 2025 05:39:10 GMT

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

hill — Mon, 31 Mar 2014 15:16:56 GMT

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

cscott — Mon, 31 Mar 2014 18:37:28 GMT

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

hill — Mon, 31 Mar 2014 23:03:13 GMT

Great stuff as always Scott!

-Kevin

Re: List Aggregate with size deatils in workflow

SURESH_SENNIAPPAN — Tue, 01 Apr 2014 01:36:20 GMT

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

cscott — Tue, 01 Apr 2014 02:35:25 GMT

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

SURESH_SENNIAPPAN — Tue, 01 Apr 2014 02:49:55 GMT

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

cscott — Tue, 01 Apr 2014 12:35:54 GMT

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'