Re: Where can I put SELECT statement into WFA?

heightsnj · ‎2017-03-28

All,

For a simple example, if I want to use a SELECT to populate all aggretaes, where and how can I put this statement into WFA?

I can see all SELECT's in Filters. In Design, when I build a WFA, I can put variable, or constant. I guess, my questions how can I add Filters in the process here?

Please help me out, step by step in details.

Thanks!

ekashpureff · ‎2017-03-28

Heights -

Click on Filters, then click on the green + to create a new filter...

I hope this response has been helpful to you.

At your service,

Eugene E. Kashpureff, Sr.
Independent NetApp Consultant http://www.linkedin.com/in/eugenekashpureff
Senior NetApp Instructor, FastLane US http://www.fastlaneus.com/
(P.S. I appreciate 'kudos' on any helpful posts.)

ekashpureff · ‎2017-03-28

Heights -

P.S.

I'll be teaching 'Creating Custom Workflows with WFA' on-line next week on the 6th and 7th.

Would love to see you in class !

See http://www.fastlaneus.com/netapp-training

I hope this response has been helpful to you.

At your service,

Eugene E. Kashpureff, Sr.
Independent NetApp Consultant http://www.linkedin.com/in/eugenekashpureff
Senior NetApp Instructor, FastLane US http://www.fastlaneus.com/
(P.S. I appreciate 'kudos' on any helpful posts.)

heightsnj · ‎2017-03-28

Thanks for the message.

If I wanted to create a SELECT statement to select aggregates with SAS (not SATA) type only AND within a cluster XYZ, what should SELECT statement be?

ekashpureff · ‎2017-03-28

Heights -

You select the filter when filling out the input for a given command in a workflow.

For instance on the 'Create Volume' command you mouse over the aggregate field and then click the elipses ( ... ) to go to the resource selection screen where you can then select a filter or finder.

Again - would love to have you join us in the WFA class on WebEx next week !

There are also a couple of WFA web based classes you might want to check out on Learning Center:

https://learningcenter.netapp.com/

I hope this response has been helpful to you.

At your service,

Eugene E. Kashpureff, Sr.
Independent NetApp Consultant http://www.linkedin.com/in/eugenekashpureff
Senior NetApp Instructor, FastLane US http://www.fastlaneus.com/
(P.S. I appreciate 'kudos' on any helpful posts.)

heightsnj · ‎2017-03-28

Thanks!

For the following SELECT statement, type as "aggregate". What did it go wrong? when I test it, it allows me to enter the cluster name, then got the error message shown as attached. Please correct.

SELECT
aggregate.name,
aggregate.available_size_mb
FROM
cm_storage.cluster,
cm_storage.node,
cm_storage.aggregate
WHERE
cluster.name = '${ClusterName}'
AND node.cluster_id = cluster.id
AND aggregate.node_id = node.id
AND aggregate.name NOT LIKE '%aggr0%'
AND aggregate.name NOT LIKE '%sata%'

sivakumar_sekar · ‎2017-03-29

@heightsnj

For writing filter query, we need to add natural keys of tables which are used.
Please Use below modified SQL query:

SELECT
aggregate.name,
node.name AS 'node.name',
cluster.primary_address AS 'node.cluster.primary_address' ,
aggregate.available_size_mb
FROM
cm_storage.aggregate
JOIN
cm_storage.node AS node
ON aggregate.node_id=node.id
JOIN
cm_storage.cluster AS cluster
ON node.cluster_id =cluster.id
WHERE
cluster.name = 'hrscluster-4'
AND aggregate.name NOT LIKE '%aggr0%'
AND aggregate.name NOT LIKE '%sata%'

Hope this sloves your issue.

netappmagic · ‎2017-03-29

@sivakumar_sekar, Thanks!

I copied your code, only change I made is the name of the cluster. However, got the similar errors as attached below, after I click on "test"

Any idea on why?

sinhaa · ‎2017-03-29

@heightsnj

Your original query was fine except for the little miss. See the error message carefully. Its same like below.

--------

Filter 'my aggr filter' returned attributes does not contain all natural keys.

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

-------

As you can see your SELECT query is only selecting available_size_mb, name but the natural keys for the filter of the given type aggregate on cm_storage needs node.cluster.primary_address, name, node.name as well in the SELECT columns.

So now your select statement should have the columns as required as per the natural key.

The right query is:

SELECT
aggregate.name AS 'name',
aggregate.available_size_mb,
node.name  AS 'node.name',
cluster.primary_address AS 'node.cluster.primary_address'

FROM
cm_storage.cluster,
cm_storage.node,
cm_storage.aggregate
WHERE
cluster.name = '${ClusterName}'
AND node.cluster_id = cluster.id
AND aggregate.node_id = node.id
AND aggregate.name NOT LIKE '%aggr0%'
AND aggregate.name NOT LIKE '%sata%'

Now my SELECT columns have all the required natural keys for this filter.

Your filter is READY.

For help in creating filters, you can see some of the provided canned filters of a given type.

sinhaa

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

sinhaa · ‎2017-03-30

@heightsnj

WFA has constantly been trying to reduce the effort in creating workflows and simplifying things for its users thereby reducing the learning curve.

If you use WFA4.0 + , WFA has provided Criteria builder for resource selection feature for your help. So now you don't even need to create filters. Its very easy and works from simple to moderate level usecases. ( For complex use cases you would need Filters ).

If you tell me which command are you trying in your workflow where you are selecting this aggregates, I can use this example and tell you how to proceed without creating filters using the criteria builder.

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

heightsnj · ‎2017-03-30

heightsnj · ‎2017-03-30

@sinhaa

the modified SELECT works! Thank you!

A follow-up question:

>>>As you can see your SELECT query is only selecting available_size_mb, name but the natural

>>>keys for the filter of the given type aggregate on cm_storage needs node.cluster.primary_address,

>>>name, node.name as well in the SELECT columns.

How do I know if a filter needs the natural keys, and what are they?

I guess, what I still dont' understand is that why I have to include each one of following columns in the query, and as such format/syntax? Cann't I replace "node.name" with "aggregate.node"?

aggregate.name AS 'name',
aggregate.available_size_mb,
node.name  AS 'node.name',
cluster.primary_address AS 'node.cluster.primary_address'

sinhaa · ‎2017-03-31

@heightsnj

How do I know if a filter needs the natural keys, and what are they?

--------------

Every filter always needs you to select all the natural keys for the given filter type.

In your example type aggregate cm_storage

If you go to Designer -> dictionary and locate the dictionary aggregate scheme cm_storage, you can see what are its natural keys.

Its name , reference -> cm_storage.Node . Now look to resolve the reference

For dictionary cm_storage.Node, the natural keys are Node.name and ref-> cm_storage.Cluster

Similarly resolving the ref for cm_storage.Cluster which has natural key node.cluster.primary_address

The above manual way to look for the natual keys is too cumbersome to try. WFA till now hasn't provided a good way to know the which are the natural keys to be included in the filter and in what format. Currently you can only know by the way of failure as you did in your case. Have any select statement and do test, failure message will tell you wnat all you need.

In future we have plans to make it better.

I still dont' understand is that why I have to include each one of following columns in the query, and as such format/syntax? Cann't I replace "node.name" with "aggregate.node"?

------------------

I think the above should help you understand why you can't replace node.name with aggregate.node. Columns in WFA dictionary ( DB tables ) can have foreign keys which are referencing to objects in other dictionary ( tables).

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

heightsnj · ‎2017-04-03

@sinhaa

I still could not fully understand the manual way you described. In any event, the error message indicated to me to include "node.cluster.primary_address" and "node.name" as part of natural keys. ayou suggested,by using this way, I can find what needs to be included from there wiithout fully understanding the manual way. What about "name" here? In the error message, it only told me to use "name". It didn't say what refernce I have to use. So, again, why cannot I use "aggregate.name", "cluster.name", or even "node.name", since it didn't say what kind of name I should use?

Also, how do I know that I have to use AS 'name', AS 'node.name', and AS 'node.cluster.primary_address'? Can I just drop off all these 3 AS's?

Foreign keys are able to have two tables joined together, if I understand correctly. Are foreign keys equvalent to natural keys, conceptually ?

I know you have tried a lot to help me out. Thank you!

abhit · ‎2017-04-03

natural key = attributes which are used to uniquely identify an object. This concept is used in WFA.

You can see it in the dictionary entries for a particular object type, like Volume, SVM etc.

The attributes which are used to uniquely identify an object is ticked.

Regards

Abhi

sinhaa · ‎2017-04-03

@heightsnj

What about "name" here? In the error message, it only told me to use "name". It didn't say what refernce I have to use. So, again, why cannot I use "aggregate.name", "cluster.name", or even "node.name", since it didn't say what kind of name I should use?

-------

The required natural keys have to be the column headings in your SQL query. 'name' is the natural key for the aggrgate itself, so there is no referance and hence its resolution not required. The other natural key is ref->cm_storage.node and hence that needs to be resolved. The dictionary (table) node has natural keys 'name' and ref->cm_storage.Cluster. The dictionary cm_storage.Cluster has natural key primary_address.

So the overall natural keys for the aggregate are 'name', 'node.name', 'node.cluster.primary_address'

And so your SELECT statement need to hav columns with the above 3 headings. If you use aggrgate.name then the column heading in result will be aggrgate.name and WFA is expecting name. So this fails.

I hope you its clear now.

sinhaa

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

heightsnj · ‎2017-04-05

@sinhaa

I can kind of understand the process, but still it seems there are no definite rules I can follow. It may come to the point, I need to have more practices, and then find out the regularity.

Thanks a lot!

sivakumar_sekar · ‎2017-04-05

@heightsnj,

Along with Sinha solution another way to filter the resource using resource selection and below is the details:

Step1

Create new workflow and add "search and define" command need to be added in workflow

Step2

Using "content tab" define the dictionary entry.
Here please select your dictionary entry to filter the resource.

Step 3

Please select "enter search Criteria" to add the resource selection details

Step 4

select "the filter aggregate that match the following rules" check box to add the rules for resource selection
here you can make aggregate name rule and type of the aggregate and etc.