AuditWizard - Administration - SQL Import - Assets

From Layton Support
Jump to: navigation, search

Btn back up.png Back to Contents

Contents

Overview

As SQL Server is not maintained or supported by Layton Technology, our support can only be limited.

A CSV file can be prepared to match the columns of the ASSET table so that an import can be done. There is no need to import computers, as they can be audited and imported that way. This procedure would normally be done on non-auditable assets. Note that any user-defined data would have to imported separately.

Table Structure

The table structure is as follows:

   _ASSETID,_UNIQUEID,_NAME,_LOCATIONID,_DOMAINID,_PARENT_ASSETID,_IPADDRESS,_MACADDRESS,_LASTAUDIT,_HIDDEN,_REQUESTAUDIT,
   _AGENT_STATUS,_ASSETTYPEID,_MAKE,_MODEL,_SERIAL_NUMBER,_SUPPLIERID,_STOCK_STATUS,_AGENT_VERSION,_ALERTS_ENABLED,
   _OVERWRITEDATA,_ASSETTAG
Column Name Description
_ASSETID Serial number for internal reference. Exclude this column.
_UNIQUEID Another serial number used for internal reference to identify computers. It corresponds to Computer\HKEY_LOCAL_MACHINE\SYSTEM\HardwareConfig\LastConfig in the Registry. Leave this blank.
_NAME The name of this asset.
_LOCATIONID The _LOCATIONID from the LOCATIONS table. Look up the _LOCATIONID and enter the numeric ID, not the actual name. If in doubt, enter 1.
_DOMAINID The _DOMAINID from the DOMAINS table. Look up the _DOMAINID and enter the numeric ID, not the actual name. If in doubt, enter 1.
_PARENT_ASSETID The _ASSETID of a parent asset. Most likely not applicable, so enter 0.
_IPADDRESS Not applicable to a device. Leave this blank.
_MACADDRESS Not applicable to a device. Leave this blank.
_LASTAUDIT Not applicable to a device. Leave this blank.
_HIDDEN Not applicable to a device. Enter 0.
_REQUESTAUDIT Not applicable to a device. Enter 0.
_AGENT_STATUS Not applicable to a device. Enter 0.
_ASSETTYPEID The _ASSETTYPEID from the ASSET_TYPES table. Look up the Asset Type and enter the numeric ID, not the actual name. You may have to add an asset type in AuditWizard. If in doubt, enter 2 (PC).
_MAKE The make of the asset.
_MODEL The model of the asset.
_SERIAL_NUMBER The serial number of the asset.
_SUPPLIERID The _SUPPLIERID from the SUPPLIERS table. Look up the _SUPPLIERID and enter the numeric ID, not the actual name. If in doubt, enter 1.
_STOCK_STATUS One of four statuses to indicate whether an asset is in use or disposed. Enter 1 (in use).
_AGENT_VERSION Not applicable to a device. Enter 0.
_ALERTS_ENABLED Not applicable to a device. Enter 0.
_OVERWRITEDATA Records whether or not the Overwrite User-Defined Data is enabled. Enter 0.
_ASSETTAG Essentially the same as _NAME. Enter the same name.

Example CSV File

   _ASSETID,_UNIQUEID,_NAME,_LOCATIONID,_DOMAINID,_PARENT_ASSETID,_IPADDRESS,_MACADDRESS,_LASTAUDIT,_HIDDEN,_REQUESTAUDIT,
   _AGENT_STATUS,_ASSETTYPEID,_MAKE,_MODEL,_SERIAL_NUMBER,_SUPPLIERID,_STOCK_STATUS,_AGENT_VERSION,_ALERTS_ENABLED,
   _OVERWRITEDATA,_ASSETTAG
   ,TAB-01,1,1,0,,,,0,0,0,17,Apple Inc.,iPad Mini 2 16GB ME784LL/A,1234567890,1,1,,0,0,TAB-01
   ,TAB-02,1,1,0,,,,0,0,0,17,Apple Inc.,iPad Mini 2 16GB ME784LL/A,1234567891,1,1,,0,0,TAB-02
   ,TAB-03,1,1,0,,,,0,0,0,17,Apple Inc.,iPad Mini 2 16GB ME784LL/A,1234567892,1,1,,0,0,TAB-03
   ,TAB-04,1,1,0,,,,0,0,0,17,Apple Inc.,iPad Mini 2 16GB ME784LL/A,1234567893,1,1,,0,0,TAB-04
   ,TAB-05,1,1,0,,,,0,0,0,17,Apple Inc.,iPad Mini 2 16GB ME784LL/A,1234567894,1,1,,0,0,TAB-05

Btn back up.png Back to Contents

SQL Server Import Procedure

  1. Prepare your CSV file as per the Table Structure.
  2. In Microsoft SQL Server Management Studio, right-click the AuditWizard database and select Tasks > Import Data....

    Lsd sql import asset01.png


  3. Fill out the form as follows. Click Next:

    Lsd sql import asset02.png


  4. Fill out the form as follows. Click Next:

    Lsd sql import asset03.png


  5. In the Destination column, change it to [dbo].[ASSETS]. Click Next:

    Lsd sql import asset04.png


  6. In the On Error (global) and On Truncation (global) menus, change to Ignore. Click Next:

    Lsd sql import asset05.png


  7. Click Finish >>|:

    Lsd sql import asset06.png


  8. Click Finish:

    Lsd sql import asset07.png


  9. There will be warnings, but these can be ignored. You should see that a number of rows have been imported in the Copying to [dbo].[ASSETS] line. Click Close:

    Lsd sql import asset08.png


  10. In AuditWizard, check that the assets have appeared in the Network View tab.

Btn back up.png Back to Contents

Personal tools
Namespaces

Variants
Actions
Main Page
Online User Guides
General Support
Release Notes
Toolbox