Is your PDQ Deploy console starting to get unorganized and messy but you’re unsure where to begin cleaning it up? Are you afraid of clearing out old/unused deployments in fear of removing something relevant and making a permanent mistake? Are you even more fearful of SQL/SQLite code?
Using SQLite queries can be a quick path to gaining insight into a solution you’re looking for. For many, scripting SQL queries is the bane of their existence and a curse of their comfort zone. The unknown is intimidating, and the idea of familiarizing with a coding language seems like too much extra work. The biggest hurdle is that customers may know what they need/want to filter, yet lack how to query it. They may also feel they lack understanding how certain clauses/commands are used and why they behave the way they do!
SQLite (from PDQ Deploy) to the Rescue
The ‘SQLite Console’ button in PDQ Deploy can help you generate lists/reports of filtered data you want/need from the database. Listed below is a query that should display a list of packages that were last deployed over ‘x’ days ago.
Where Do I Find the SQLite Console in PDQ Deploy?
- Click Options > Preferences (or press Ctrl+Comma) and select Database in the Preferences window.
- Click SQLite Console. This will launch a querying window similar looking to a command prompt window.
- Enter your query and watch the magic happen.
Display Packages Deployed More than ‘x’ Days Ago
Remember, you’re executing this query because your PDQ Deploy console is getting messy/unorganized with packages that haven’t deployed recently. Customers want to know which packages those are so you can remove them from your Deploy instance and keep the most recent deployments. Although this query will NOT remove or delete those packages (come on, you know we can’t be liable for that), it will give you a list of the packages you may want to remove based on the results.
Here is a query you can copy and paste below.
SELECT p.packageid, p.name, d.finished FROM packages p JOIN deployments d USING (packageid) GROUP BY p.packageid HAVING d.finished < datetime('now', '-20 days') ORDER BY d.finished DESC;
NOTE: The ‘-20 days’ in the query can be modified using any increment of time you desire (‘x’ number of minutes, hours, days, weeks, months, years, etc). For example, ‘-3 weeks’ or ‘-4 months’.
If this query looks like a foreign language, fear not, I’ll break it down below. The BLUE commands are 5 of the 6 major SQL clauses used for scripting queries to extract specific data from a database. The 6th clause, WHERE, is not present or necessary in this query. However, you will see this clause in the future. There is a specific order of execution for the clauses when writing SQL queries in which the query’s syntax should abide by for best results and practices (Additional blog post explaining this upcoming soon).
SELECT and FROM
The query starts by commanding the PDQ Deploy database to SELECT (aka return/output) the ‘PackageId’ and ‘Name’ columns FROM the ‘Packages’ table. But, we also need to see the last time those packages finished deploying. However, the ‘Packages’ table does not contain a column for deployment dates/times or even a ‘finished’ date; we use a JOIN command to tie the ‘Deployments’ table to the ‘Packages’ table.
To make the connection, we have to use a column that exists in both tables (typically called primary or foreign keys). In this case, we will be USING the ‘PackageId’ column.
We can now be specific about the columns we want our query to return from the ‘Deployments’ table. We query the ‘finished’ column by adding ‘d.finished’ to the SELECT clause.
Why the single letters?
These are merely shortcuts to save time. Listing the letter after the table name (‘packages p’ or ‘deployments d’) establishes an abbreviation for that table to use throughout the query and saves a whole lot of time.
For example: As you can see ‘deployments d’ has established using just the letter ‘ d’ to abbreviate the deployments table name. We can now type ‘d.finished’ instead of ‘deployments.finished’ to specify querying the ‘finished column’ of the ‘deployments table’.
The general format for specification would be:
SELECT packageid, packages.name, deployments.finished
In this query, it makes sense to use the HAVING clause because we want to know which packages have certain criteria. We’re looking for packages that have not been deployed for ‘x’ amount of ‘time increment’.
HAVING d.finished < datetime('now', '-20 days')
We are commanding the results HAVING a ‘finished’ datetime less than 20 days before now/today.
NOTE: The ‘less than’ operator ‘<‘ symbolizes a deployment’s ‘finished’ date that is earlier on a calendar than our increment (20 days ago) therefore has not been deployed in the last 20 days from now/today.
No, I didn’t forget to include this one.
IMPORTANT: When using the HAVING clause, the GROUP BY clause must be used and must be above the HAVING clause or you will get a syntax error. The next SQL blog will outline and explain the order of execution of SQL syntax.
Because we must have a GROUP BY clause, we will group the results by ‘p.packageid’ and we use this instead of the ‘p.name’ column because each package (including upgraded versions, etc.) has a unique ‘packageid’. Since packages do not require unique names, we avoid grouping by ‘name’, to prevent a data discrepancy.
Lastly, we want to know the dates and times these packages were last deployed (‘finished’) in descending (DESC) order. We use the ORDER BY clause to command the query results of the ‘d.finished’ column to be in descending order
REMEMBER: This query is not including packages that were deployed within the set increment and only including packages deployed earlier than the increment.
I hope this offers some insight into the way this query is executed, processed, and the results it returns.
Until next time!