SQL Asset Update Query
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'; 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? Thanks |
Answers (1)
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.
Comments:
-
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. - SDNBTP 10 years ago
-
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):
SELECT ASSET.ID, ASSET.NAME, AD.FIELD_22 FROM ORG1.ASSET
JOIN ASSET_DATA_5 AD on AD.ID = ASSET.ID;
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. - chucksteel 10 years ago-
I was able to get this done by using the VLOOKUP function in excel, it mapped the two sheets together. Thanks! - SDNBTP 10 years ago
