Search/Filter all properites of a variable

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Scott Windmiller Scott Windmiller 2 weeks, 3 days ago.

  • Author
  • #55162
    Profile photo of Scott Windmiller
    Scott Windmiller

    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.


  • #55165
    Profile photo of Rob Simmers
    Rob Simmers

    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.

  • #55178
    Profile photo of Scott Windmiller
    Scott Windmiller

    Great, thanks!

You must be logged in to reply to this topic.