We’re going to look at using PowerShell and SQLite to update package file paths in PDQ Deploy. Trust me, it’s more exciting than it sounds. During the course of a recent file server migration, lots of scary warnings started popping up all over the place in my Deploy window! What happened? What was I to do?!
Our shared file paths were changing and all of my packages suddenly had incorrect file paths.This wouldn’t have been a problem if I had stored my PDQ Deploy packages in the Repository. That’s why it exists. The Repository and the $(Repository) variable allows us to update and reference paths with ease. In fact, that’s one of the biggest reasons that the $(Repository) variable was introduced in PDQ Deploy so very long ago in version 2.
Which sounds better: updating all your packages one at a time or by updating them all at once by changing a single value, the Repository path?(*psst* As IT admins, we all know the lazy course of action is best. It’s science.)Despite this great time-saving feature in PDQ Deploy, many IT admins (myself included) sometimes fail to include our packages in our Repository.
Sigh. Lesson learned. Store your package files in your repository to avoid this hassle!
For those of us that learned our lesson too late, this blog post is for you. Rather than change all of my hundreds of packages by hand (Pass! Boo! No thanks!), I figured that it would be easier to update them directly in the database. Using PowerShell and SQLite, this is a fairly straightforward task.
Enter PowerShell!
PowerShell and SQLite
Since PDQ products use SQLite for their local database, we have a couple different choices to use with PowerShell and SQLite.
Using a PowerShell provider to query the data
Piping in SQL (Structured Query Language) directly into SQLite
For simplicity, I’m going to go with piping a SQL query into PowerShell. All we need to do is provide the location of sqlite3.exe (if it’s not in your local PATH), the location of the database file, and the SQL commands that we wish to run. Here’s a quick example of using PowerShell and SQLite to view the Settings table in our PDQ Deploy database:
$db = "C:\programdata\Admin Arsenal\PDQ Deploy\Database.db"
$sql = "SELECT * FROM SETTINGS;"
$sql | sqlite3.exe $db
Update package file paths
Using the information above, we can apply it to the location of the individual file paths in the PDQ Deploy database. All the information about each step is in our database, so we simply need to know tables and values to update within our SQLite database. We’re going to update the path for Install Steps. Let’s break it down into the following steps:
Identify the records to update
Verify our syntax before making any change
Update the records
1 . Identifying the records
Let’s look for any records that start with the file path: \\MyOldFileShare\
$db = "C:\ProgramData\Admin Arsenal\PDQ Deploy\Database.db"
$sql = "SELECT FileName
FROM InstallSteps
WHERE FileName LIKE '\\MyOldFileShare\%';"
$sql | sqlite3.exe $db2. Verifying the syntax
We’re going to use the REPLACE command to find and replace the value \\MyOldFileShare\ with \\NewFileShare\Deploy\ for our files. This is still a SELECT command, so this will not update the database. We’re going to use this to verify our syntax.
$db = "C:\ProgramData\Admin Arsenal\PDQ Deploy\Database.db"
$sql = "SELECT REPLACE(FileName, '\\MyOldFileShare\', '\\NewFileShare\Deploy\')
FROM InstallSteps
WHERE FileName LIKE '\\MyOldFileShare\%';"
$sql | sqlite3.exe $db3. Update database with changes
Now that we have the correct syntax, we’re going to update the InstallSteps table with the Replace value that we identified in step 2. The UPDATE command doesn’t return any values, so I have added an additional SELECT command to run immediately after.
$db = "C:\ProgramData\Admin Arsenal\PDQ Deploy\Database.db"
$sql = "UPDATE InstallSteps
Set FileName = REPLACE(FileName, '\\MyOldFileShare\', '\\NewFileShare\Deploy\')
WHERE FileName LIKE '\\MyOldFileShare\%';"
$sql | sqlite3.exe $db
$sql = "SELECT FileName
FROM InstallSteps
WHERE FileName LIKE '\\NewFileShare\%';"
$sql | sqlite3.exe $dbNow that we’ve updated our database, we can see those pesky warning triangles disappear from our PDQ Deploy console. I simply switch back to my PDQ Deploy console and tap the F5 button to refresh:
Yes! No more warnings! PowerShell and SQLite to the rescue! Now we can deploy to our heart’s content once more.
Repository and variables: Paths made easy
First and foremost, if you are able to store all your files in your repository, that is what we recommend the most. If you are unable to store your files in your repository, our next best recommendation is to use a variable (Preferences > Variables) to store your file paths.
Once we do this, we can reference this variable directly within our packages.
Now, any time we need to update our file paths, we will only need to update our variables. Use the SQLite update examples from above and replace \\MyOldFileShare with either the repository or custom path variables.
Final notes
This only will update the file path for Install Steps. If you need help with other step types, please let me know. Hopefully, you will never need to update the file paths in all your packages. Because boo to that. If you do find yourself in a similar situation, however, PowerShell and SQLite can help you out. You should be able to use this blog post to update your install file paths within each of your PDQ Deploy packages. Give it a shot! This has personally saved me a lot of manual effort.
Did you know that PDQ Deploy has a PowerShell step you can use to deploy your scripts?
