topic Re: Need Aggregate Listed for the Cluster in Active IQ Unified Manager Discussions

Need Aggregate Listed for the Cluster

DHARMESH18 — Wed, 04 Jun 2025 13:52:51 GMT

Hello Guys,

I am looking for an SQL Query which will populate the Aggregates in the Selected cluster only. Below is the Query that I have written but the same displays me Aggregate from all the Clusters rather than the selected one.

SELECT
aggregate.name
FROM
cm_storage.aggregate,
cm_storage.vserver,
cm_storage.cluster
WHERE
vserver.cluster_id = cluster.id
AND cluster.name = '${ClusterName}'
AND vserver.name = '${VserverName}'
ORDER BY
aggregate.name ASC

Re: Need Aggregate Listed for the Cluster

NICKBARTON — Mon, 26 Mar 2018 14:28:03 GMT

@DHARMESH18

Try the SQL query below should get you where you need to be. You don't need to reference the vserver table at all the aggregates have a relationship to nodes which have a relationship to clusters you are probably getting multiple results because of like vServer names accross your clsuters.

The below query retuns the unique aggregate names from the specified cluster name and filters out root aggregates.. If your root aggregates have a different naming convention that doesn't end with root then you can change the below NOT LIKE filter to match your naming convention.

The DISTINCT option is needed to remove repeat aggregates as it will return each aggregate name X number of times with X being the number of nodes in your cluster. Hope this gets you what need.

SELECT DISTINCT
aggr.name
FROM
cm_storage.aggregate as aggr,
cm_storage.cluster as clus,
cm_storage.node as node
WHERE
node.cluster_id = clus.id
AND
clus.name = '${ClusterName}'
AND
aggr.name NOT LIKE '%root'
ORDER BY
aggr.name ASC

Thank You,

-Nick

Re: Need Aggregate Listed for the Cluster

DHARMESH18 — Mon, 26 Mar 2018 14:53:35 GMT

Hey thanks for reverting, Nick.

The code that you shared did help in clearing me with the root aggregate with the use of NOT LIKE filter. But the code still shows me all the aggregates from each of the clusters and not the one I selected. I want the aggregates to be displayed only for the cluster that I select.

Re: Need Aggregate Listed for the Cluster

DHARMESH18 — Mon, 26 Mar 2018 15:00:22 GMT

Thanks Nick,

I did some modification and this has worked now. Below is the change,

SELECT DISTINCT
aggr.name
FROM
cm_storage.aggregate as aggr,
cm_storage.cluster as clus,
cm_storage.node as node
WHERE
aggr.node_id = node.id
AND
node.cluster_id = clus.id
AND
clus.name = '${ClusterName}'
AND
aggr.name NOT LIKE 'aggr0%'
ORDER BY
aggr.name ASC

Thanks for the Help.

Regards,

Dharmesh

Re: Need Aggregate Listed for the Cluster

NICKBARTON — Mon, 26 Mar 2018 15:11:20 GMT

@DHARMESH18

Correct sorry about that I copied over the wrong version of the SQL query I had tested in WFA database. Not sure I had that part left out but glad everything is working now.

-Nick