PowerShell enthusiasts will appreciate the streamlined process we're about to share for exporting Custom Fields data to a .csv file. For those using the Central Server, this method is crucial since your Custom Fields data doesn’t automatically transfer to the shared database. Here's how you can efficiently move that valuable data into the new server's database. Exporting Custom Fields data with PowerShell involves these three simple steps:
Part 1: Identify Custom Fields to export
You will need to identify the correct Custom Fields that you wish to export.
1. Using SQLite, run this fairly easy query of the CustomComputerItems table where Custom Fields are stored.
$db = "C:\programdata\Admin Arsenal\PDQ Inventory\Database.db"
$sql = "SELECT Name FROM CustomComputerItems"
$CustomComputerItems = sqlite3.exe $db $sql
$CustomComputerItems
2. Take a screenshot, or leave the window open to keep the list of the Custom Fields names in front of you.
Part 2: Query for Custom Fields data.
Next, we’re going to create a fancy SQL statement to query for all the computers and their associated Custom Fields data.
1. Query the database using variables $db and $CustomComputerItems, then iterate with ForEach-Object
$sql = @"
SELECT
Computers.Name
$($CustomComputerItems | ForEach-Object {", Group_concat(Case When CustomComputerItems.name = '{0}' THEN CustomComputerValues.value END) as '{0}'`n" -f $_})
FROM Computers
LEFT JOIN CustomComputerValues on Computers.ComputerId = CustomComputerValues.ComputerId
LEFT JOIN CustomComputerItems on CustomComputerItems.CustomComputerItemId = CustomComputerValues.CustomComputerItemId
GROUP BY Computers.Name
"@
$result = sqlite3.exe $db $sql
$resultYou’ll notice that there is no header listed in the results and that all results are separated by “|”, the pipe character. This a the default behavior in SQLite that we will account for in the next step.
Part 3: Export results to file
Now that we have our data, we just need to get it into a file. You can export to your selected file type, however, a .csv file is required to use the Custom Fields Import Wizard.
1. Modify the line
$result = sqlite3.exe $db $sql2. Change the script to include headers and use a comma instead of the pipe character by using SQLite’s built-in dot command, (more info on those here).
$result = sqlite3.exe $db ".headers on" ".mode csv" $sql$result | Out-File "C:\temp\test.csv"Ta da! Now you’ve got a .csv file with all your Custom Field data!
Putting it all together
Here is the script all put together for copy-paste convenience.
$db = "C:\programdata\Admin Arsenal\PDQ Inventory\Database.db"
$sql = "SELECT Name FROM CustomComputerItems"
$CustomComputerItems = sqlite3.exe $db $sql
$sql = @"
SELECT
Computers.Name
$($CustomComputerItems | ForEach-Object {", Group_concat(Case When CustomComputerItems.name = '{0}' THEN CustomComputerValues.value END) as '{0}'`n" -f $_})
FROM Computers
LEFT JOIN CustomComputerValues on Computers.ComputerId = CustomComputerValues.ComputerId
LEFT JOIN CustomComputerItems on CustomComputerItems.CustomComputerItemId = CustomComputerValues.CustomComputerItemId
GROUP BY Computers.Name
"@
$result = sqlite3.exe $db ".headers on" ".mode csv" $sql
$result | Out-File "C:\temp\test.csv"Wrapping up
Now you have two different methods to export your Custom Fields to a .csv file. You can do it via creating a report in Inventory, or you can use PowerShell and feel extra fancy. Whatever the reason for exporting your Custom Field data to .csv file, if you’re interested in importing this into another PDQ Inventory database, please refer to this wonderful guide.
Loading...
