Filter with Natural Key error

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 As 'QtreeName', As 'VolumeName',
group_concat(distinct(cm_storage.logical_interface.address)) as 'Address'
cm_storage.volume.vserver_id = And
cm_storage.logical_interface.vserver_id = And
cm_storage.logical_interface.home_port_id = and
cm_storage.port.node_id = and
cm_storage.aggregate.node_id = and
cm_storage.volume.aggregate_id = and like "%-%" and Like '{volume_name}') as 'Address'
cm_storage.volume.vserver_id = And
cm_storage.qtree.volume_id = And 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,
qtree1 volume1,
qtree2 volume1,
qtree3 volume1,


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?



Re: Filter with Natural Key error



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  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,]"


Here was the select part of my statement:

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


The correct statement:

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.