Author Posts

October 10, 2016 at 6:57 pm

I have a PC inventory SQL database that I am accessing in PS and I want to be able to search the SQL query results. I am able to run the SQL query which writes the results to $PCTableInventory and I am able to filter those results more with the code below:

#Textbox with the search criteria
$PCInventorySearch = $textboxPCInventorySearch.text

#$PCTableInventory is the results of the original SQL query.
$PCInventoryResults = $PCInventoryTable | where {
($_.lastloggedin -like "*$PCInventorySearch*") -or ($_.Name -like "*$PCInventorySearch*") -or ($_.Manufacturer -like "*$PCInventorySearch*") -or ($_.Model -like "*$PCInventorySearch*") | select Name

This all works fine but what I was hoping to do was search all the properties or columns without having to specify each one.

Thanks,
Scott

October 10, 2016 at 8:19 pm

If you want to see if "*blue*" is contained in any of 10 columns, you are doing the query correctly. One thing you should do is to only return rows from the database that you need versus bringing the inventory locally and then searching it with a Where filter in Powershell. For instance, if you look at this sudo code:

$PCInventorySearch = "Blue"

$sqlCMD = @"
SELECT Name
FROM PCInventoryDB
WHERE LastLoggedIn LIKE '%$PCInventorySearch%'
OR Name LIKE '%$PCInventorySearch%'
OR Manufacturer LIKE '%$PCInventorySearch%'
OR Model LIKE '%$PCInventorySearch%'
"@

$results = Invoke-SQLCmd -Command $sqlCMD ...

Then you are filtering in SQL versus Powershell and your query should be much faster.