Systems Management Question

SQL Asset Update Query

02/10/2014 2924 views

Is it possible to run multiple update queries from within Custom Ticket Rules Update Query? I am trying to use it to mass update our assets.

Here is an example of queries I need to run:

UPDATE `ORG1`.`ASSET_DATA_5` SET `FIELD_264`=20130116 WHERE FIELD_22 like '2UA93307QK';
UPDATE `ORG1`.`ASSET_DATA_5` SET `FIELD_264`=20130117 WHERE FIELD_22 like '2UA93307R4';
UPDATE `ORG1`.`ASSET_DATA_5` SET `FIELD_264`=20130118 WHERE FIELD_22 like '2UA93307SG';
UPDATE `ORG1`.`ASSET_DATA_5` SET `FIELD_264`=20130119 WHERE FIELD_22 like '2UA93307SC';
UPDATE `ORG1`.`ASSET_DATA_5` SET `FIELD_264`=20130120 WHERE FIELD_22 like '2UA93307Q9';

UPDATE `ORG1`.`ASSET_DATA_5` SET `FIELD_264`=20130121 WHERE FIELD_22 like '2UA93307RV';
(Asset_Data_5 is my computer asset table, Field 264 is our asset tag field and Field 22 is the service tag/serial number)

1 line at a time succeeds and updates the asset tag field based on the serial number but more than one fails with a syntax error. Is there any way to mass update these fields? I need to update about 3,000 assets and running this one at a time is not practical. Since R1 user does not have write access to the DB, I cannot use MySQL Workbench to update. Asset Import will not work either, since the asset name is not part of the spreadsheet to import. I am trying to match a spreadsheet of our asset tags with service tags/serial numbers within the assets. Does anyone have any suggestions to acomplish this?


0 Comments   [ + ] Show comments


All Answers


If the asset name isn't part of your spreadsheet then I would perform an export of your assets (via a report) that would get you a spreadsheet with the asset name included. You can then update the spreadsheet to include the data you need updated.

Answered 02/11/2014 by: chucksteel
Red Belt

  • Thanks for the suggestion but I think that would still require manually mapping the serial number to the asset name in the exported report. Export is typically how I mass update my assets but we got this spreadsheet from a vendor so asset name is not included. All it has is serial numbers and it's assigned asset tag. That would require matching all 3,000 serial numbers to the asset name, no? Why is Name a required field when I can use serial number as the PK? It finds the records fine that way but updates/erases the asset name. There has to be an easier way to do this.
    • We have our asset module configured to use the serial number for this exact reason. Also, KACE should be matching the serial number to the asset name, otherwise it wouldn't be field_22 in the asset_data_5 table. The following query should get you asset names and field_22 (which I'm guessing is your serial number):


      Export that to a spreadsheet and use Excel to match up your new data from the vendor based on the serial number (or use Access, which might be easier) and you'll have the file you need for your import.
      • I was able to get this done by using the VLOOKUP function in excel, it mapped the two sheets together. Thanks!

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login


This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ