PDQ.com mobilePDQ.com desktop
Support
Product keys PowerShell

Importing Product Keys into PDQ Inventory with PowerShell

Kris Powell
·

Once upon a time, I was asked how to go about bulk importing product keys into PDQ Inventory during a webcast.

Easy peasy, I thought, as I babbled on for a bit until I realized that I could write a blog instead.

So voila! Blog!

Preparation (and deep breaths)

First and foremost, this functionality already exists natively for Custom Fields. If that's what you're looking for, you should check out the Custom Fields Import Wizard.

If it meets your needs (and you're okay with putting your product key info into the Custom Fields), then it's going to be a fairly straightforward solution. In fact, you could even check out these links for more info (my fancy PowerShell blog or someone else's not-as-fancy article).

For the purpose of this blog, I'm going to assume that you want to use Product Keys (and not Custom Fields).

Where do we start

 We may or may not be using some fairly contrived examples for this blog (spoiler alert: we are), so please please please modify them to fit your various needs.

In prep for that, let's identify what we need:

• PowerShell (yay!) • SQLite (thrilling!) • A CSV file full of Product Key info (fascinating!) • Beverage of choice (yum!)

Contrived example CSV file:

Contrived example CSV file

In this particular CSV file, I am using all the fields that are available to use on PDQ Inventory's Product Keys page (Vendor, Product, Version, and Key).

As a bonus, seeing as how I am spectacularly creative with my names, the values are all sequential numbers, alternating between values for the computers FRY and ZAPP.

What about the Import Button?

What about the Import button, you ask? For individual machines, we can use the Import button to import a list of product keys for a single computer.

The import button

This option, however, will not distinguish between different computers within the same CSV file. It will ignore any computer names and import the entire CSV file into the Product Keys of the computer you're currently looking at (FRY in this case).

If I attempted to import our CSV file above directly into FRY, here is what the result would look like:

import our CSV file above directly into FRY

All gasping and surprise aside, that's not what we want at all! At least, I know that I don't want ZAPP's product key info in my record for FRY.

Now, you could split out each machine (and their keys) to their own CSV file. With that, you'd be able to import each of them one-by-one by clicking the Import button of each computer's Product Keys page.

That doesn't sound like a very good use of time. In fact, it sounds downright boring and awful, and I hate myself for even suggesting the very idea!

Instead, we're going to roll up our sleeves and have some fun adding the data directly into the database. Weeee!

A few (more) notes before we begin

Since we are going to be directly modifying the database, I highly recommend making a backup of our existing database on the off chance that we corrupt it. Directions can be found here.

Additionally, in order to avoid a locked database, I will also strongly suggest closing PDQ Deploy and PDQ Inventory and stopping their respective services.

It's easy with PowerShell, so fire away! (The Verbose part is optional, but I enjoy the extra info that's displayed.)

Stop-Service -Name pdq* -Verbose

Windows PowerShell Verbose

Importing Product Keys with SQLite and PowerShell

I know that there are a bunch of different ways to handle this. For the purpose of this blog, I'm just going to show a quick way that works for me.

We are going to batch all of our queries into a single transaction and pump it into our database. If you're interested in why we want to consolidate to a single transaction, check this link out.

Batching SQLite queries into a single transaction

In this example, we're going to do the following:

1. Import a CSV file ($ComputerList) 2. Create an empty array ($HoneyBunchesOfQueries) 3. Loop through our list of computers and build individual queries and add them to our array (the foreach section) 4. Sandwich the array values inside of a SQLite transaction ($Transaction) 5. Run that transaction against our database ($DBPath)

Deep breaths! Off we go!

$ComputerList = Import-Csv -Path 'C:\temp\Product Keys - LOTS.csv'
$DBPath = "C:\programdata\Admin Arsenal\PDQ Inventory\Database.db"
$HoneyBunchesOfQueries = New-Object System.Collections.ArrayList

# Go through each record in CSV and add each generated query to $HoneyBunchesOfQueries
foreach ($Computer in $ComputerList) {

$HoneyBunchesOfQueries.Add("
INSERT INTO ProductKeys (ComputerId, Vendor, Product, Version, Key)
SELECT Computerid, '$($Computer.Vendor)',
'$($Computer.Product)', '$($Computer.Version)', '$($Computer.Key)'
FROM Computers
WHERE Name LIKE '$($Computer.ComputerName)';")

}

# Build a single transaction with $HoneyBunchesOfQueries sandwiched in the middle
$Transaction = "
BEGIN TRANSACTION;

$HoneyBunchesOfQueries

COMMIT;
"

$Transaction | sqlite3.exe $DBPath

(The following screenshot includes some additional code for measuring the performance of our code.)

Addtional code to measure the performance of our code

Verifying our results

If all went well, FRY should have a bunch of odd-number entries between 1 and 10000 (ZAPP has the even-numbered entries).

We can verify our results by opening PDQ Inventory.

Product Keys 1Product Keys 2

Scrolling through the window shown above, we are able to see all the results that we expected!

It looks like it worked fantastically! Let's take a sip of our beverage and call it a day!

Wrapping up

If you find yourself needing to import a bunch of product key info into your PDQ Inventory database, I hope that you stumble upon this blog and that it is helpful to you.

Cheers and Happy PowerShelling to you!

Don't miss the next post!

How PowerShell can calculate your retirement

How PowerShell can track 401K growth

In this blog, I use PowerShell to focus and build a 401k calculator because its tax advantages make it ideal to build your retirement nest egg.

PowerShell