Active IQ Unified Manager Discussions

Where can I put SELECT statement into WFA?

heightsnj
14,705 Views

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

ekashpureff
14,535 Views

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
14,534 Views

 

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
14,520 Views

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
14,513 Views

 

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
14,508 Views

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
14,405 Views

 @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
14,359 Views

@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
14,320 Views

@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
14,305 Views

@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
13,653 Views
 

heightsnj
13,651 Views

 @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
13,591 Views

@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
13,558 Views

@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
13,546 Views

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
13,509 Views

@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
9,489 Views

@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
9,479 Views

@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

 

Step1 Create_New_workflow.png

 

Step2

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

 

Step2 Defining_Dictionary_Entry.png

Step 3

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

 

Step3 Select Search Criteria.png

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.

 

Step4 ResourceSelectionDetails.png

Step5

By using test button, we can achive the result.

 

Step5 ResourceSelectionTableDetails.png

 

 

Note:

 

Resource selection criteria contains 

 

equals 

not-equals

Contains

....

etc

 

As of now "Not-contains" can not be used for resource selection.

 

 

For more details, please search the topic as "how resource selection works" in below link:

 

https://library.netapp.com/ecm/ecm_download_file/ECMLP2436046

 

 

resource selection can be used from WFA 4.0 version.

 

Public