Filter with Natural Key error

[sheelnidhig]

Hello Guys,

I am trying to write a filter where in i would like to get the list of all the qtree within the volume together with the Logical interface address from the same node's ports on which the volume is hosted:

Select
cm_storage.qtree.name As 'QtreeName',
cm_storage.volume.name As 'VolumeName',
(Select
group_concat(distinct(cm_storage.logical_interface.address)) as 'Address'
From
cm_storage.volume,
cm_storage.aggregate,
cm_storage.node,
cm_storage.port,
cm_storage.logical_interface,
cm_storage.vserver
Where
cm_storage.volume.vserver_id = cm_storage.vserver.id And
cm_storage.logical_interface.vserver_id = cm_storage.vserver.id And
cm_storage.logical_interface.home_port_id = cm_storage.port.id and
cm_storage.port.node_id = cm_storage.node.id and
cm_storage.aggregate.node_id = cm_storage.node.id and
cm_storage.volume.aggregate_id = cm_storage.aggregate.id and
cm_storage.logical_interface.name like "%-%" and
cm_storage.volume.name Like '{volume_name}') as 'Address'
From
cm_storage.volume,
cm_storage.vserver,
cm_storage.qtree
Where
cm_storage.volume.vserver_id = cm_storage.vserver.id And
cm_storage.qtree.volume_id = cm_storage.volume.id And
cm_storage.volume.name Like '{volume_name}'

This query is working well outside but when i put this in a filter it asks me to all the natural keys, and if i add all the natural keys, I would not be able to get the consolidate output like this:

QtreeName VolumeName Address
NULL volume1 10.10.10.1,10.10.20.1
qtree1 volume1 10.10.10.1,10.10.20.1
qtree2 volume1 10.10.10.1,10.10.20.1
qtree3 volume1 10.10.10.1,10.10.20.1

This give me all the lifs within the vserver that have home port on one node.

is there any other way to write this SQL Query?

,Sheel

[coreywanless]

Sheel,

What you have to keep in mind with a filter, is that WFA is trying to associate your selected columns with actual database columns. So the names chosen in the select statement need to be exactly what WFA wants them to be.

Below is an example error. I purposely caused this error by making one of the select variables to be called node.namedd instead of node.name.  You will then see in the error messasge, a list of required names to be returned.

"Filter 'Filter aggregates by delegation to Storage Virtual Machine' returned attributes does not contain all natural keys. Filter's returned attributes '[name, node.cluster.primary_address, node.namedd]' , dictionary entry natural keys '[node.cluster.primary_address, name, node.name]"

Here was the select part of my statement:

SELECT
aggr.name,
node.name AS 'node.namedd',
cluster.primary_address AS 'node.cluster.primary_address'

The correct statement:

SELECT
aggr.name,
node.name AS 'node.name',
cluster.primary_address AS 'node.cluster.primary_address'

I hit this error 90% of the time, but once I understand the natural keys, can quickly change the SQL Query around to fit what they are asking for.

For reference, check out one of the certified filters. I used 'Filter aggregates by delegation to Storage Virtual Machine' in this post.