CODEWITHSUNDEEP
csvpowershell
user4317867
user4317867

Reputation: 2448

Lookup value in CSV and return value from 2nd column

I'm pulling a server list from SCOM and want to check this list against a CSV which contains the following data:

Computername,Collection Name
Server01,NA - All DA Servers - Patching - Cert - Thu 2:00
Server02,NA - All DA Servers - Patching - Prod - Wed 18:00

If the server is found, return the Collection Name. I'm just unsure what would be the best approach.

Import-Module OperationsManager

New-SCOMManagementGroupConnection -ComputerName SCOMsvr

$PendReboot = Get-ScomAlert -Criteria "Severity = 1 AND ResolutionState < 254 AND Name = 'Pending Reboot'" |
              Select NetbiosComputerName

$data = Import-Csv .\servers2.csv

$table = $data | Group-Object -AsHashTable -AsString -Property Computername

Edit - second attempt code:

$csv = Import-Csv D:\SCOM-Pend-Reboot.csv

$biglist = Import-Csv D:\servers2.csv

foreach ($line in $csv){
  $server = $line.NetbiosComputerName

  if ($server -eq $biglist.Computername) {
    "$server is in $biglist.'Collection Name'"
  } else {
    "$server is not found!"
  }
}

Upvotes: 3

Views: 15114

Answers (3)

user4317867
user4317867

Reputation: 2448

A case of over complicating things.

Thanks to this post my answer was as simple as:

IF(-not(Get-SCOMAlert)){Import-Module OperationsManager}

New-SCOMManagementGroupConnection -ComputerName SCOM-Server

Get-SCOMAlert -Criteria "Severity = 1 AND ResolutionState < 254 AND Name = 'Pending'" | Select NetbiosComputerName | Export-Csv -NoTypeInformation -Path D:\SCOM-Pend-Reboot.csv

$csv = Get-Content 'D:\SCOM-Pend-Reboot.csv'
Import-Csv D:\servers2.csv | Where {$_ -match $csv.computername} | Sort Computername

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200453

If you group the CSV data like that you'll have to access the information like this, because the value of the group item contains the Servername property as well:

$server = 'Server01'
$table[$server].'Collection Name'

That only makes sense if your CSV has more than one column. But even then I'd probably prefer a hastable of custom objects so that the server name appears only as the key:

$table = @{}
Import-Csv .\servers2.csv | ForEach-Object {
  $table[$_.Computername] = New-Object -Type PSCustomObject -Property @{
    'Collection Name' = $_.'Collection Name'
    'other property'  = ...
    ...
  }
}

If the CSV has just the two columns it'd be simpler to create the hashtable like this:

$table = @{}
Import-Csv .\servers2.csv | ForEach-Object {
  $table[$_.Computername] = $_.'Collection Name'
}

So that you can look up the name directly:

$server = 'Server01'
$table[$server]

Upvotes: 3

sodawillow
sodawillow

Reputation: 13176

You could do this:

Import-Module OperationsManager

New-SCOMManagementGroupConnection -ComputerName SCOMsvr

$PendReboot = get-scomalert -Criteria "Severity = 1 AND ResolutionState < 254 AND Name = 'Pending Reboot'" | Select NetbiosComputerName

$data = Import-Csv .\servers2.csv

$table = $data | Group-Object -AsHashTable -AsString -Property Computername

foreach($server in $PendReboot) {
    if($table.ContainsKey($server)) {
        ($table.$server)."Collection Name"
    }
}

Upvotes: 0

Related Questions