Scanned registry value as column in inventory

Follow

Mcgregor Chris

• January 20, 2021 08:00

I have added a scan to find the last Sam user of a PC which drags that from the windows registry of the machine. Is there any way to add this value as a column to the main inventory search page?

1

• 

  Dagannoth Rex

  • January 20, 2021 18:47

  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

  • January 20, 2021 22:51

  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

  • January 29, 2021 03:39

  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

  • January 29, 2021 07:12

  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.