Search/Filter all properites of a variable

Welcome Forums General PowerShell Q&A Search/Filter all properites of a variable

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
2 years, 3 months ago.

  • Author
    Posts
  • #55162

    Participant
    Points: 0
    Rank: Member

    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

  • #55165

    Participant
    Points: 438
    Helping Hand
    Rank: Contributor

    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

    Participant
    Points: 0
    Rank: Member

    Great, thanks!

The topic ‘Search/Filter all properites of a variable’ is closed to new replies.