Scanned registry value as column in inventory
-
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:
- 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" - 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
- Run a scan using the "Standard" Scan Profile
- 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
- 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.
Please sign in to leave a comment.
Comments
4 comments