Active IQ Unified Manager Discussions

Need Aggregate Listed for the Cluster

DHARMESH18
3,928 Views

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

1 ACCEPTED SOLUTION

DHARMESH18
3,901 Views

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

View solution in original post

4 REPLIES 4

NICKBARTON
3,894 Views

@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 

DHARMESH18
3,885 Views

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.

 

DHARMESH18
3,902 Views

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

NICKBARTON
3,876 Views

@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 

Public