Using Where with Powershell + CSV

user_16644831 0
Viewed 149

I'm trying to read some device names from a .csv and compare them with AzureAD. The .csv in question only has one column with device names. Building the code from here: https://docs.microsoft.com/en-us/azure/active-directory/devices/manage-stale-devices

I'm modifying their code here:

$dt = (Get-Date).AddDays(-90)
Get-AzureADDevice -All:$true | Where {$_.ApproximateLastLogonTimeStamp -le $dt} | select-object -Property AccountEnabled, DeviceId, DeviceOSType, DeviceOSVersion, DisplayName, DeviceTrustType, ApproximateLastLogonTimestamp | export-csv devicelist-olderthan-90days-summary.csv -NoTypeInformation

Currently, my code looks like this:

$dt = (Get-Date).AddDays(-90)
$Names = @()
Import-Csv -Path C:\test123\Computers.csv | ForEach-Object {$Names += $_.Name}

Get-AzureADDevice -All:$true | Where {($_.ApproximateLastLogonTimeStamp -le $dt) -and ($_.DisplayName -eq $Names)} | select-object -Property DisplayName, ApproximateLastLogonTimestamp | export-csv oldcomputers.csv -NoTypeInformation

However, the csv that's created is blank. Is there some issue with how I'm searching the local .csv file?

powershell
1 Answers

To check if an element exist in an array you have several options (-in, -contains, .contains(..), etc). See containment_Operators for detailed info.

Try with something like this:

$dt = (Get-Date).AddDays(-90)
$names = (Import-Csv -Path C:\test123\Computers.csv).Name

Get-AzureADDevice -All:$true | Where-Object {

    $_.ApproximateLastLogonTimeStamp -le $dt -and
    $_.DisplayName -in $Names
    # => OR $Names -contains $_.DisplayName
    # => OR $Names.contains($_.DisplayName) => This one is case sensitive

} | Select-Object -Property DisplayName, ApproximateLastLogonTimestamp |
Export-Csv C:\path\to\oldcomputers.csv -NoTypeInformation

Edit

To explain what could possible be happening, as in my comment, -in and -contains are looking for an exact match on the $names array.

Example

'two' -in 'one','two','three' # => True
'twooo' -in 'one','two','three' # => False

'one','two','three' -contains 'two' # => True
'one','two','three' -contains 'tttwo' # => False

As a workaround, you could try using -match which would be looking for a partial match and also allows the use of Regular Expressions:

'two' -match 'one|two|three' # => True
'tttwo' -match 'one|two|three' # => True
'twooo' -match 'one|two|three' # => True

Try with this instead:

$dt = (Get-Date).AddDays(-90)

# NOTE: (...).Name => Where .Name is the header or column name, if the
# header is named differently change it here too  
$names = (Import-Csv -Path C:\test123\Computers.csv).Name -join '|'

Get-AzureADDevice -All:$true | Where-Object {

    $_.ApproximateLastLogonTimeStamp -le $dt -and
    $_.DisplayName -match $names

} | Select-Object -Property DisplayName, ApproximateLastLogonTimestamp |
Export-Csv C:\path\to\oldcomputers.csv -NoTypeInformation
user_153395440
Related