How to bulk update asset records
The problem I have is all asset records (specifically assets representing Servers) on the Kbox are missing a cost value. In a CSV I have all the costs and the server hostnames but not the asset record names. i.e. my CSV looks like
Hostname | Cost
server1 | 199
server2 | 232
server3 | 192
However I cant import the CSV to bulk update the Server asset records because it wants the Asset Name field which I dont have in the CSV. The import insists on using the Asset Name as the primary key, it wont let me use hostname. There are 2000+ records needing updating so I cant manually update each and also dont want to manually put the asset name in the spreadsheet for every asset as that would take just as long.
If you can think of another way to update all the Server assets with their costs I'd love to hear. I've asked Kace support for a way to directly modify the Kbox database, as with MySQL Workbench I could quickly bring up a table view of my assets and add in each cost value. But i've asked before and been told no.
Another way I've tried is going through the steps of editing the tables using Workbench (it fails but i can follow the wizard), then copying the SQL update statements it constructs into a helpdesk rule. At least that way I could make the all changes through Workbench then simply copy and paste the update statements into a rule. But I get lots of syntax errors when I try more than one update statement. One works but any more and it says the below
51:33> Starting: Wed, 29 Jun 2011 13:51:33 +0100
51:33> Executing Select Query...
51:33> selected 609 rows
51:33> Executing Update Query...
51:33> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
UPDATE ADVISORY SET TITLE='Example of a kb article TEST2' WHERE ID=1' at line 1] in EXECUTE("UPDATE ADVISORY SET TITLE='Example of a kb article TEST' WHERE ID=1;
UPDATE ADVISORY SET TITLE='Example of a kb article TEST2' WHERE ID=1;")
51:33> Ending: Wed, 29 Jun 2011 13:51:33 +0100
Any direction would be really useful.
Thanks
StuBox
Hostname | Cost
server1 | 199
server2 | 232
server3 | 192
However I cant import the CSV to bulk update the Server asset records because it wants the Asset Name field which I dont have in the CSV. The import insists on using the Asset Name as the primary key, it wont let me use hostname. There are 2000+ records needing updating so I cant manually update each and also dont want to manually put the asset name in the spreadsheet for every asset as that would take just as long.
If you can think of another way to update all the Server assets with their costs I'd love to hear. I've asked Kace support for a way to directly modify the Kbox database, as with MySQL Workbench I could quickly bring up a table view of my assets and add in each cost value. But i've asked before and been told no.
Another way I've tried is going through the steps of editing the tables using Workbench (it fails but i can follow the wizard), then copying the SQL update statements it constructs into a helpdesk rule. At least that way I could make the all changes through Workbench then simply copy and paste the update statements into a rule. But I get lots of syntax errors when I try more than one update statement. One works but any more and it says the below
51:33> Executing Select Query...
51:33> selected 609 rows
51:33> Executing Update Query...
51:33> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
UPDATE ADVISORY SET TITLE='Example of a kb article TEST2' WHERE ID=1' at line 1] in EXECUTE("UPDATE ADVISORY SET TITLE='Example of a kb article TEST' WHERE ID=1;
UPDATE ADVISORY SET TITLE='Example of a kb article TEST2' WHERE ID=1;")
51:33> Ending: Wed, 29 Jun 2011 13:51:33 +0100
Any direction would be really useful.
Thanks
StuBox
0 Comments
[ + ] Show comments
Answers (5)
Please log in to answer
Posted by:
GillySpy
12 years ago
I would reimport the csv. You can use IP as the primary key but ALSO use the name field as the primary key. If you don't have one (i.e. it's blank) then just assign a blank column. But if the data does already have a name column then make sure it is in your data set (CSV).
Support can help you (at least part of the way if not all) with this since you are trying to use a feature that is not working as you expect.
Support can help you (at least part of the way if not all) with this since you are trying to use a feature that is not working as you expect.
Posted by:
stubox
12 years ago
So if I were to import the CSV below it should recognise the assets already exist based on the hostname (primary key), wont change the Asset Name, and update the cost? When I try this it wants to insert them as new records rather than update.
AssetName | Hostname | Cost
| server1 | 199
| server2 | 232
| server3 | 192
AssetName | Hostname | Cost
| server1 | 199
| server2 | 232
| server3 | 192
Posted by:
isolinear
12 years ago
Gilly, any luck that there will be a feature (in an k1000 update) to easily bulk edit assets in the future? importing works but doesn't seem very efficient to do every time you want to make changes to more than 1 device at a time, or have multiple technicians who need to mass update the assets they are responsibile for.
-Iso
-Iso
Posted by:
stubox
12 years ago
Had to give up waiting for Kace support due to time pressure and achieved what I wanted manually by reimporting assets from scratch and bulk updating similar assets using helpdesk rules i.e. 10 servers with similar hostnames and the same prices
UPDATE `ORG1`.`ASSET_DATA_35` SET `FIELD_163`=118.99 WHERE FIELD_169 like Server1%';
I've begged Kace support for a user/pass for write access to the database, it would make my life so much easier. And if I bricked the KBox then so be it, its my prerogative and if I cant get support afterwards thats fine.
isolinear - that would be great , I fear it only will be added to the K2000 and newer products though.
UPDATE `ORG1`.`ASSET_DATA_35` SET `FIELD_163`=118.99 WHERE FIELD_169 like Server1%';
I've begged Kace support for a user/pass for write access to the database, it would make my life so much easier. And if I bricked the KBox then so be it, its my prerogative and if I cant get support afterwards thats fine.
isolinear - that would be great , I fear it only will be added to the K2000 and newer products though.
Posted by:
cblake
12 years ago
Please make a feature request guys- http://kace.uservoice.com so engineering gets your feedback.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.