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