nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

WFA Filter that give the first available volume name

Berra

I’m trying to create a WFA filter that will give me the first available volume name with the lowest possible number . The already existing filters in WFA only gives the next available volume name/number after the existing ones. Let’s say that their already exist volumes with names like vol01, vol02, vol04, vol05 and vol06. The existing filters, for example the filter: “Filter volumes by name and numeric suffix in a specified Storage Virtual Machine”, will give the next volume name= vol07. Instead I would like to have the next volume name with the lowest possible number, that will be in this example= vol03 as there is a gap between vol02 and vol04.
Does any wfa/sql hacker has some good examples that could solve this problem for me?

Find more posts tagged with

Workflow Automation (WFA)

Active IQ Unified Manager

Accepted answers

Berra

Hi Christian
This was exactly what I was looking for so this will help me a lot.
Thank you very much Christian for your help and also thank you Matt that previously gave me so much help and good ideas of how to solve this question
Regards
Berra

All comments

mbeattie

Hi Berra,

Thanks for posting this question, i think it's an interesting requirement and problem to solve. It certainly would be generically useful to help customers maintain and enforce a clean and concurrent naming standards in thier environment. I do have some ideas on how to solve it (just need to get the MVEL\Java syntax working).

As an example i created a few igroups and developed SQL query to return the name and number. EG if i've understood your requirement correctly in this case you would want WFA to return number 25 (not increment the number to 27)?

I'm not aware of any native method to return the missing record in mysql (as a query only returns records that exist, not ones that dont). It's possible to create a temporary table, stored proceedure and compare records numbers but it's a lot of messing around and not something you could do natively in a query so i came up with another idea...

How about using an MVEL function to invoke a SQL query, iterate through the recordset and calculate the next non-concurrent record? It is possible. EG:

function get_igroup(cluster, vserver, prefix, count){
   import java.sql.*;
   import java.util.*;
   String query= 'SELECT CAST(RIGHT(igroup.name, ' + count +') AS UNSIGNED)  AS igroup_number FROM cm_storage.igroup, cm_storage.vserver, cm_storage.cluster WHERE vserver.cluster_id = cluster.id AND igroup.vserver_id = vserver.id AND igroup.name LIKE \'' + prefix + '%\' AND RIGHT(igroup.name,  ' + count +') AND vserver.name = \'' + vserver + '\' AND (cluster.primary_address =  \'' + cluster + '\'  OR cluster.name =  \'' + cluster + '\' ) ORDER BY igroup.NAME ASC';
   //Connect to local WFA DB using the built-in credentials
   Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/","wfa","Wfa123");
   Statement stmt=con.createStatement();  
   ResultSet rs=stmt.executeQuery(query);  
   int c = rs.getRow();
   int i = 0;
   String s=; 
   Hashtable numbers = new Hashtable(c);
   while(rs.next()) {
      s = rs.getString('igroup_number');
      int n = Integer.parseInt(s);
      numbers.put(rs.getRow(), n)
   }
   return numbers;
}

This will return the igroups (record index and igroup number) as a hashtable. EG

All that i need to add is the logic to return the first non-concurrent number in the sequence. EG in PowerShell the programming logic would be:

[Array]$numbers = @(22,23,24,26)
For($i = 0; ($i -le $numbers.GetUpperBound(0) -1); $i++){
   Write-Host $numbers[$i]
   If(($numbers[$i + 1] - $numbers[$i]) -ne 1){
      Write-Host ($numbers[$i] + 1) -ForegroundColor Green
      Break;
   }
   If($i -eq $numbers.GetUpperBound(0) -1){
      Write-Host ($numbers[$i] + 2) -ForegroundColor Magenta
   }
}

That will exit the loop at number 25. Alternately if all records were concurrent it will increment the number to 27. EG

[Array]$numbers = @(22,23,24,25,26)
For($i = 0; ($i -le $numbers.GetUpperBound(0) -1); $i++){
   Write-Host $numbers[$i]
   If(($numbers[$i + 1] - $numbers[$i]) -ne 1){
      Write-Host ($numbers[$i] + 1) -ForegroundColor Green
      Break;
   }
   If($i -eq $numbers.GetUpperBound(0) -1){
      Write-Host ($numbers[$i] + 2) -ForegroundColor Magenta
   }
}

All that is required is to add that programming logic in Java syntax within the MVEL function but I should be able to provide you an example to work with. I'll post it once i've got it working.

Note: Obviously the same logic\programming can be applied any object type (EG volume) it's just a matter of updating the SQL query in the MVEL function.

/Matt

mbeattie

Hi Berra,

I got this working. Here is the code as an example MVEL function:

function get_igroup(cluster, vserver, prefix, count){
   import java.sql.*;
   import java.util.ArrayList;
   String query= 'SELECT igroup.name, CAST(RIGHT(igroup.name, ' + count +') AS UNSIGNED)  AS igroup_number FROM cm_storage.igroup, cm_storage.vserver, cm_storage.cluster WHERE vserver.cluster_id = cluster.id AND igroup.vserver_id = vserver.id AND igroup.name LIKE \'' + prefix + '%\' AND RIGHT(igroup.name,  ' + count +') AND vserver.name = \'' + vserver + '\' AND (cluster.primary_address =  \'' + cluster + '\'  OR cluster.name =  \'' + cluster + '\' ) ORDER BY igroup.NAME ASC';
   //Connect to local WFA DB using the built-in read-only WFA user credentials
   Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/","wfa","Wfa123");
   Statement stmt=con.createStatement();  
   ResultSet rs=stmt.executeQuery(query);
   int i = 0;
   String s=; 
   ArrayList numbers = new ArrayList();
   ArrayList names = new ArrayList();
   while(rs.next()) {
      s = rs.getString('igroup_number');
      name = rs.getString('name');
      int n = Integer.parseInt(s);
      numbers.add(n);
      names.add(name);
   }
   for (int i = 0; i < (numbers.size() -1); i++) {
      if (!(numbers.get(i + 1)  - numbers.get(i)).equals(1)){
         return names.get(i).substring(0, (names.get(i).length() - count)) + padNumber((numbers.get(i) + 1), count)
      }
      if (i.equals(numbers.size() -1)){
         return names.get(i).substring(0, (names.get(i).length() - count)) + padNumber((numbers.get(i) + 2), count)
      }
   }
}

When testing the function it looks like this:

IE the function returns next conncurrent igroup name instead of incrementing the last igroup to return "igp_customer_SYDPOD025" (if there are names within the sequence that are missing) otherwise it will increment the last igroup name and return "igp_customer_SYDPOD027"

cluster1::> igroup show -vserver vserver3 -igroup igp_customer_SYD* -fields igroup
vserver  igroup
-------- ----------------------
vserver3 igp_customer_SYDPOD022
vserver3 igp_customer_SYDPOD023
vserver3 igp_customer_SYDPOD024
vserver3 igp_customer_SYDPOD026
4 entries were displayed.

Modify the SQL query for the volume to meet your requirement and let me know if you're still stuck get it working.

Hope that helps

/Matt

mbeattie

Hi Berra,

I did test this with volumes for you an confirmed it works. EG

cluster2::> vol show -vserver svm21 -fields volume
vserver volume
------- ----------
svm21   svm21_root
svm21   vol1
svm21   vol2
svm21   vol3
svm21   vol5
5 entries were displayed.

The function returns the missing volume in the sequence (vol4). When calling the function modify the input variables to meet your requirements. the syntax is:

get_concurrent_volume(<%cluster_name_or_ipaddress%>, <%vserver_name%>, <%volume_name_prefix%>, <%volume_numerical_padding%>)
EG: get_concurrent_volume('cluster1', 'vserver1', 'vol', 2)

MVEL function - Source Code:

function get_concurrent_volume(cluster, vserver, prefix, count){
   import java.sql.*;
   import java.util.ArrayList;
   String query= 'SELECT volume.name, CAST(RIGHT(volume.name, ' + count +') AS UNSIGNED)  AS volume_number FROM cm_storage.volume, cm_storage.vserver, cm_storage.cluster WHERE vserver.cluster_id = cluster.id AND volume.vserver_id = vserver.id AND volume.name LIKE \'' + prefix + '%\' AND RIGHT(volume.name,  ' + count +') AND vserver.name = \'' + vserver + '\' AND (cluster.primary_address =  \'' + cluster + '\'  OR cluster.name =  \'' + cluster + '\' ) ORDER BY volume.name ASC';
   //Connect to local WFA DB using the built-in read-only user credentials
   Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/","wfa","Wfa123");
   Statement stmt=con.createStatement();  
   ResultSet rs=stmt.executeQuery(query);
   int i = 0;
   String s=; 
   ArrayList numbers = new ArrayList();
   ArrayList names = new ArrayList();
   while(rs.next()) {
      s = rs.getString('volume_number');
      name = rs.getString('name');
      int n = Integer.parseInt(s);
      numbers.add(n);
      names.add(name);
   }
   for (int i = 0; i < (numbers.size() -1); i++) {
      if (!(numbers.get(i + 1)  - numbers.get(i)).equals(1)){
         return names.get(i).substring(0, (names.get(i).length() - count)) + padNumber((numbers.get(i) + 1), count)
      }
      if (i.equals(numbers.size() -1)){
         return names.get(i).substring(0, (names.get(i).length() - count)) + padNumber((numbers.get(i) + 2), count)
      }
   }
}

/Matt

Berra

Hello Matt

Thank's for your answer. This will help me a lot as this seems to be the solution for me.
I still have one more question if it's ok?

When I have modified the function you gave me (so it will give me the volume names instead of the igroup) I guess I have to create a new command and in that command call for the function, or? If so, what is the syntax for calling the function from a powershell command?

Many thanks in advance

/Berra

mbeattie

Hi Berra,

No problem, i did find one issue with the MVEL function, it works to return the missing numbers in a sequence but it gave me an error attemtping to increment the last volume number, probably just a java syntax issue (the powershell syntax works) but i think based on the code i've posted you get the idea and understand what the possiblities are.

The main issues with using this method is scalability and efficent. It's fast on a small SQL recordset but what if you had thousands of records to process to find one missing sequential number? It will have to check each an every record in the recordset to determine if the next record is sequential. I collaberated with collegues to see if there is a better solution...

As mentioned it is possible to use SQL natively to select the missing record however you need to create a tempory table to select the number sequence to compare and join the recordset to the temp table. It's a little more work to setup initially but it is definately a more efficent and scalable solution. You can find a more efficent and scalable solution here:

http://www.wfaguy.com/2019/04/find-first-available-object-with-number.html#more

To answer your second question, if you wanted to use an MVEL function that invokes a SQL query and returns a result, you don't need to create a WFA command. You just call the MVEL function from within the command and if you need to reference the value more than once than consider creating a constant and set the constant value to call the MVEL function.

Then in your command when you set the volume name and junction path just reference the constant. EG

Note: It's advisable to wrap the volume name around the "sanitizeVolumeName" function to ensure the volume name only contains valid characters.

Hope that helps.

/Matt

mbeattie

Hi Berra,

One other issue i discovered during testing the MVEL function method is that when you invoke a MySQL query from within an MVEL function the WFA reservations are NOT processed and therefore has the potential to result in an error if you are not using a search and define step to first test if the object exists before attempting to create it. For example, when a filter is processed WFA will include an existing reservations when processing the SQL query (that is a compelling reason to use the query method Mirko has posted on his blog). EG

I did manage to figure out the bug in the MVEL java syntax to return the next object name if all records are sequential. EG

function get_concurrent_igroup(cluster, vserver, prefix, count){
   import java.sql.*;
   import java.util.ArrayList;
   String query= 'SELECT igroup.name, CAST(RIGHT(igroup.name, ' + count +') AS UNSIGNED)  AS igroup_number FROM cm_storage.igroup, cm_storage.vserver, cm_storage.cluster WHERE vserver.cluster_id = cluster.id AND igroup.vserver_id = vserver.id AND igroup.name LIKE \'' + prefix + '%\' AND RIGHT(igroup.name,  ' + count +') AND vserver.name = \'' + vserver + '\' AND (cluster.primary_address =  \'' + cluster + '\'  OR cluster.name =  \'' + cluster + '\' ) ORDER BY igroup.NAME ASC';
   //Connect to local WFA DB using the built-in read-only user credentials
   Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/","wfa","Wfa123");
   Statement stmt=con.createStatement();  
   ResultSet rs=stmt.executeQuery(query);
   int i = 0;
   String s=; 
   ArrayList numbers = new ArrayList();
   ArrayList names = new ArrayList();
   while(rs.next()) {
      s = rs.getString('igroup_number');
      String name = rs.getString('name');
      int n = Integer.parseInt(s);
      numbers.add(n);
      names.add(name);
   }
   String last = names.get(names.size() - 1);
   for (int i = 0; i < (numbers.size() - 1); i++) {
      if (! ( (numbers.get(i + 1))  - numbers.get(i) ).equals(1) ){
         return prefix + padNumber((numbers.get(i) + 1), count);
      }
   }
   return nextNamePadded(last);
}

/Matt