Import Custom Fields with SQL

Earlier we explored the power of custom fields for keeping track of unique information on your computers. inventory_256But what if you need to track this custom information on 100s or more computers?

Adding this data in one computer at a time isn’t the most efficient use of a sys admin’s time. There are a number of ways to import custom items directly into your PDQ Inventory console in Pro or Enterprise mode. This is just one example.

In this example, we’ll use asset tags as an option for something you may want to track on each computer.

Try PDQ Inventory

Step 1: Create a CSV file

Create a CSV file containing two columns, the name of the computer followed by the value to import into the custom field. For example:

COMPUTER1,Asset tag 5
COMPUTER2,Asset tag 7

When entering dates and times, use a format that is recognizable on your computer. To ensure compatibility between different locales, you can use a standard date format of “YYYY-MM-DD HH:MM:SS”.

COMPUTER1,2015-01-05 04:14:32
COMPUTER2,2015-12-31 00:00:00

Step 2: Find the ID of the custom field

Open the SQLite console using Preferences > Database > SQLite Console. Execute the following SQL (replace Asset Tag with the name of the custom field):

select CustomComputerItemId from CustomComputerItems where Name like 'Asset Tag';

The number value you see is the number you will use later. If you have only one custom field, the number will most likely be 1.

Step 3: Import the CSV file from step 1

While still in the SQLite console, create a temporary table to hold the data with the following SQL:

create temp table CustomImport (name, value);

Import the CSV file with the following two commands:

.mode csv
.import C:/AssetTags.txt CustomImport

** Note that the SQLite console uses forward instead of back slashes in the path. **

Step 4: Update the computers with the asset tags.

Execute the following SQL to attach the asset tags to the individual computers. Replace the 1 with the ID number from step 2.

insert into CustomComputerValues (CustomComputerItemId, ComputerId, Value) 
select 1, ComputerId, Value from CustomImport, Computers 
where CustomImport.Name like Computers.Name;

These steps were originally published on our support site.