Filter on 2 things

PowerShell Help
1

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?

2

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
3

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) }
4

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

5

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
  1. 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
6

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.

7

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"
8

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.