Active IQ Unified Manager Discussions

Max Flexvol volume filter for aggregates in Cluster mode

rogerhinson
3,776 Views

Hey all,

 

I found that there is a certified Max Flexvol filter for aggregates in 7-Mode, but not for Clustered Mode.

 

I did some tweaking on the 7-Mode filter and came up with this.

 

SELECT
    aggr.name AS 'name',
    nodeName AS 'nodeName',
    nodeIP AS 'anotherIP',
    matching_arrays.nodeIp AS 'node.ip'
FROM
    cm_storage.aggregate AS aggr,
    ( SELECT
  node.id AS nodeId,
  node.name AS nodeName,
  node.primary_address AS nodeIp
FROM
  cm_storage.node
LEFT JOIN
  cm_storage.aggregate AS aggr
    ON aggr.node_id = node.id
LEFT JOIN
  cm_storage.volume AS vol
    ON vol.aggregate_id = aggr.id
    AND vol.type = 'Flexible'
GROUP BY
  vol.aggregate_id
HAVING
  COUNT(vol.id) <= ${max_flex_vol} ) AS matching_arrays
WHERE
    aggr.node_id = matching_arrays.nodeId

 

 

It works when I use Toad on the DMF database.  When I bring it into WFA and test it, I get the below error.

 

Filter 'Filter aggregates by max flexvols_copy' returned attributes does not contain all natural keys.
Filter's returned attributes '[anotherIP, name, node.ip, nodeName]' ,
dictionary entry natural keys '[node.name, name, node.cluster.primary_address]'

 

Can anyone help out?

 

Thanks,

Roger

 

 

1 ACCEPTED SOLUTION

sinhaa
3,740 Views

Roger,

 

If you read the part of the error message:

===

Filter's returned attributes '[anotherIP, name, node.ip, nodeName]' ,
dictionary entry natural keys '[node.name, name, node.cluster.primary_address]'

===

 

In your query you are selecting [anotherIP, name, node.ip, nodeName] . But the WFA filter needs at least all these: [node.name, name, node.cluster.primary_address] along with whatever you want. So your query will have to return values with heading i.e. AS node.name, name, node.cluster.primary_address along with anything else.

 

sinhaa

 

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

View solution in original post

4 REPLIES 4

geringer
3,769 Views

Roger,

 

   It looks like WFA is checking for valid return variables.  If you look in the filter editor, it looks like you are trying to return 4 variables.  The last one "matching_arrays.nodeIp" does not exist in the cm_storage dictionary, and it is not defined as a variable in your query.  I think you may have a typo in the last line of your query, you type nodeId, not nodeIp.  In any case, I am not sure why you need to return anything other than the aggr.name and the node.name or node.primary_address.  So I would remove the last two lines of the select and try that.  You may want to look at the query and return the total remaining volume counts for the nodes with each aggregate if you intend to sort the results by aggregates with the largest number of available volumes.  I am on the train, and do not have reliable access to Lab on Demand, so I can not test my theory at this time.  If you need me to tweek the query and test it, I can do that first thing tomorrow morning.  Let me know.

 

Mike

rogerhinson
3,761 Views

Hey Mike,

 

This is pretty much a copy of the 7-Mode filter to filter out aggregates with too many volumes.  We only use one aggregate per node (not counting aggr0), so it functions as a filter for too many volumes on a node.  It took me a little bit looking through it, but there are actually two SELECT statements.  The second one is part of the FROM in the first SELECT statement. 

 

The second SELECT statement result from within the FROM statement is assigned to the AS matching_arrays.  That's where the matching_arrays.nodeIp comes from.

 

I tried remove the last two lines of the first SELECT statement as received the same error about the natual keys.

 

If you could run it the lab, that would be great.  It works as I expect when I run it from Toad on the WFA Windows server directly.

 

Thanks,

Roger

 

 

 

sinhaa
3,741 Views

Roger,

 

If you read the part of the error message:

===

Filter's returned attributes '[anotherIP, name, node.ip, nodeName]' ,
dictionary entry natural keys '[node.name, name, node.cluster.primary_address]'

===

 

In your query you are selecting [anotherIP, name, node.ip, nodeName] . But the WFA filter needs at least all these: [node.name, name, node.cluster.primary_address] along with whatever you want. So your query will have to return values with heading i.e. AS node.name, name, node.cluster.primary_address along with anything else.

 

sinhaa

 

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

rogerhinson
3,710 Views

Thanks..  That did it.  It's odd that sometimes the AS will auto fill in and other times, you need to mistype it and then come back and correct it.  I made a small tweak so that it actually filters by the node rather than the aggregate. 

 

Here it is, in case it helps anyone else.

 

SELECT
    aggr.name AS 'name',
    nodeName AS 'node.name',
    matching_arrays.nodeIp AS 'node.cluster.primary_address'
FROM
    cm_storage.aggregate AS aggr,
    (
    SELECT
        node.id AS nodeId,
        node.name AS nodeName,
        node.primary_address AS nodeIp
    FROM
        cm_storage.node
    LEFT JOIN
        cm_storage.aggregate AS aggr
            ON aggr.node_id = node.id
    LEFT JOIN
        cm_storage.volume AS vol
            ON vol.aggregate_id = aggr.id
            AND vol.type = 'Flexible'
    GROUP BY
        node.id
    HAVING
        COUNT(vol.id) <= ${max_flex_vol} ) AS matching_arrays
WHERE
    aggr.node_id = matching_arrays.nodeId

 

Public