CODEWITHSUNDEEP
powershell
the1fan
the1fan

Reputation: 131

Powershell Compare for matches

I have two files, one with the servers listed in file A and I need to use that list to search file B for matches.

File A = Server Names (2 columns, Hostname/ServiceName)

File B = Server Metrics (3 columns, Hostname/ServiceName/ServiceOutput)

I need to use the two columns from "File A" to parse "File B" and when it finds a match it needs to write that row in "File B" to an output file. Each server should have three rows in the output file however, nothing is being written.

File A

host_display_name,service_display_name
US_Server1,Disk /
US_Server1,Disk /logs
US_Server1,mem

File B

Host_Name , ServiceName , Server_Ouput 

US_Server1,Disk/,Disk Metrics
US_Server1,CPU/,CPU Metrics 
US_Server1,Disk/logs,Disk Logs Metrics
US_Server1,APT,APT Metrics 
US_Server1,User Count,User Count Metric
US_Server1,Java,Java Metrics
US_Server1,mem,Memory Metric
NONUS_Server2,Disk/,Disk Metrics
NONUS__Server2,Disk/logs,Disk Logs Metrics
NONUS_Server2,CPU/,CPU Metrics 
NONUS_Server2,APT,APT Metrics 
NONUS_Server2,User Count,User Count Metric
NONUS_Server2,Java,Java Metrics 
NONUS_Server2,mem,Memory Metric 
US_Server2,Disk/ ,Disk Metrics 
US_Server2,Disk/logs,Disk Logs Metrics 
US_Server2,CPU/,CPU Metrics 
US_Server2,APT,APT Metrics 
US_Server2,User Count,User Count Metric
US_Server2,Java,Java Metrics 
US_Server2,mem,Memory Metric

Desired Output

Host_Name,ServiceName,Server_Ouput
US_Server1,Disk/,DiskMetrics
US_Server1,Disk/logs,DiskLogsMetrics
US_Server1,mem,MemoryMetric
US_Server2,Disk/,DiskMetrics
US_Server2,Disk/logs,DiskLogsMetrics
US_Server2,mem,MemoryMetric

CODE:

 $Path = "C:\Projects\Excel_Data_Powershell"
    $UserList = Import-Csv -Path "$($path)\Servers.csv"
    $UserData = Import-Csv -Path "$($path)\services.csv"
    $UserOutput = @()
    
        ForEach ($name in $UserList)
        {
    
            $userMatch = $UserData | where {$_.host_name -eq $name.service_name}
            If($userMatch)
            {
                # Process the data
    
                $UserOutput += New-Object PsObject -Property @{HostName =$name.host_name;column1 =$userMatch.service_name;column2 =$userMatch.service_output}
            }
            else
            {
            $UserOutput += New-Object PsObject -Property @{UserName =$name.hostnames;servicename ="NA";serviceoutput ="NA"}
            }
        }
    $UserOutput | ft

Upvotes: 1

Views: 62

Answers (2)

Santiago Squarzon
Santiago Squarzon

Reputation: 59772

This is how you could filter fileB based on fileA, using Group-Object -AsHashtable for reference filtering:

$fileA = Import-Csv -Path path\to\fileA.csv | Group-Object HostName -AsHashTable -AsString
$fileB = Import-Csv -Path path\to\fileB.csv

$desiredOutput = $fileB.Where({
    $_.ServiceName -in $fileA[$_.'Host_Name'].ServiceName
})
$desiredOutput | Export-Csv ..... -NoTypeInformation

Above should give you the following result:

Host_Name ServiceName Server_Ouput
--------- ----------- ------------
Server1   Disk /      Disk Metrics
Server1   Disk /logs  Disk Logs Metrics
Server1   mem         Memory Metric
Server2   Disk /      Disk Metrics
Server2   Disk /logs  Disk Logs Metrics
Server2   mem         Memory Metric
Server3   Disk /      Disk Metrics
Server3   Disk /logs  Disk Logs Metrics
Server3   mem         Memory Metric

Upvotes: 1

OttScott
OttScott

Reputation: 21

You're referencing host_name vs service_name in your where-object clause.

$UserData | where {$_.Host_Name -eq $name.HostName}

generates good results.

Upvotes: 0

Related Questions