Filter on 2 things

Welcome Forums General PowerShell Q&A Filter on 2 things

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

 
Participant
3 months, 1 week ago.

  • Author
    Posts
  • #110221

    Participant
    Points: 47
    Rank: Member

    Hi Guys, I am trying to filter some results based on 2 rules but can't seem to work it out.

    I have a CSV with 2 columns (Hostname, Profiles) and I want to filter an array based on if the Hostname matches AND if the Profile field is Null/Empty etc

    So I have this that filters out computers that are in my $imported array fine

    $Computers.DNSHostName | select-string -pattern $imported.Hostname -simplematch -notmatch
    

     

    But I only want to filter them if the field $imported.profiles is also not empty or null

    (This is obviously not going to work but might explain what I am trying to achieve better)

    $Computers.DNSHostName | select-string -pattern $imported.Hostname -and $imported.Profiles -not $null -simplematch -notmatch
    

    I tried piping it to a second select but that just matched one or the other and I want both matched

    Can someone point out how I do a second match?

  • #110222

    Participant
    Points: 878
    Helping Hand
    Rank: Major Contributor

    Did you check Where-Object cmdlet. This cmdlet is capable of filtering any object.

    If csv is
    HostName Profile
    ——– ——-
    H1 P1
    H2
    H3 P2

    $CSV = Import-Csv -Path C:\YourCsv.csv
    $CSV | Where-Object -FilterScript { ($imported.Hostname -contains $_.HostName) -and [String]::IsNullOrEmpty($_.Profile) }
    

    I suggest you to go through the help documentation

    Get-Help Where-Object -Full
  • #110234

    Participant
    Points: 319
    Helping Hand
    Rank: Contributor

    This ...

    $computers = Get-ADComputer -Filter *
    $Computers.DNSHostName only give the DNS host name string
    DC01.contoso.com
    EX01.contoso.com
    ...

    So, you can't do this at all, as you are trying, as noted by you already.

    # Past the DnsHostName  down the pipeline and match then name against Hostname and profiles
    $Computers.DNSHostName | select-string -pattern $imported.Hostname -and $imported.Profiles -not $null -simplematch -notmatch

    So,

    ($computers = Get-ADComputer -Filter * -Properties *)[0]
    ($adusers = Get-ADUser -Filter * -Properties *)[0]
    
    There is no Hostname or profiles property on Get-ADComputer or Get-ADUser

    So, you need to provide all the properties you need, then compare

    Simplify Your PowerShell Code by Using a Compound Where Clause
    https://blogs.technet.microsoft.com/heyscriptingguy/2012/04/14/simplify-your-powershell-code-by-using-a-compound-where-clause

    Snippet from the article —
    Where clause, the writer had the following:

    where {($_.status -eq "Running") -and ($_.CanStop -eq $true)}

    As I mentioned, it works. But it can be shortened by saying –and $_.CanStop. Here we are saying, “Does the CanStop property exist on the object?” If it does, this is all we want. Therefore, I can reduce this by saying, “Does it exist?” This is shown here.

    where { $_.status -eq 'running' -and $_.canstop }

    The nice thing is that I can negate the Boolean value. For example, if I want the running services that do NOT accept a Stop command, I have several choices. I would probably use the Not operator (!). This is shown here.

    where { $_.status -eq 'running' -AND !$_.canstop }

    Most of the time, I will use grouping around the Boolean value to make the code a bit clearer. This is shown here.

    where { $_.status -eq 'running' -AND !($_.canstop)

    If you do not like the exclamation point (!) for the Not operator, you also can use the –not operator. This is shown here.

    where { $_.status -eq 'running' -AND -not $_.canstop }

    Once again, I generally put grouping around the Boolean value to make the command easier to read (at least for me). This is shown here.

    where { $_.status -eq 'running' -AND -not ($_.canstop) }
  • #110432

    Participant
    Points: 47
    Rank: Member

    I had also tried Where but was unable to work it out too.

    Below is my whole script which might explain what I am trying to do better

    
    $CSV = Import-Csv -Path C:\Temp\WrongOU.csv
    
    $Computers = Get-ADComputer -Filter * -SearchScope OneLevel -SearchBase $OU | Where -FilterScript {$CSV.DNSHostName -notcontains $_.DNSHostName}
    
    $object = @()
    
    Foreach ($Computer in $Computers){
    
    $DNSHostName = $Computer.DNSHostName
    
    If (Test-Connection $DNSHostName -Count 1 -Quiet){
    
    $Names = Get-ChildItem -Directory "\\$DNSHostName\C$\Users" -ErrorAction SilentlyContinue | Where {$_.Name -notlike "Public" -and $_.Name -notlike "Admin*"} -ErrorAction SilentlyContinue | Select Name
    
    $line = [pscustomobject]@{
    
    DNSHostName = $DNSHostName
    
    Profiles = $Names.name -join ", "
    
    }
    
    $object += $line
    
    }
    
    }
    
    $object | Export-Csv -NoTypeInformation -Path C:\Temp\WrongOU.csv -Append
    
    

    What I couldn't work out is how to check to see if the profile field is empty during this check as I am looking for $_.DNSHostName anywhere in $CSV.DNSHostName (I didn't know how to check for the $CSV.DNShostname, then in the same row look to see if $CSV.profile empty or not.

    
    $Computers = Get-ADComputer -Filter * -SearchScope OneLevel -SearchBase $OU | Where -FilterScript {$CSV.DNSHostName -notcontains $_.DNSHostName}
    
    

    This is what I am hoping the script will do in the end, although, obviously I haven't got to most of it yet)

    1. Grab computers that are still in OU

    2. Filter out machines where it already found the Profile

    3. Check to see if the machine is ON

    4. If it is ON, then grab the profiles

    5. Add info to CSV

    6. Read CSV once a week and email anywhere it has found the computer and its profiles

    7. If computer no longer in $OU then delete it from CSV

  • #110434

    Participant
    Points: 319
    Helping Hand
    Rank: Contributor

    The order of this are not quite right, IMHO

    Based on what you are after the flow show be...
    Handle each thing one at a time to make sure you are getting what you'd expect then combine as one script.

    1. Grab computers that are still in OU

    # code to get the target OU sent to variable
    # code to the computers in the OU sent to a variable
    

    3. Check to see if the machine is ON, then grab the profiles

    # Loop and test connection of each computer
    # then On each computer, search for a user profiles.
    # code should filter out the default profile and default Administrator if used to build the computer
    # code to send / append to csv
    

    This should be a seperate script set as a scheduled task.
    6. Read CSV once a week and email anywhere it has found the computer and its profiles

    # Create scheduled task
    # script to read the OU for current computer and pass to a variable
    # If computer no longer in $OU then delete it from CSV
    # Read the file to compare pass the results to a variable
    # use regex to remove the record line and line space
    
    • #110438

      Participant
      Points: 47
      Rank: Member

      Let me try your order first. I thought that it is something that I should be able to do quite simple but just couldn't get my head around it.

      I also tried moving the filtering to a few different places but I always ran into the same issue.

  • #110458

    Participant
    Points: 319
    Helping Hand
    Rank: Contributor

    No worries.

    Here is something very rough, to give you a few ideas. Done in the ISE.

    # Very rough untested code, since I can't get to my lab right now to try this on my DC.
    
    ($TargetOU = ((Get-ADOrganizationalUnit -Filter *).Name) -match 'Domain COntrollers')
    ($TargetOuComputers = Get-ADComputer -Filter * -SearchBase "OU=$TargetOU, DC=$Env:USERDOMAIN, DC=com")
    
    ForEach($TargetOuComputer in $TargetOuComputers)
    {
        If(Test-Connection -ComputerName $TargetOuComputer.Name -Count 1)
        {
            $TargetOuComputerProfile = Invoke-Command -ComputerName $TargetOuComputer.Name -ScriptBlock{
            $path = 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList\*'
            Get-ItemProperty -Path $path | Select-Object -Property PSChildName, ProfileImagePath 
    
            If ((Get-ChildItem -Path $path).Count -ge 3) # if there are 3 or more, then more than the default profiles are on the host
              {  
                  $ReportData = $TargetOuComputer | 
                  Select-Object -Property Name,DNSHostName,SamAccountName,DistinguishedName
              }
            }
            $ReportData  | Out-File -FilePath "$env:USERPROFILE\Desktop\ComputerWithProfileReport.csv" -Append
        }
    }
    psEdit "$env:USERPROFILE\Desktop\ComputerWithProfileReport.csv"
    
  • #110797

    Participant
    Points: 47
    Rank: Member

    I finally managed to work out how to filter an array on another array. Below is my script and after feedback, it was decided that they wanted the currently logged on user, not profiles so there is that change.

    Please ignore all the horrible write-hosts, I've had headaches recently and it was easier to see the results at a glance.

    I'll run this a few times a day to catch users when they are on, but email out once a week so as not to spam people.

    The script will be redundant soon anyway when I get SCCM access and can pull this in directly from the DB.

    
    $CSV = Import-Csv "C:\Temp\WrongOU.csv"
    
     
    
    $OU = "OU"
    
    $Computers = Get-ADComputer -Filter * -SearchBase $OU -SearchScope OneLevel -Server Domain.Local
    
    $FilteredComputers = $Computers | Where-Object {$CSV.ComputerName -notcontains $_.Name}
    
     
    
    $object = @()
    
    $line = @()
    
     
    
    Foreach($Computer in $FilteredComputers){
    
    Write-Host "———-Start $($Computer.DNSHostName)———-" -BackgroundColor Yellow
    
     
    
    If(Test-Connection $Computer.DNSHostName -Quiet -Count 1){
    
    Write-Host "$($Computer.DNSHostName) (Found) – Start" -BackgroundColor Green -ForegroundColor DarkGreen
    
     
    
    IF((Get-WmiObject -ComputerName $Computer.DNSHostName -Class Win32_ComputerSystem -ErrorAction SilentlyContinue).name -eq $Computer.Name){
    
    $line = [pscustomobject]@{
    
    ComputerName = $Computer.name
    
    ComputerDomain = (Get-WmiObject -ComputerName $Computer.DNSHostName -Class Win32_ComputerSystem).Domain
    
    LoggedOnUser = (Get-WmiObject –ComputerName $Computer.DNSHostName –Class Win32_ComputerSystem).UserName
    
    }
    
    $object += $line
    
    Write-Host "Name Matched remote system and has been added to output" -BackgroundColor Green -ForegroundColor DarkGreen
    
    }
    
     
    
    Else{
    
    Write-Host "DNS doesn't match – Skipping" -BackgroundColor Red -ForegroundColor DarkRed
    
    }
    
    }
    
    Else{
    
    Write-Host "$($Computer.DNSHostName) not reachable via ping" -BackgroundColor Magenta -ForegroundColor DarkMagenta
    
    }
    
    Write-Host "———-End $($Computer.DNSHostName)———- `n" -BackgroundColor Yellow
    
    }
    
    $object | Export-Csv "C:\Temp\WrongOU.csv" -Append -NoTypeInformation
    
    

     

    Thanks everyone.

The topic ‘Filter on 2 things’ is closed to new replies.