Access PDQ Info with Powershell

Access PDQ Info with Powershell

by | Sep 30, 2020 | Deployments, Information Technology, PowerShell

You read that right, access PDQ information with PowerShell. You will need the PS SQL Lite module first. This module will allow you to access the SQL lite server. The second thing you will need is a Powershell script on the server accessing the module information. Then finally you will need to be able to access that script remotely. Let’s install the PSSQLite first. Do this on the PDQ server as the script that will be interacting with the SQLite database is on that server. 

Install-Module -Name PSSQLite -RequiredVersion 1.0.3

Now we ill build a script on the PDQ server. The folder should be accessible by anyone you want to run this script. Let’s look at the starting points.

First we will import the module using the Import-Module command.

Import-Module PSSQLite

Next we need to declare the database. By default the database is located under the C > ProgramData > Admin Arsenal > PDQ Inventory > Database.db if it is not, you will need to declare it here. 

$Database = "C:\ProgramData\Admin Arsenal\PDQ Inventory\database.db"

Before we continue, this script is designed to grab just a single computer’s information. If you choose to grab more than one computer, this can cause PDQ to crash and even the server to crash. So, play itself and don’t be too greedy. With that said, Let’s grab the computers with the computer name. from the server. This is our first SQL query of the script. It’s also the most important as it gives us the Computer ID. Without it, nothing else will work. 

$Computer = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM Computers WHERE Name LIKE '$ComputerName'"

The Command to grav the SQL lite database information is Invoke-SQLiteQuery. Watch out because I made the mistake of adding an additional L while typing it. We select the data base as the data source. And the rest is very much like SQL commands. Inside our Query we select everything from the computers table where the name is like the computer name we provide. This gives us a lot of useful data that can be used else where. Once again, the ComputerID is the most important part of this data.

We will repeat this process but instead of looking for the name, we will be looking for the computerID inside our SQL Query from each table. 

Apps = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM Applications WHERE ComputerID LIKE '$($Computer.ComputerID)'"

Here we are selecting everything from the Application table where the ComputerID is like $($Computer.ComputerID). Without the first command the second command would not run. From here you can do each table like this until you create a list of data you want. You can place the data inside the [pscustomobject] and pump that out for use else where. Here is a full list of items I grab. 

[pscustomobject]@{
    Computer = $Computer
    CPU = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM CPUs WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    CustomInfo = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM CustomComputerValues WHERE ComputerID LIKE '%$($Computer.ComputerId)%'"                 
    Apps = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM Applications WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    PDQDeployments = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM PDQDeployments WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    DiskDrives = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM DiskDrives WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    Displays = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM Displays WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    EnvironmentVariables = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM EnvironmentVariables WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    HardwareDevices = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM HardwareDevices WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    HotFixes = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM HotFixes WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    LocalUsers = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM LocalUsers WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    LocalGroups = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM LocalGroups WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    LocalGroupMembers = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM LocalGroupMembers WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    MemoryModules = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM MemoryModules WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    NetworkAdapters = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM NetworkAdapters WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    LocalPrinters = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM Printers WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    ProductKeys = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM ProductKeys WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    Profiles = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM PowerShellScanner_4aa0b92dc0524234a90075fa296d3f84_View WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    Scanns = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM ScanProfileComputers WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    Services = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM Services WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    Shares = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM Shares WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    SharesPermission = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM SharePermissions WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    WindowsFeatures = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM WindowsFeatures WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    WindowsTaskSchedules = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM WindowsTaskSchedules WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    DesktopShortcuts = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM Files WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    Activation = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM PowerShellScanner_6162ffd7f94249d6a4964b5c08fad9b3_View WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    Bitlocker = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM PowerShellScanner_f609ba7492b54dbeaf1d7d72b05d50e4_View WHERE ComputerID LIKE '$($Computer.ComputerID)'"
    GPO = Invoke-SqliteQuery -DataSource $Database -Query "SELECT * FROM WMIScanner_31_View WHERE ComputerID LIKE '$($Computer.ComputerID)'"
}

I want to point out the Profiles object. This is the PowerShell scanner name for the profile size scanner. To grab the table names I suggest using the SQLite browser. You can download it here: Link.

Notice the bottom right hand side there is text. This text is the commands. You can browse around and find good information.

The call

Now you must create a function to call the script. This part is the easy part. We will be using the Invoke-command to trigger the script in question. 

$Test = Invoke-Command -ComputerName $ServerName -ScriptBlock { c:\PSScripts\PDQ-Calls\Get-PDQComputerData.ps1 -ComputerName $args[0] } -ArgumentList $Computer

Now test will provide a large number of information for you. You can sort this information however you like. It will look something like this:

if ($Quick) {
        [PSCustomObject]@{
            Computername      = $test.computer.Name
            IPAddress         = $Test.Computer.IPAddress
            MacAddress        = $test.Computer.MacAddress
            CurrentUser       = $Test.Computer.CurrentUser
            BootTime          = $test.computer.BootTime
            Manufacturer      = $test.computer.Manufacturer
            Model             = $test.computer.model 
            SystemFamily      = $test.Computer.SystemFamily
            Chassis           = $test.Computer.Chassis
            SerialNumber      = $test.Computer.SerialNumber
            CPU               = $test.CPU.name
            Memory            = "$($Test.Computer.Memory /1gb)/gb"
            DiskDriveSize     = "$($Test.DiskDrives.Size /1gb)/gb"
            OSname            = $Test.Computer.OSName
            PhysicalDiskType  = $Test.DiskDrives.PhysicalDiskType
            bitlocker         = $test.bitlocker.ProtectionStatus
            WindowsActivation = $test.Activation.StatusReason
            WebrootVersion    = ($test.apps | where-object { $_.name -like "*webroot*" }).Version
            TeamViewerVersion = ($test.apps | where-object { $_.name -like "*TeamViewer*" }).Version
        }
    }

The above code is part of a script that will give me quick information. As you see, it should be treated as an XML because the data is loaded like an XML.

One of the things I have done is used this inside my universal dashboard to allow quick information that is formatted nicely.

Watch out

  • Here are some gotyas. If the database is busy, sqlite will break down on you.
  • If the disk is busy, the SQL lite will break down on you.
  • Don’t make to many requests as it slows everything down.