Script for VLOOKUP in Powershell

Question

I am writing a Script which will fetch a list of Computers from AD having a specific Operating System and then will compare the 'Name' column of this sheet with another Workbook which has the list of Computers reporting to the AV Agent. Kind of like using the VLOOKUP Function to check the mismatch and then build a Pivot table and the Graph based on the same data. However ; I am kind of stuck on the comparison of "Names" as it is giving a blank sheet with the Indicators. I want the comparison table and with no indicators. Can that be achieved in Powershell? Any help on the same would really be great. The Code is as follows :-

import-module ac*

Get-ADComputer -filter { OperatingSystemVersion -Like '*6.1*' -and Enabled -eq "true"} -SearchBase 'OU=Computers,OU=IM,dc=miraje,dc=intr' -Properties '*' | Select Name,OperatingSystem,Status, OperatingSystemVersion, `
LastLogonDate,CanonicalName | Export-Csv -NoType "C:\Temp\ExportPC3.csv" -Encoding UTF8

$file1 = import-csv -Path "C:\Temp\ExportPC3.csv"
$file2 = import-csv -Path "C:\Temp\AV_Machines.csv"


$result = Compare-Object $file1 $file2 -property Name -IncludeEqual | Export-Csv -NoType "C:\Temp\ExportPC2.csv" -Encoding UTF8

If you can't yet get the data out of Active Directory, then it's not an Excel issue... yet! Check out [here](https://stackoverflow.com/questions/tagged/active-directory%20powershell?mode=all) and also the same tags over at [SuperUser](https://superuser.com/questions/tagged/active-directory+powershell) — ashleedawg, Oct 30 '17 at 08:55
The data is being fetched fine out of the AD. The issue is with the comparison or rather how to do a Vlookup using Powershell ? — Avik Chowdhury, Oct 30 '17 at 09:58
I must have misunderstood what you meant with the `Excel` tag and by ` ...'Names' as it is giving a blank sheet with the Indicators` ... `Vlookup` is not an command in Powershell, but I can see a number of possible alternatives [here](https://www.google.ca/search?q=lookup+table+powershell). — ashleedawg, Oct 30 '17 at 10:05
Here I have trying to automate the excel Vlookup function in powershell where I want to write a Powershell Script which can check and compare 2 columns in separate Workbooks and then give us the comparison result. — Avik Chowdhury, Oct 30 '17 at 10:18

iRon · Answer 1 · 2017-10-30T13:38:38.743

$AV_Machines = ConvertFrom-CSV @"
Host Name,OS,Version,Device,Type
AMSSVIATRS31,Win,Windows Server 2008 R2,Server
AMSSVIFLS32,Win,Windows Server 2008 R2,Server
AMSSVIPRTFLS31,Win,Windows Server 2008 R2,Server
ANTSRVATRS31,Win,Windows Server 2008 R2,Server
"@

$PC3 = ConvertFrom-CSV @"
PC Name,PC status,OS
BFK0852,Enabled,6.1 (7601)
BLVDLMBHYV2,Enabled,5.1 (2600)
BLVPACKAGING,Enabled,6.1 (7601)
BLVSAMSUNG2,Enabled,6.1 (7601)
BRG314F68G,Enabled,6.1 (7601)
"@

Using Compare-Object:

PS C:\> Compare-Object $PC3 $AV_Machines -Property "Host Name", "PC Name", "OS"
, "Version", "Device", "Type", "PC status" | FT

Host Name      PC Name      OS         Version                Device Type PC status SideIndicator
---------      -------      --         -------                ------ ---- --------- -------------
AMSSVIATRS31                Win        Windows Server 2008 R2 Server                =>
AMSSVIFLS32                 Win        Windows Server 2008 R2 Server                =>
AMSSVIPRTFLS31              Win        Windows Server 2008 R2 Server                =>
ANTSRVATRS31                Win        Windows Server 2008 R2 Server                =>
               BFK0852      6.1 (7601)                                    Enabled   <=
               BLVDLMBHYV2  5.1 (2600)                                    Enabled   <=
               BLVPACKAGING 6.1 (7601)                                    Enabled   <=
               BLVSAMSUNG2  6.1 (7601)                                    Enabled   <=
               BRG314F68G   6.1 (7601)                                    Enabled   <=

_{Note that I am not sure whether this suits your need as this will also include records where "Host Name" -eq "PC Name" but the given examples do not have any common name and it is still unclear what exactly you expect for output.}

Using Join-Object:

PS C:\> $PC3 | FullJoin $AV_Machines {$Left."Host Name" -ne $Right."PC Name"} |
 ft

Version                Type PC Name      OS         Device Host Name      PC status
-------                ---- -------      --         ------ ---------      ---------
                            BFK0852      6.1 (7601)                       Enabled
                            BLVDLMBHYV2  5.1 (2600)                       Enabled
                            BLVPACKAGING 6.1 (7601)                       Enabled
                            BLVSAMSUNG2  6.1 (7601)                       Enabled
                            BRG314F68G   6.1 (7601)                       Enabled
Windows Server 2008 R2                   Win        Server AMSSVIATRS31
Windows Server 2008 R2                   Win        Server AMSSVIFLS32
Windows Server 2008 R2                   Win        Server AMSSVIPRTFLS31
Windows Server 2008 R2                   Win        Server ANTSRVATRS31

In the bare form as it is defined above, it will basically it will compare the `Name` in the left table (`$file1`) with the right table (`$file2`) and if equal, merge and return all properties of both objects. But it can do a lot more, if you supply a sample of the `AV_Machines.csv` and a sample of the output you expect, I can be more specific. — iRon, Oct 30 '17 at 10:47
https://pastebin.com/GUWBg3HW I have pasted the sample for both the CSV files. So basically I want to compare the 'Name' Column from both the WORKBOOK and provide the Mismatch in the columns in a different spreadsheet and then build an Pivot table on the same. — Avik Chowdhury, Oct 30 '17 at 11:19
@Avik Chowdhury, afaik, `vlook` doesn't normally do a compare on what is *not* equal, let alone in two directions. Nevertheless, I have updated the answer accordingly — iRon, Oct 30 '17 at 12:58

score 1 · Answer 2 · answered May 12 '20 at 11:11

I had a similar problem to this and couldn't find anything on google so here was my solution in case it helps anyone else searching for the same thing:

$Addresses = Import-CSV -Path "C:\myfiles\mylookupdata.csv" 
#csv contains SAMAccountName,emailLower,


foreach ($entry in $VPNRepSum) {
        if (($Addresses.SAMAccountNameLower).contains($entry.username)) {
            write-output "Match found..."
            $tempemail = $Addresses | Where-Object {$_.SAMAccountName -eq $entry.username } | select-object -Expand emailLower
            $unique = $tempemail | get-unique
            Add-Member -InputObject $entry -MemberType NoteProperty -Name Email -Value $unique -Force
            } else {
            write-output "No match found."
            }
        }

The $VPNRepSum is just a report that I pulled into a system array (obviously this will be whatever you want to lookup against) and FYI is of the type below:

PS C:\Powershell> $VPNRepSum.gettype()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array

Also, remember that Get-Unique is case sensitive and I'm not aware that there's anyway to change that. I got around this by making sure everything was lowercase.

Script for VLOOKUP in Powershell

2 Answers2