Data Infrastructure Management Software Discussions

Highlighted

Where can I put SELECT statement into WFA?

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!

17 REPLIES 17
Highlighted

Re: Where can I put SELECT statement into WFA?

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.)

Highlighted

Re: Where can I put SELECT statement into WFA?

 

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.)

Highlighted

Re: Where can I put SELECT statement into WFA?

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? 

Highlighted

Re: Where can I put SELECT statement into WFA?

 

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.)

Re: Where can I put SELECT statement into WFA?

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%'

Highlighted

Re: Where can I put SELECT statement into WFA?

 @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.

 

Highlighted

Re: Where can I put SELECT statement into WFA?

@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?

Highlighted

Re: Where can I put SELECT statement into WFA?

@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.

Highlighted

Re: Where can I put SELECT statement into WFA?

@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.

Highlighted

Re: Where can I put SELECT statement into WFA?

 
Highlighted

Re: Where can I put SELECT statement into WFA?

 @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'

 

 

Highlighted

Re: Where can I put SELECT statement into WFA?

@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.

Highlighted

Re: Where can I put SELECT statement into WFA?

@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!

Highlighted

Re: Where can I put SELECT statement into WFA?

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

Highlighted

Re: Where can I put SELECT statement into WFA?

@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.

NetApp Insights To Action
All Community Forums