How to create export rules and add reservations while using the row repeat feature?

[ninja]

Any ideas on creating export rules and adding the reservations as it iterates through a row repeat? The certified "Create export rule" command doesn't have any reservations.

Can you update the WFA DB directly from a command such as Invoke-MySqlQuery and do an insert/update? If yes I'll just go that route.

[cbauernf · Accepted solution]

ninja,

the cleanest way would be to clone the certified command step and add your own reservation code.  If you're comfortable with invoking SQL statements from within the command text, this should not be overly complicated.

I've looked at this before, and the problem I found was with the rule index.  When no rule index is specified, the powershell version of the command will insert the new rule as the first in the policy, and the perl version as the last.  That made it a bit difficult to predict the correct rule index for the reservation.  That and the fact that Ontap will allow multiple rules for the same clientmatch makes it difficult to guarantee that the reservation will create a correct entry, and in turn that the verification will match it up with a table record during OCUM aquisition.  You'll have to set some ground rules in your environment to ensure that the WFA database will not go out of sync with the cluster, e.g. no rules updates through system manager or the command line, always specify a rule index etc.

If you want to go down that route, here's some SQL that implements the always-last semantics:

INSERT
INTO
    cm_storage.export_rule
    SELECT
        NULL AS id,
        '${ClientMatch}' AS clientmatch,
        export_policy.id AS policy_id,
        '${Protocol}' AS protocol,
        '${RoRule}' AS ro_rule,
        IF ('${RuleIndex}' <>'',
        '${RuleIndex}',
        IF ( (
        SELECT
            COUNT(id)
        FROM
            cm_storage.export_rule
        WHERE
            export_rule.policy_id = export_policy.id) > 0,
        (
        SELECT
            MAX(rule_index) + 1
        FROM
            cm_storage.export_rule
        WHERE
            export_rule.policy_id = export_policy.id),
        1 ) ) AS rule_index,
        '${RwRule}' AS rw_rule,
        '${Superuser}' AS super_user
    FROM
        cm_storage.export_policy,
        cm_storage.vserver,
        cm_storage.cluster
    WHERE
        export_policy.name = '${PolicyName}'
        AND export_policy.vserver_id = vserver.id
        AND vserver.name = '${VserverName}'
        AND vserver.cluster_id = cluster.id
        AND (
            cluster.name = '${Cluster}'
            OR cluster.primary_address = '${Cluster}'
        );

Hope this helps,

Christian

[ninja]

Thanks for the quick reply. What I'm looking for is similar but I'm using the row repeat feature in the workflow. Which means the workflow will run one or multiple times depending on how many the user chooses in the workflow. See the image below:

 

So in otherwords I don't have a single variable to use in the sql statement to do the insert. Each time it runs the client match will be different so the field has to run a function to lookup the index and pull out the client name. Here is an example of the field and I can't put this into sql unless you know of a way to do it. '$ExportRuleSpecification==''?'':getValueAt2D($ExportRuleSpecification,ExportRuleLoopIndex,1)'

This also applies to the RO, RW, SUPER, and protocol fields that you have. They will always change when the workflow is iterating. 

This is why I wanted to see if there was a way to do an insert from powershell with the sql cmdlet. If I use that I would have to do anything in the reservations tab and not worry about the iteration.

Does Invoke-MySqlQuery allow an INSERT?

I'm open to any other ideas.

[cbauernf]

It should work just the same.  The variables in the reservation statement are command parameters (look at the left column of the "Parameter Mapping" tab), not user inputs.  They will be assigned the correct values on every iteration.  (Think about it, if they did not contain the correct values, the command block would not create the correct rule.)

Christian

[ninja]

Ok so you're saying I can uses these variable from the sql query in the reservations tab? Do I prefix them with $ just like I would with a user-defined one? If that's the case I'm good to go.

[cbauernf]

Exactly. You use '${Variable}' in the reservation code the same way you would use $Variable in the perl or PS code.

Christian

[ninja]

I'll give it a try and let you know thanks.

[ninja]

Worked well. I used perl for the custom command and added the reservation. Thank you.