locked
optimize my query RRS feed

  • Question

  • Hi,

    I would like to know what is one of the best possibilities to have this result :

    Name                     Build         number

    W10 Pro                 1703          10

    W10 Enterprise       1809            5

    W8                           x               20

    .....

    I found the command where I can have all these informations :

    Get-ADcomputer -filter * -Properties name,operatingsystem,operatingsystemversion| Where-Object { $_.operatingsystem -like 'windows*'} 


    My issue is I don't know how to format it correctly and "easely".

    I make this, but I think there is one easier way to do it...

    $req = (Get-ADcomputer -filter * -Properties *)| Where-Object { $_.operatingsystem -like 'windows*'} | group-object operatingsystem,operatingsystemversion | Sort-Object name -Descending 
    foreach ($entry in $req)
    {
        [pscustomobject]@{
            Name = $entry.name.split(",")[0]
            Build = $entry.name.split(",")[1]
            Number = $entry.count
        }
    }

    Result :


    Merci de marquer comme reponses les interventions qui vous ont ete utile.




    • Edited by matteu31400 Thursday, April 9, 2020 4:03 PM
    Thursday, April 9, 2020 12:19 PM

Answers

  • I would use:

    -Filter {operatingSystem -Like "Windows*"}

    and skip the Where clause.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Marked as answer by matteu31400 Thursday, April 16, 2020 12:47 PM
    Thursday, April 9, 2020 2:02 PM

All replies

  • I would use:

    -Filter {operatingSystem -Like "Windows*"}

    and skip the Where clause.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Marked as answer by matteu31400 Thursday, April 16, 2020 12:47 PM
    Thursday, April 9, 2020 2:02 PM
  • In your post you have a "Build" column, but the value in that column is the "ReleaseID" (e.g. "1903").

    Name                 Build        number
    W10 Pro              1703         10
    W10 Enterprise    1809         5
    W8                      x              20

    The Get-ComputerInfo dies return that value (in the WindowsVersion property) but, sadly, it doesn't work on remote machines. That means you'll have to use Invoke-Command to run it on a remote computer, or get the value from registry on the remote computer (again, with Invoke-Command).

    I think this might be a good way to get the information from the remote machines:

    invoke-command -ComputerName (Get-ADComputer -Filter {operatingSystem -Like "Windows*"} | Select -Expand Name) -ScriptBlock {Get-ComputerInfo -Properties WindowsVersion,WindowsProductName}


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Thursday, April 9, 2020 3:16 PM
  • Thanks for both answer.

    I test you solution Richard mueller and it's better than mine :) less time (because minus pipeline I guess).

    Rich matheisen, unfortunately your answer is not what I need ^^. I need to use AD to obtain these information because there is no need for all my computers to be online when I do my cmdlet.

    But you're totally right about "build" I'm going to change it :)


    Merci de marquer comme reponses les interventions qui vous ont ete utile.

    Thursday, April 9, 2020 4:01 PM
  • When you use the -Filter parameter, the filtering happens on the DC, which should have an OS optimized for such queries. Everything after the first pipe is processed on the client computer. Also, with the filter a smaller recordset is communicated over the network from the DC to the client. In a large environment this can make a significant performance difference.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Thursday, April 9, 2020 5:42 PM
  • Perfect :)

    Thanks you very much for your help

    My final query is :

    $param=@{
        filter = "operatingsystem -like 'windows*'"
        properties = "operatingsystem","operatingsystemversion"
    }
    $req = (Get-ADcomputer @param) | group-object operatingsystem,operatingsystemversion | Sort-Object name -Descending 
    foreach ($entry in $req)
    {
        [pscustomobject]@{
            Name = $entry.name.split(",")[0]
            ReleaseID = $entry.name.split(",")[1]
            Number = $entry.count
        }
    }
    



    Merci de marquer comme reponses les interventions qui vous ont ete utile.


    • Edited by matteu31400 Thursday, April 16, 2020 12:46 PM
    Thursday, April 16, 2020 12:38 PM