How to important custom fields with SQL

Earlier we explored the power of custom fields for keeping track of unique information on your computers. But 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. 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.

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;