Jump to content

Search the Community

Showing results for tags 'mysql'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • MSPGeek
    • The Geek Cast
    • Code of Conduct
  • ConnectWise Automate / Labtech
    • ConnectWise Automate / LabTech
    • ConnectWise Automate / LabTech - Development

Categories

  • ConnectWise Automate
    • Scripts
    • Plugins
    • SQL Snippets
    • Role Definitions
    • Automate PowerShell Code
    • Reports
    • Internal Monitors
    • Remote Monitors
  • ConnectWise Manage
    • API Interacting Code

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


About Me


Location


Agent Count


INTERESTS


OCCUPATION


ICQ


WEBSITE


WLM


YAHOO


AOL


FACEBOOK


GOOGLEPLUS


SKYPE


TWITTER


YOUTUBE

Found 9 results

  1. Hi Everyone, Here at Automation Theory we think access to the Automate database is very import for every Automate admin, along with some basic statistics for monitoring database load and what the app is doing with the DB (and that you shouldn't have to pay for it!). We're excited to announce the launch of a FREE plugin we call Database Commander. It shows important MySQL load/performance demographics and has a query editor, complete with table name autocomplete and syntax highlighting. Features: Live MySQL load and performance data Syntax highlighting query editor Autocomplete for all default application tables Text editor functions (find/replace, word wrap, zoom, etc.) CSV export of query results Double-safety filter to prevent accidental data changes Auditing of plugin access and use You can get the plugin here: https://automationtheory.org/database-commander You'll get an email with links to the plugin download, the documents, and a blog series we created "MySQL for Automate 101".
  2. With recent events, the demand for remote connectivity is high. For partners using the MAC Signup functionality issues can arise when remote connectivity solutions use duplicate MAC addresses. Like most issues in Automate, it's possible to work around this and use monitors/scripts/searches/etc. to create a creative solution. However, basic validity checking is a native function of MySQL, and there's an elegant approach that will help in this situation (and may not completely replace creative solutions). This guide is designed to demo how to use database triggers in MySQL to enforce data validation. This can be useful for several things, but we'll use the "duplicate software MAC" problem (Cisco AnyConnect VPN) as our example. Important notes: Database triggers are rather low-level items and are difficult to troubleshoot You can only have one trigger per time per operation per table This is unsupported by CW (both on the grounds of database modification and the manipulation of how the application works). With that, open up your favorite MySQL client and let's get started. DELIMITER $$ CREATE TRIGGER `computer_update` BEFORE UPDATE ON `labtech`.`computers` FOR EACH ROW BEGIN IF NEW.mac IN ('00-05-9A-3C-7A-00', '00-00-00-00-00-00') AND OLD.mac IS NOT NULL THEN SET NEW.mac = OLD.mac; END IF; END$$ DELIMITER ; The above code creates a trigger for the updating of the MAC address in the computers table. MySQL triggers can reference the old and new values, and they do so via KEYWORD.COLUMNNAME syntax. We begin by checking if the new MAC address is in the list of known bad MAC addresses ('00-00-00-00-00-00' is just added here for example) and if the old MAC address is not null. IMPORTANT: This means that if there's no existing MAC address (like on agent signup) this MAC could still be written to the DB. This is why the creative solutions might still have a place depending on the particular issue you're solving. For this particular issue, we're going to assume that these MAC addresses are already blacklisted for signup and we're just trying to solve for inventory updates. Next, we set the new value for the column to be the old value, as we'd want to keep the old MAC address if a user connects a VPN client. You'll notice that we switch the delimiter when creating this trigger. This is needed since our trigger itself contains semicolons, and to parse correctly we temporarily switch how we're terminating our lines. This may throw your particular SQL client for a loop. At this point, once we execute the code to create this trigger we are set. If you try to update a computer's MAC address to one of the values in the list the statement will still run without errors, but the value won't change (this would appear to be black magic to anyone who did not know the trigger was in place -- please be advised if you're considering a broader use case). Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/
  3. Hey fellow geeks! Like many of you, I used the check boxes to Hide from All Clients and Disable this Client when offboarding and removing old clients from the navigation tree. Since moving to Automate 12, this functionality no longer works. Having several former clients cluttering up the tree in the CC, I started the process of manually deleting clients. This process was slow and frustrating. To combat this, I created a new stored procedure that can be run from script or via SQLyog to simplify this process. The procedure performs the following steps: Checks to ensure no computers remain in the computers table Identifies all locations assigned to the client Deletes network devices, retired assets, location stats, and passwords assigned at the location level then deletes the location. Scans all tables for columns named clientid then deletes any reference to the clientid in these tables. This includes cleaning up references in plugins that the standard delete command misses. This procedure can be run from script using the SQL Execute function with the below SQL statement Substitute the target clientid for X. The same statement can be executed directly from SQLyog, or your favorite MySQL management tool. I've attached a SQL dump that can be executed to add the procedure to your database. Let me know how this works for you! Thanks, Jared Brantley jared@mspautomated.com Here's the code: DELIMITER $$ USE labtech$$ DROP PROCEDURE IF EXISTS sp_Custom_DeleteClient$$ # Procedure is launched by running CALL sp_Custom_DeleteClient(%clientid%); CREATE PROCEDURE sp_Custom_DeleteClient(IN CLID INT) # Start of procedure. sp_Custom_DeleteClient: BEGIN # NOTE: If this procedure is performed on versions of MySQL earlier than 5.6.3, you may # encounter a 1329 warning for No data - zero rows fetched, selected, or processed. This # is a bug that is resolved in MySQL 5.6.3. The procedure still completes successfully. # Subroutine to verify no agents exist on computers table. SafetyCheck_SUB: BEGIN # Check to ensure that no agents exist in the computers table for ClientID. IF (SELECT COUNT(computerID) FROM computers WHERE clientid = CLID) > 0 THEN # If agents exist, end the procedure now. LEAVE sp_Custom_DeleteClient; # Carry on if no agents exist in the computers table. END IF; # End SafetyCheck subroutine. END SafetyCheck_SUB; /*==============================================================*/ # Subroutine to delete locations assigned to ClientID. DeleteLoc_SUB: BEGIN # Variable representing LocationID. DECLARE LOCID INT(10); # Create variable to end the process. DECLARE FINISH BOOLEAN DEFAULT 0; # Create cursor to grab all LocationsIDs for the ClientID. DECLARE LOCID_CUR CURSOR FOR SELECT locationid FROM locations WHERE clientid = CLID; # SQLSTATE 020000 is returned when we reach the last row, set variable to close loop. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET FINISH=1; # Here we open the cursor to begin looping through the results. OPEN LOCID_CUR; # Begin looping through results. Del_Location: LOOP # Get locationid from the cursor and insert into LOCID variable. FETCH LOCID_CUR INTO LOCID; # Test FINISH variable to determine if the loop is complete. IF FINISH = 1 THEN LEAVE Del_Location; END IF; # Delete network devices assigned to LocationID. DELETE FROM networkdevices WHERE locationid = LOCID; # Delete retired assets assigned to LocationID. DELETE FROM retiredassets WHERE locationid = LOCID; # Delete location stats assigned to LocationID. DELETE FROM h_locationstats WHERE locationid = LOCID; # Delete passwords assigned at location level. DELETE FROM passwords WHERE locationid = LOCID; # Delete the location. DELETE FROM locations WHERE locationid = LOCID; # End of Del_Location loop. END LOOP; # Close the open CURSOR. CLOSE LOCID_CUR; # End of location deletion subroutine. END DeleteLoc_SUB; /*==============================================================*/ # Subroutine to delete the client. DeleteClient_SUB: BEGIN # Variable to store table names containing a clientid column. DECLARE TBLNAME VARCHAR (50); # Create variable to end the process. DECLARE FINISH BOOLEAN DEFAULT 0; # Create cursor to loop through tables containing a clientid column. # This excludes views and the plugin_webroot_3api table uses the clientid # column for an entirely different purpose. DECLARE TBLNAME_CUR CURSOR FOR SELECT table_name FROM information_schema.columns WHERE column_name='clientid' AND table_name NOT LIKE 'v_%' AND table_name != 'plugin_webroot3_api'; # SQLSTATE 020000 is returned when we reach the last row, set variable to close loop. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET FINISH = 1; # Here we open the cursor to begin looping through the results. OPEN TBLNAME_CUR; # Loop creation to delete client records. Del_Client: LOOP # Get locationid from the cursor and insert into LOCID variable. FETCH TBLNAME_CUR INTO TBLNAME; # Test FINISH variable to determine if the loop is complete. IF FINISH = 1 THEN LEAVE Del_Client; END IF; # Create a new variable with the populated TBLNAME and CLID variables. SET @s = (SELECT CONCAT('DELETE FROM ',TBLNAME,' WHERE clientid IN (',CLID,');')); # Preapare a delete statement from the above variable. PREPARE stmt FROM @s; # Excute the delete statement EXECUTE stmt; # Dump the statement from memory DEALLOCATE PREPARE stmt; # End of Del_Client loop. END LOOP; # Close the open CURSOR. CLOSE TBLNAME_CUR; # End client deletion subroutine. END DeleteClient_SUB; /*==============================================================*/ # End of procedure. END sp_Custom_DeleteClient $$ DELIMITER; sp_Custom_DeleteClient.sql
  4. Hi Everyone, Since ConnectWise no longer supports MariaDB I've created a guide to help partners migrate to MySQL. The vendor provided documentation involves a long and convoluted logical backup process that can be avoided. This guide is the fastest method of upgrading to MySQL while allowing 100% ability to revert back in case of failure (since we'll be leaving MariaDB intact). Before we go any further please note that you can farm out this task if you want to focus on automation and not database administration: https://automationtheory.org/product/mysql-patching/ Now, onto the upgrade process (with pictures!)! Please note that this is a development server with four different versions of SQL installed -- you may see items in screenshots that are not applicable to a normal server (like LabMySQL not running when restarting the Database Agent). Here's a screenshot showing the existing version of MariaDB installed: Before we get started make sure you have your root password and enough disk space to hold twice your DB (we're going to duplicate it in order to do a "fail-safe" upgrade). This guide assumes that the DB upgrade is taking place on the same server and does not cover any application-level changes required for moving the database to a new host. Download and extract the ZIP file installer for MySQL 5.7 (link). Standard deployments extract to C:\MySQL, but it's not all that important (just remember where you put it). MySQL 5.7 does not include a data directory -- this is good, as we're going to do what's known as a physical backup where we move the existing MariaDB data directory to our new install. If you have downloaded MySQL 5.6 for whatever reason it DOES have this directory and it should be deleted. Open up SQLyog (or your editor of choice) and run the command "SET GLOBAL innodb_fast_shutdown=0" (not case sensitive). This will force data in the buffer files to be written to the actual tables (the buffer file formats aren't compatible, so we'll be deleting them). WARNING: Failure to do this will lead to potential data loss in three steps! Stop the service LabMySQL. Once the service has stopped copy the entire data folder from the MariaDB base directory to the extracted MySQL base directory. Also, your my.ini file can be moved there (MySQL will automatically apply it on startup without special instructions if it's in this location). Delete the files ib_logfile0 and ib_logfile1 (and if you're running an exotic configuration and you have more than two ib_logfileX files delete any others). Open an administrative command prompt in the bin directory of the new MySQL server and run the command "mysqld.exe --console". Scary error messages are to be expected, but the application should stay running. If it closes something has gone wrong (feel free to troubleshoot with the provided console messages -- but please note that this would be a good time to get a second opinion if the issue seems exotic). Open a second administrative command prompt in the bin directory (leaving the other one open). This time run the command "mysql_upgrade.exe -u root -p --force". This command will produce lots of crunchy messages; it's MySQL seeing the table files in a different format and doing all the under the hood conversions to make them match MySQL 5.7 formatting (note: the screenshot shows the wrong syntax). Once the mysql_upgrade has finished, go back to the command window where the server (mysqld) is running. Press CTRL+C to stop it, then up arrow to get the start command and re-launch it. When the server starts up it should have fewer error messages, but it will likely have errors about the mysql.user table. If you do not see errors about the mysql.user table, skip this step. Otherwise, open SQLyog and connect to the DB (note: if you can't get logged in this would be another good stopping point for a second opinion). Run the following commands to remove the extra columns that MariaDB has that MySQL doesn't. alter table mysql.user drop column default_role; alter table mysql.user drop column max_statement_time; alter table mysql.user drop column password_expired; At this point, it's worth repeating steps 8 and 9 to make sure that the application will be happy when connecting. Restart the Database Agent service. In the console you'll see some failed login attempts; this is normal. After 1-2 minutes try logging into the application. As shown here, success! We have upgraded to MySQL 5.7. Important: We're not done yet! The service LabMySQL is still pointed at the old database instance. If the console closes or the server reboots you'll lose MySQL. To correct this close the application, stop the server, delete the old service (sc delete LabMySQL), and run the following command "mysqld --install LabMySQL". Please note that you can add the option " --defaults-file=C:\path\my.ini" but as previously mentioned this is not required if the my.ini file is in the base directory of the MySQL server. At this point, MySQL should be the persistent database for the Automate server. Start the service LabMySQL, restart the Database Agent, and verify the application launches. To be extra sure you can restart the server to make sure that the service starts on boot. Please note that the old database is 100% intact at this point. Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/
  5. I think every Automate admin has experienced the frustration of how slow the desktop application is. My primary Automate server has ~10,000 agents, and my users were having a horrible experience on a daily basis (they named the agent context menu "the right click of death"). I was sick of the painful user experience and the administration headaches -- so I upgraded to MySQL Enterprise and did a deep dive into how the application interacts with the database. Using the management tools from Oracle I found lots of issues, and after correcting them it's a night-and-day difference (the right click of death is gone!). Each Automate deployment is unique, and there's no hard code-able one-size-fits-all approach. However, I created a plugin that will dynamically gather information from the current configuration and adjust MySQL on the fly to optimally perform for Automate. I published a blog post on my site explaining more of the details: https://automationtheory.org/the-mysql-performance-problem/ I wanted everyone to be able to have a properly setup database without having to buy MySQL Enterprise (or be a DBA on the side), so I created a plugin that does all the dirty work. I plan on releasing this plugin as a monthly subscription, but first I want to do some beta testing to verify results on different Automate deployments (to get a good mix of sizes and types). To get started go to https://automationtheory.org/support/kb/faq.php?id=1 The KB article has a download for the plugin and the setup script, along with instructions. Feel free to reach out with any questions or comments.
  6. The root password is something that is easy to forget about until you really need it. Then it becomes a wild goose chase looking through password managers, documentation systems, and any other place that password might be used. This guide is meant for the situation where database maintenance is required and the root password is unknown. Items worth noting Every super admin account in Automate has the same permissions in the database as the root user. If your server has been around for a while (pre 10.5) it might be plain text in the registry under the key HKLM\Software\WOW6432NODE\LabTech\Setup There's nothing special about the root account -- it's very similar to Administrator in Windows. Best security practice is to create a new account with the super permission and not use root (unfortunately in the CW Automate world this best practice is difficult to implement). IMPORTANT: This guide is actually going to instruct you how to create a new account with the super and grant permissions -- we won't actually be resetting the root password. The root password used at server install is encrypted and stored in the registry and is used by Automate patches and other support utilities. If you change the root password you're going to break things in Automate down the road. An example, the 2020.2 patch: Now, without further ado, the new super account creation process. To begin, here's a screenshot to show that we're locked out of a new MySQL 5.7 instance: [In case you're wondering, MySQL 5.7 randomly generates a root password if one is not specified, and it's recorded in the error file.] Create a text file with the following commands, and save it as a .sql file: CREATE USER 'rootbeer'@'localhost' IDENTIFIED BY 'abc123!'; GRANT ALL PRIVILEGES ON * . * TO 'rootbeer'@'localhost' WITH GRANT OPTION; GRANT PROXY ON ''@'' TO 'rootbeer'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES; I'll be calling the account 'rootbeer' and using an insecure password for the sake of demonstration. Good security practice is strongly advised. Stop the service LabMySQL Open an administrative command prompt, and navigate to the bin folder of your MySQL install, wherever mysqld.exe is located. Start MySQL passing the script you created in step one as the init-file parameter. mysqld.exe --console --init-file C:\path\to\reset.sql When the server starts it should show in its output that it ran the script. Press CTRL+C to shutdown the server once you see this. As you can see in the other window, we're successfully able to login. Start LabMySQL again. Login as your new super account. As you can see here, ours has all global privileges and the DBA role. Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/
  7. I've had an issue with our server at random times after the daily maintenance period cause IIS to stop responding. The error reads: "Sql Open Conn ErrorAuthentication to host 'localhost' for user 'asp_LabTech_1' using method 'mysql_native_password' failed with message: Access denied for user 'asp_LabTech_1'@'localhost' (using password: YES). I had a case with support back in October and after about a month they came to the conclusion that it was a Windows OS issue. I built a new server and migrated the database. I had no issues from the end of October to now mid-Feburary. Now I'm starting to have the same issue again. I opened another ticket and they suggested to go ahead and upgrade to the 2019.2 patch since it fixes a lot of issues (I was on 12 patch 12). Its been a week on the new patch and today my server threw the same error again. I would appreciate any suggestion as I do not want to go through another server rebuild. Thanks in advance!
  8. The topic sounds good right? Really, this will be a post to address some misconceptions and for me to keep my notes. First off. I AM NOT A MySQL TUNING EXPERT. Please point out any mistakes in what I say below if you know better. Many of you may have heard that MySQL under Windows supports a maximum of 2048 connections. (Well, 2048-(2*Tables) connections). It is shown in the MySQL documentation for versions 5.5-5.7 (and maybe others). Some pages say that this was resolved in MySQL 6 but the changes will not be backported. Other sources say that this limit was removed with MySQL 5.5+. Who do you trust? I trust data. MySQL includes a testing utility, mysqlslap. With some tests you can verify for yourself the concurrent connection limits, and uncover other system limits that may be a factor. First, a review of some my.ini settings to know before testing. max_connections : Recommended to be AgentCount * 3. If this is exceeded, SQL queries will fail with the error "max_connections exceeded" or similar. See https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connections max_connect_errors : My server had a value of 100. After 100 failed connections without a successful connection, ALL connections from the offending host are blocked for some period of time. I believe mostly working is better than completely not working. Until I learn otherwise, I will suggest that this be set = max_connections. (Yes, this will mostly disable the feature. Is that bad? I'm not sure) See https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connect_errors open_files_limit = Each open socket (connection) is a file handle. Each table is at least 2 file handles. I am going to suggest that this should be set to max_connections * 3. (That's probably high.. max_connections+tables*2+500 might be closer.. I don't know, but I do know that if this is lower than max_connections or lower than your table count, things will be bad) Well, maybe it wont be bad: mysqld may attempt to allocate more than the requested number of descriptors (if they are available), using the values of max_connections and table_open_cache to estimate whether more descriptors will be needed. See https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_open-files-limit back_log = The TCP Listen queue size. When MySQL receives a connection, it starts a thread. During this slight delay, additional connections can come in before the threads respond. Unless you expect huge spikes in connections, this does not need to be high. But.. My server had a value of "80" When set to -1, calculates automatically as 50+(Max_connections/5) Max_Connections are typically = 3 x Agent Count. I suspect the calculation may be higher than needed for normal use. So I am going to suggest a Back_Log =AgentCount/5+50. So for 1000 Agents : back_log=250 (Hint: You will want to raise this to the suggested level or higher if you are trying to do testing with a large simultaneous count) See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_back_log bind-address - Optional, use to control IP addresses the server will listen to. * = All IPv4 and IPv6 Addresses (Default) 0.0.0.0 - All IPv4 addresses - DO NOT DO THIS - I put this entry in to specifically remind myself not to try that again. See https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address All MySQL Options Reference - https://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html Before you change ANYTHING to any values I have suggested, please research and become informed yourself, and test to verify results. Now, for testing. (Almost) The last thing to be aware of. TCP Ephemeral Ports and TCP TimeWait. I may explain this more later, for now, know that running out of ports is very bad. Settings to adjust are MaxUserPort and TcpTimedWaitDelay. See https://support.microsoft.com/kb/196271 and http://www.networkjack.info/blog/2008/07/21/windows-server-ephemeral-ports-and-stale-sockets/ Some of the Testing Parameters: (See also https://dev.mysql.com/doc/refman/5.5/en/mysqlslap.html) --concurrency= Number of Concurrent Client sessions (If you want to prove you can reach your max_connections, set this equal to max_connections) --auto-generate-sql-execute-number= Number of queries per client session (Normally would be 3 to create a table, query it, and drop it.) Setting this higher causes each client session to run longer, increasing the odds that you will reach max_connections --iterations= Number of times to repeat test above. Total connections should be concurrency*iterations*(sql-execute-number/detach) --detach= Number of queries to complete before dropping and reconnecting the session Testing Commands: #Kick off Load Test: "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlslap.exe" --user=ADMINUSERorROOT --password="PASSWORD" --host=localhost --auto-generate-sql --auto-generate-sql-execute-number=100 --concurrency=40 --iterations=2 --number-int-cols=2 --number-char-cols=2 -v --auto-generate-sql-load-type=read #Stress a little: "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlslap.exe" --user=ADMINUSERorROOT --password="PASSWORD" --host=localhost --auto-generate-sql --auto-generate-sql-execute-number=100 --concurrency=4000 --iterations=2 --number-int-cols=5 --number-char-cols=2 -v --auto-generate-sql-load-type=read #Break Max Connections: (Set concurrency=2*max_connections) - If back_log is low, connections will rate limit and you won't get all attempts to occur simultaneously. "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlslap.exe" --user=ADMINUSERorROOT --password="PASSWORD" --host=localhost --auto-generate-sql --auto-generate-sql-execute-number=100 --concurrency=8000 --iterations=2 --number-int-cols=5 --number-char-cols=2 -v --auto-generate-sql-load-type=read #Break Ephermeral Ports (Probably): Make each thread drop and reconnect every 10 queries. Should exhaust the ports in TIME_WAIT state "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlslap.exe" --user=ADMINUSERorROOT --password="PASSWORD" --host=localhost --auto-generate-sql --auto-generate-sql-execute-number=1000 --concurrency=40 --iterations=10 --detach=10 --number-int-cols=2 --number-char-cols=2 -v --auto-generate-sql-load-type=read During testing, to see MySQL Server Stat info run: SHOW STATUS WHERE `variable_name` LIKE '%connect%' AND VALUE <> 0; During testing to see connection information run: cmd /v:on /c "SET "newf=%temp%\netstat-temp-%RANDOM%.tmp"&netstat -an|find ":3306">"!newf!"&echo Established:&type "!newf!"|find /c "ESTABLISHED"&echo Time_Wait:&type "!newf!"|find /c "TIME_WAIT"&echo Other States:&type "!newf!"|find /v "ESTABLISHED"|find /v /c "TIME_WAIT"&del "!newf!">NUL" mysql server tuning my.ini server variable variables setting settings parameter parameters performance optimize
  9. Automate installations provide a number of custom MySQL functions. These helped fill particular needs within the program, but they can help you too!. Suppose you have two version numbers, like VER1=10.3 and VER2=10.2. Trying to compare them directly is a StringComparison and returns only 1 or 0 (True or False). A proper version comparison will tell you if VER1 is LESS than, EQUAL to, or GREATER than VER2. The custom function LT_CompareVersions('VER1','VER2') will do just this. Here is how the function works in practice. If VER1 is greater, the result should be "1", if it is less it should be "-1", and "0" if equal. Evaluating, StringCompare, VersionCompare, ShouldBe '11.34>11', '1', '1', '1' '11.34>11.4', '0', '1', '1' '1.10.0>11.0.0', '0', '-1', '-1' '2.10.0>11.0.0', '1', '-1', '-1' '11.34.0.0>11.4.99.99', '0', '1', '1' You can see that a simple version compare will work with VER1>VER2, but when they get more complex you would get the wrong result each time. Here is an example using the "IF SQL Check" script function. In my test, I want to know if the DiscoveredVersion is greater than my TargetVersion: IF [SQL SELECT (LT_CompareVersions('@DiscoveredVersion@','@TargetVersion@') > 0) AS VerResult] = 1 THEN Jump to :NoUpgradeNeeded
×
×
  • Create New...