→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
rsellers
Engaged Sweeper II
We have/had been manually keeping up an Access DB for all PC on the network. One key field in the DB is the PC's location. Putting in a Asset location seems to be a manual process per device.

What I would like to know is there any documentation on how to import data from the Access DB into LanSweeper?
We figure the Asset serial number would be a key field to match since it's unique. I found the Import Assets Basic Action and downloaded the template.

Do I just extract the Serial number and location info from the Access DB and past that into the appropriate column of the spreadsheet then import the spreadsheet?
Do I need to populate any other fields in the spreadsheet before doing an import?

If there are already instructions, could someone provide a link as I did not see it.

Thanks in advance
1 ACCEPTED SOLUTION
Bruce_B
Lansweeper Alumni
Unfortunately the import template cannot be used to update existing assets. It was created to import assets that could not (yet) be scanned. We do not have a specific procedure for importing assets from an MS Access database. With this in mind your options for mass editing the location field for certain assets are:
  • Going to the assets tab, selecting all the assets from a matching location, using filters or by checking the boxes in front of matching assets by location, afterwards click the Mass Edit Assets button in the left pane (the top one), select Location and enter the location of your choosing.
  • Through the use of a database script (Configuration\Database Scripts) with which you update tblassetcustom.location based on the serial number, we've added an example database script below, we recommend backing up your database before running any scripts, just in case.


Update tblAssetCustom Set Location = 'Newlocation' Where Serialnumber = 'abc12345678';

View solution in original post

4 REPLIES 4
rsellers
Engaged Sweeper II
Getting back to this, I understand using the template. What I'm not sure is if this has to manually be typed into the template or can I use a txt file with 1 line per asset change and multiple lines in the text file?

From your example:
Update tblAssetCustom Set Location = 'Newlocation1' Where Serialnumber = 'abc12345671';
Update tblAssetCustom Set Location = 'Newlocation2' Where Serialnumber = 'abc12345672';
Update tblAssetCustom Set Location = 'Newlocation3' Where Serialnumber = 'abc12345673';
Update tblAssetCustom Set Location = 'Newlocation4' Where Serialnumber = 'abc12345674';
Update tblAssetCustom Set Location = 'Newlocation5' Where Serialnumber = 'abc12345675';

or something like
Update tblAssetCustom Set Custom01 = '2017' Where Serialnumber = 'abc12345676';
Update tblAssetCustom Set Custom01 = '2018' Where Serialnumber = 'abc12345677';
Update tblAssetCustom Set Custom01 = '2019' Where Serialnumber = 'abc12345678';
Update tblAssetCustom Set Custom01 = '2020' Where Serialnumber = 'abc12345679';

The goal is to add a custom field and populate the values extracted for each record in an Access DB. If successful, we may be able to move away from maintaining the Access DB since the bulk of the data is already pulled within Lansweeper.

Bruce_B
Lansweeper Alumni
Correct, one line per asset, no extra code should be required, just a variation of the script provided with different serials/locations.
rsellers
Engaged Sweeper II
Thanks Bruce,
Is there any other code going with this script? I'll assume it's one line per asset/serial #?
Bruce_B
Lansweeper Alumni
Unfortunately the import template cannot be used to update existing assets. It was created to import assets that could not (yet) be scanned. We do not have a specific procedure for importing assets from an MS Access database. With this in mind your options for mass editing the location field for certain assets are:
  • Going to the assets tab, selecting all the assets from a matching location, using filters or by checking the boxes in front of matching assets by location, afterwards click the Mass Edit Assets button in the left pane (the top one), select Location and enter the location of your choosing.
  • Through the use of a database script (Configuration\Database Scripts) with which you update tblassetcustom.location based on the serial number, we've added an example database script below, we recommend backing up your database before running any scripts, just in case.


Update tblAssetCustom Set Location = 'Newlocation' Where Serialnumber = 'abc12345678';