SQL Report - All Computers That Are Members Of A Specific Collection

Alphaeus Mote

March 14, 2018 17:30

Using the following powershell code to get the PSSQLite Module

If (!(Get-PackageProvider -ListAvailable | Where-Object {$_.Name -eq "NuGet"})) {Install-PackageProvider -Name "NuGet" -Scope AllUsers -Force -ErrorAction Stop}

Install-Module -Name "PSSQLite" -Scope AllUsers -Force -Verbose -ErrorAction Stop

Import-Module -Name "PSSQLite" -Force -Verbose -ErrorAction Stop

$DB = New-SQLiteConnection -DataSource "\\YourServer\C$\ProgramData\Admin Arsenal\PDQ Inventory\Database.db" -ReadOnly

$Query = @"

Select
Computers.Name as "Name",
Computers.Manufacturer as "Manufacturer",
Computers.Model as "Model"
From Computers
"@

Invoke-SqliteQuery -SQLiteConnection $DB -Query $Query

This returns a native powershell object directly from the database which can be filtered, sorted, and piped. Its the best! I mainly like this because it allows me to design a collection, and when creating a rollout schedule, I can select 20 random workstations, 50 random workstations from the general population to receive an update or patch by placing them in a static collection. Allows for better tracking and so forth.

What SQL query can I use to find all computers that are a member of a specific collection?

Second question: Can PDQ make something like this available (Powershell Module or Built In), that allows adding the random results to a static collection?

Many Thanks In Advance!!!

Comments

1 comment

Permanently deleted user
March 26, 2018 23:47
1. This turns out to be a decent size query because multiple joins are required. I created an example SQL Report, but "PDQInventory.exe GetCollectionComputers" is WAY easier.
2. Thank you for the feature request. It’s important, as we've been a customer-driven product since the beginning. I've made a note of the request, but cannot provide a timeline when, or if, the request will be approved or, if approved, with what version.
```
SELECT
    Computers.ComputerId
    , Computers.Name
FROM
    Computers
INNER JOIN
    CollectionComputers ON Computers.ComputerId = CollectionComputers.ComputerId
INNER JOIN
    Collections ON CollectionComputers.CollectionId = Collections.CollectionId
WHERE
    Collections.Path = 'Collection Library\Applications\Imaging\GIMP\GIMP (Latest)'
AND
    CollectionComputers.IsMember = 1
AND
    <ComputerFilter>
```
0