Jump to content
mr.wallstrom

Get Product Keys oob script

Recommended Posts

First, I think it's ridiculous that "Get Product Keys" are each "too common terms" and so the forum search will not allow you to search for those words, or that phrase! Great way to bury an issue from coming to light.

 

So I had a little SQL class 7 years ago, so just started relearning anything about it yesterday. I was building a script to pull the productkey out of the LT db - that worked great once done. Today, I tried to find out why the oob script "Get Product Keys" was failing on a workstation. It errors out on line 14. The machine is Windows 10 Pro. I can pull the key with magic jelly bean just fine (manually.)

 

I'm hoping someone can look at the line here, and help me understand why it's failing. I thought it was due to poor usage of the wildcard with the LIKE Windows % part, but changing that did not fix hte issue.

 

Original line:

UPDATE productkeys SET productname = CONCAT('Microsoft ', productname) WHERE productname LIKE 'Windows %';

 

Line I thought would fix it (based on my work yesterday):

UPDATE productkeys SET productname = CONCAT('Microsoft ', productname) WHERE productname LIKE '%Windows%';

 

I'm still learning LTScript, so am not totally sure what this line is really trying to do. Looks like it, and the 2 above it, are udpating the table. The two above it though, have something the 3rd one does not, this:

UPDATE productkeys SET productname = CONCAT("Microsoft Windows", SUBSTRING(productname,24)) WHERE productname LIKE "Microsoft® Windows®%";

 

(The (productname,24) part, or 34 in the other line, is missing from the 3rd line entirely.)

 

Related question - what is the best way to test this command out, and get a better error message to pinpoint the problem? When working with a QUERY in sqlyog, the errors were decent enough I could look things up and work through it. I don't want to damage the productkey table, as we DO have a ton of keys in there - just none for this machine (and several like it.)

 

Thanks,

Michael

Share this post


Link to post
Share on other sites

Well then... I'll defer to any experts in here, but...

 

If your oob script doesn't work, change line 14 to this:

UPDATE productkeys SET productname = CONCAT("Microsoft", productname) WHERE productname LIKE '%Windows%';

 

So there were several issues in that line. First, there's no space between the word and the wildcard (at the end.) Second, "Windows% = anything STARTING with Windows; %Windows%= anything CONTAINING Windows. Third, the CONCAT('Microsoft'... has single quotes, where they should be double quotes.

 

Script now runs fine, and updates the company Product Keys table and client tab correctly. Hope this helps someone else. I hear a LOT of folks have trouble with this script.

 

Thanks,

Michael

Share this post


Link to post
Share on other sites
Well then... I'll defer to any experts in here, but...

If your oob script doesn't work, change line 14 to this:

UPDATE productkeys SET productname = CONCAT("Microsoft", productname) WHERE productname LIKE '%Windows%';

So there were several issues in that line. First, there's no space between the word and the wildcard (at the end.)

The space is intentional, it prevents a match for a string like 'Windows®', 'Windowshade', etc. It only matches when 'Windows' is not the end of the string, there must be a space, and presumably more words. (It would match even if there was nothing after the space, so 'Windows ' would match. To require at least one more character after the space, you would use 'Windows _%'.)

 

Second, "Windows% = anything STARTING with Windows; %Windows%= anything CONTAINING Windows.
Do not put a leading '%' on this statement. The explanation is correct, but the function is adding the string 'Microsoft ' (which has lost it's ending space as shown here) to whatever matches. If you matched anything containing Windows, then 'Microsoft Windows' would be matched, and changed to 'Microsoft Microsoft Windows'.

 

Third, the CONCAT('Microsoft'... has single quotes, where they should be double quotes.
It looks like the script is the source of a bad example, but you should not use double quotes. Single quotes are the standard, accepted way to enclose a literal string.

See https://dev.mysql.com/doc/refman/5.5/en/string-literals.html and https://dev.mysql.com/doc/refman/5.5/en/sql-mode.html#sqlmode_ansi_quotes

 

Yes, double quotes SHOULD work, because MySQL accepts either single or double quotes. UNLESS your database server has "ANSI_QUOTES" enabled. Single quotes WILL work with ANSI_QUOTES enabled or disabled. There is only one potential reason for preferring a double quote, Single quotes are not treated specially inside a double quoted string. Normally, any single quotes need a backslash in front. If the string Darren's Computer was inside single quotes, it would need to be 'Darren\'s Computer'. (As I was checking sources, I believe I read that doubling the quotes works also, so 'Darren''s Computer' would be an alternative, but I have personally never tried doing that.) It is recommended to always use single quotes, and to correctly prepare the string inside so that special characters are escaped.

Share this post


Link to post
Share on other sites
First, I think it's ridiculous that "Get Product Keys" are each "too common terms" and so the forum search will not allow you to search for those words, or that phrase! Great way to bury an issue from coming to light.
End each word with * . "Get* Product* Keys*" will allow you to get results for all three words.

 

Original line:

UPDATE productkeys SET productname = CONCAT('Microsoft ', productname) WHERE productname LIKE 'Windows %';

There is nothing wrong with this line. To find out what is happening, set the variable @ScriptEngineEnableLogger@=1 at the start of the script, or as a script global variable. This is provide a detailed run record at the end so that the failing step can be fully confirmed.

 

I'm still learning LTScript, so am not totally sure what this line is really trying to do. Looks like it, and the 2 above it, are udpating the table. The two above it though, have something the 3rd one does not, this:

UPDATE productkeys SET productname = CONCAT("Microsoft Windows", SUBSTRING(productname,24)) WHERE productname LIKE "Microsoft® Windows®%";

productname refers to a column in the productkeys table. CONCAT will return the string 'Microsoft Windows' in front of what is returned by SUBSTRING(productname,24). SUBSTRING returns characters from the current value for productname, starting with the 24th character. The first 23 characters would be ignored. The string 'Microsoft® Windows®' is 23 characters long, so that statement would return everything except 'Microsoft® Windows®'. See https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring

 

Related question - what is the best way to test this command out, and get a better error message to pinpoint the problem? When working with a QUERY in sqlyog, the errors were decent enough I could look things up and work through it.
To test these queries, use SELECT in sqlyog to have it return values that were intended to be affected. You can test most anything by replacing INSERT, UPDATE, or DELETE with SELECT, and making some adjustments.

 

Some Examples. (Edited to use example names and not be live/valid SQL)

If I wanted to test the results for an UPDATE query like this:

UPDATE tablename SET columnname = CONCAT("Microsoft Windows", SUBSTRING(columnname,24)) WHERE columnname LIKE "Microsoft(R) Windows(R)%";

I would run the following statement:

SELECT CONCAT("Microsoft Windows", SUBSTRING(columnname,24)) FROM tablename WHERE columnname LIKE "Microsoft(R) Windows(R)%";

If I wanted to test the rows that would be affected by a DELETE query like this:

DELETE FROM tablename WHERE columnname LIKE 'Microsoft(R) Windows(R)%';

Just use

SELECT * FROM tablename WHERE columnname LIKE 'Microsoft(R) Windows(R)%';

Edited by Guest

Share this post


Link to post
Share on other sites
...

It errors out on line 14.

....

Original line:

UPDATE productkeys SET productname = CONCAT('Microsoft ', productname) WHERE productname LIKE 'Windows %';

I checked my script history, and confirmed it also was failing on step 14. In the LTAScriptEngine log file, I found:

LTAgent  v110.376	 - 8/5/2017 2:15:51 AM	 - Script Engine: Error in processing Script Query Type: Duplicate entry 'Microsoft Windows 10 Home-50-00xxx-xx0xx-xxxxx-XXOEM-2813' for key 'ClientID_2':::

The problem is that the change it is trying to make is not allowed because it would create a duplicate entry in the 'ClientID_2' index.

I'm opening a ticket now.

 

Incidentally, the next step sets a script state variable, which once set, prevents lines 12-14 from ever being executed again. If you modified the step in a way that made it pass (or you disabled the step), it would skip these steps in the future even if you tried to correct the change. It would have the appearance of working but it wouldn't really be fixed, the damaged/incorrect records would still exist in the table.

Edited by Guest

Share this post


Link to post
Share on other sites

I have submitted a ticket to get this fixed properly. Steps 12-14 should be changed to:

STEP 12:
DELETE p1 FROM productkeys AS p1 JOIN productkeys AS p2 USING (ClientID, `Serial`, ComputerID) WHERE p1.productname LIKE 'Microsoft(R) Microsoft Windows(R)%' AND p2.productname = CONCAT('Microsoft Windows', SUBSTRING(p1.productname,34)); UPDATE productkeys SET productname = CONCAT('Microsoft Windows', SUBSTRING(productname,34)) WHERE productname LIKE 'Microsoft(R) Microsoft Windows(R)%';
STEP 13:
DELETE p1 FROM productkeys AS p1 JOIN productkeys AS p2 USING (ClientID, `Serial`, ComputerID) WHERE p1.productname LIKE 'Microsoft(R) Windows(R)%' AND p2.productname = CONCAT('Microsoft Windows', SUBSTRING(p1.productname,24)); UPDATE productkeys SET productname = CONCAT('Microsoft Windows', SUBSTRING(productname,24)) WHERE productname LIKE 'Microsoft(R) Windows(R)%';
STEP 14:
DELETE p1 FROM productkeys AS p1 JOIN productkeys AS p2 USING (ClientID, `Serial`, ComputerID) WHERE p1.productname LIKE 'Windows _%' AND p2.productname = CONCAT('Microsoft ', p1.productname); UPDATE productkeys SET productname = CONCAT('Microsoft ', productname) WHERE productname LIKE 'Windows _%';

Each step first DELETEs rows to be updated by the next query that will conflict with records that already exist. This way there are no duplicate indexes created when the UPDATE query is run in each step.

Edited by Guest

Share this post


Link to post
Share on other sites

Darren,

 

I wanted to run this by you because I'm seeing the opposite of what you described, and don't want to mess anything up by using the current code I have, if I shouldn't. 3 Pics attached below. #1 shows my current edited line 14. Second one shows what I see from LT's built in script - the double naming you mentioned "Microsoft Microsoft Windows.." Only happened with the default code. #3, shows the results of the edited code, as seen in the product keys table.

 

Note - I observed that the built in script is NOT detecting Windows 10 or Server 2016 keys at all. The edited code seems to grab them and update the table properly.

 

Thanks,

Michael

Windows-GetProductKey03-ResultsOfCorrection.JPG.4aa8b03d0ec7994cb3463bf91364bc3c.JPG

Windows-GetProductKey02-DoubleNaming.JPG.51636dd81483d3df7cba36500bc5af76.JPG

Windows-GetProductKey01.JPG.7e45cba57f08d60cfa995affccc044a3.JPG

Share this post


Link to post
Share on other sites

One of the reasons that you would have seen it not updating values was because it was creating duplicate index entries, which would error and thus not be fixed. The queries I posted earlier detect and delete the overlapping records, so that only legitimate records remain which will be updated. With improper matching and replacing, you got around the duplicate indexes by creating entries that are unique because they are invalid (they would never have been created with the original queries). But that also means that the original queries (or improved versions) will not fix your data.

 

If you ran the script even one time with the sql that you posted, you have already broken your data set (as shown in your picture, if I understand what you were showing). You will need to manually run queries to clean up it. Once line 15 in the script executes the scripted queries will never be ran again, so you can make any changes you want in there but it will not fix the data.

 

These steps should undo the specific effects shown and expected from the change you had proposed to step 14.

  1. Run this in SQLYog: (The apparent duplicate/double queries are intentional, as some records may not be able to be updated by the first query)
    UPDATE IGNORE productkeys SET productname = REPLACE(productname,'MicrosoftMicrosoft','Microsoft') WHERE productname LIKE 'MicrosoftMicrosoft%';
    UPDATE IGNORE productkeys SET productname = REPLACE(productname,'MicrosoftMicrosoft','Microsoft Microsoft') WHERE productname LIKE 'MicrosoftMicrosoft%';
    UPDATE IGNORE productkeys SET productname = REPLACE(productname,'MicrosoftWindows','Microsoft Windows') WHERE productname LIKE 'MicrosoftWindows%';
    UPDATE IGNORE productkeys SET productname = REPLACE(productname,'MicrosoftWindows','Windows') WHERE productname LIKE 'MicrosoftWindows%';


  2. Run the queries from this post in SQLYog. (These queries should make the changes intended by the original queries.)
  3. Run this and review the results and make sure that you don't see any other messed up product names. If you have any, you will need to track them down and correct them.
    SELECT DISTINCT productname FROM productkeys ORDER BY productname;


  4. Finally, fix the SQL in your script, because if it ever did run again it would be bad. Your query in Line 14 is definitely wrong.

Edited by Guest

Share this post


Link to post
Share on other sites

I wondered about that - thanks for the fix info! I was asking actually though, because it's actually the OOB script that is causing the messed up names. Running my edited script, only produced the single one (the Microsoft Windows 10 Pro shown in pic #3.) I totally get what you're saying, and looking back at it agree... it's just not matching up to what I was seeing.

 

(5 minutes later...) I think what I may be seeing, is that different LT versions may have had different versions of the original code. So I am actually seeing both the double naming, and proper single naming, all created in the past by the same Get Product Keys script (which must have changed during updates at some point.) I'm extremely cautious with tests of new scripts, so haven't damaged tables beyond 2 machines it has run on - but I see a TON that could need your suggestion above to fix, courtesy of the OOB script.

 

Question - Would it be smart to disable the part of the script that sets "version_2"? If the key changes, but the OS version stays the same, it seems to me that the productkeys table would not ever be updated properly?

Share this post


Link to post
Share on other sites

Once the records are repaired, I do not suggest modifying the script any further as any unofficial changes won't be supported and can cause more trouble in the future.

Share this post


Link to post
Share on other sites

It still sounds like my point isn't being clearly communicated, from your comment at http://www.labtechgeek.com/forum/viewtopic.php?f=3&t=1342#p22570

I am posting back over in this topic to make sure if what I said wasn't clear to anyone else, hopefully they will see this.

  • The "fixup" code steps use a test to determine if they need to be run. This test at step 11 will evaluate the same for ALL agents. It does not matter which agent you run the script against, they will all detect if a variable named 'ProductNameUpdated' has been set to 'version_2', regardless of which agent ran the script that set the variable.
  • If the variable is not found, execution continues to step 12-14. Each of these steps UPDATE the product names FOR ALL AGENTS, regardless of which agent is currently running the script.
  • Once step 15 is completed, steps 12-14 will not be executed again. Until this happens, Steps 12-14 could be executed repeatedly. If the script failed and stopped at 14, then 12 and 13 were executed. When the script is run again, 12 and 13 would run again.

The first two points are the key details. These queries are working across the whole table, changing values for all agents. It doesn't matter if the script was only run once, or only against 1 agent. The changes in these steps affect all agents. The pictures you posted are exactly what is expected from the change you described to the query in step 14. That small change would have caused many records to be improperly updated.

 

That is why it is very important not to change things you don't fully understand, and to understand what things you don't fully know. If you are running with scissors, learn how to hold them, know when to slow down, and know when you should stop.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...