Important Notice: On February 29th, this community will be put into read-only mode. All existing posts will remain but customers will be unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

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

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!!!

0

Comments

1 comment
Date Votes
    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

Please sign in to leave a comment.

Didn't find what you were looking for?

New post