Scanned registry value as column in inventory

Comments

4 comments

  • Dagannoth Rex

    No. The only way to display custom data as a column is to use Custom Fields, which scanners cannot populate.

    1
    Comment actions Permalink
  • Mcgregor Chris

    Not the answer i was hoping for but i guess that gives the folks at PDQ an opportunity for a new feature one day :)

    Thank's again Dagannoth Rex

    0
    Comment actions Permalink
  • Mcgregor Chris

    With the assistance of Corey from PDQ support a solution has been found. you are able to use the new PowerShell function to get the information from the PDQ database and import the data into the custom fields.

    Link to PDQ blog article: https://www.pdq.com/blog/adding-custom-fields-multiple-computers-powershell/

     

    My working solution:

    1. Create a new Custom field in PDQ inventory. This can be done via the Powershell integration but i found it faster just to use the PDQ Inventory app. 
      PDQ Inventory > Options > Custom Fields > New Field > Text > Enter Field name for me this was "Last SAM User"
    2. Next i added a Registry scan to the "Standard" scan profile.
      PDQ Inventory > Options > Scan Profiles > Standard > Add >
      Hive = HKEY_LOCAL_MACHINE
      Include Patterns = SOFTWARE\Microsoft\Windows\CurrentVersion\Authentication\LogonUI\LastLoggedOnSAMUser
      Row Limit = 1


    3. Run a scan using the "Standard" Scan Profile

    4. Run PowerShell using the "Run as Admin" option and use the script below and test to see if it works for you. I recommend pasting this script in Notepad++ and setting the Language to Powershell so you can see all the pretty colours before pasting in powershell to run :

      # Set the Path to your PDQ Database #
      $DBPath = "C:\programdata\Admin Arsenal\PDQ Inventory\Database.db"
      # Create SQLLite Query agains the Database to get the information. #
      # NOTE: SQLLite uses Double pipe "||" in place of the MSSql "+" to combine data. this query has been structured this way to format the data with a comma ready for use in the Temp CSV#
      $SQL = "select Computers.name || ',' || RegistryEntries.Value from Computers join RegistryEntries on RegistryEntries.ComputerId=Computers.ComputerId where RegistryEntries.name='LastLoggedOnSAMUser' and (RegistryEntries.Value is not null or RegistryEntries.Value <> '');"
      # Setting the contents of the varible "$Computers" to be the results of the SQL Querey #
      $Computers = sqlite3.exe $DBPath $SQL

      # Uncomment the line below to test your results before going further. #
      # WRITE-output $Computers

      # Setting the Name of the Custom filed the data will populate #
      $CustomFieldName = "Last SAM User"

      # Create Temp File to be read into PDQ Inventory #
      $TempFile = New-TemporaryFile
      $Computers | Out-File $TempFile

      # Import Contents of Temp CSV file into the custom Field of PDQInventory #
      # NOTE: As we are using the SQL to populate the Comma we wont be using a header so "-NoHeader" must be included and the computerColumn set to 1 as that is the first column. Like wise we are defining 2=$CustomFieldName as the 2nd column is the data we are importing into that custom field#
      PDQInventory.exe ImportCustomFields -FileName $TempFile -ComputerColumn 1 -CustomFields "2=$CustomFieldName" -NoHeader -AllowOverwrite


      Hope this helps someone else

    0
    Comment actions Permalink
  • Colby Bouma

    I'd recommend exporting a CSV from a Report/Auto Report instead of accessing the database directly, since that can cause corruption.

    If you choose to stick with direct access, I recommend using the -csv switch for sqlite3.exe instead of adding || ',' || to the query.

    0
    Comment actions Permalink

Please sign in to leave a comment.