AuditWizard - Administration - SQL Import - Assets
From Layton Support
(Difference between revisions)
m (Added "Back to Contents" link) |
m (Added description to "_UNIQUEID") |
||
| Line 23: | Line 23: | ||
|-style="vertical-align:top;" | |-style="vertical-align:top;" | ||
|_UNIQUEID | |_UNIQUEID | ||
| − | |Another serial number used for internal reference to identify computers. Leave this blank. | + | |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. |
|- | |- | ||
|-style="vertical-align:top;" | |-style="vertical-align:top;" | ||
| Line 116: | Line 116: | ||
<p align="right">[[File:btn_back_up.png|link=User Guide for AuditWizard]] [[User Guide for AuditWizard|<u>Back to Contents</u>]]</p> | <p align="right">[[File:btn_back_up.png|link=User Guide for AuditWizard]] [[User Guide for AuditWizard|<u>Back to Contents</u>]]</p> | ||
| + | |||
=SQL Server Import Procedure= | =SQL Server Import Procedure= | ||
#Prepare your CSV file as per the '''[[AuditWizard - Administration - SQL Import - Assets#Table Structure|Table Structure]]'''. | #Prepare your CSV file as per the '''[[AuditWizard - Administration - SQL Import - Assets#Table Structure|Table Structure]]'''. | ||
Latest revision as of 04:30, 3 June 2020
Contents |
[edit] 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.
[edit] 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. |
[edit] 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
[edit] SQL Server Import Procedure
- Prepare your CSV file as per the Table Structure.
- In Microsoft SQL Server Management Studio, right-click the AuditWizard database and select Tasks > Import Data....
- Fill out the form as follows. Click Next:
- Fill out the form as follows. Click Next:
- In the Destination column, change it to [dbo].[ASSETS]. Click Next:
- In the On Error (global) and On Truncation (global) menus, change to Ignore. Click Next:
- Click Finish >>|:
- Click Finish:
- 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:
- In AuditWizard, check that the assets have appeared in the Network View tab.
