Subscribe

Where can I put SELECT statement into WFA?

[ Edited ]

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!

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

Re: Where can I put SELECT statement into WFA?

[ Edited ]

 

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

Re: Where can I put SELECT statement into WFA?

[ Edited ]

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? 

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?

[ Edited ]

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

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.

 

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?

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.

Re: Where can I put SELECT statement into WFA?

[ Edited ]

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