Welcome back to the show!
Last time, we showed you how to export your Custom Fields to a .csv file using Reports. If you are a true PowerShell fan, you might have been waiting with bated breath for this blog to see the magic happen in PowerShell. This will be very important for all of you using the Central Server who realized that all your precious data in Custom Fields did not transfer to the shared database automatically. So, how are you going to get all that fancy data into the database housed on the new server?! Well, so shall it be requested, so shall it be blogged. Exporting Custom Fields data with PowerShell can be broken down into the following three easy parts:
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.I hope that it helps.Happy PowerShelling!For a real-time demo, see the video listed below:
