PowerShell – Working with an Excel Range


I’m working on a server inventory project, and as part of that project, I need to add a field “Monitored” – this field will show either SCOM, Nimbus, No, or N/A.

N/A will mean the server has an exception to monitoring. These servers are in a text file called NonmonitoredServers.txt:

Server098
PRDServer198:PRDServer598
TestServer011:TestServer055

The “:” indicates a range of servers – All servers from PRDServer198 through PRDServer598, TestServer011 through TestServer055 and Server098 have an exception to monitoring.

The length of the server name, before the numeric part varies, so I can’t just do a $server.SubString(9) and expect to strip off only alphabetic characters. So, I do this:

001
002
003
004
005
006
007
008

009

010
011
012
013
014
015

016
017
018
019
020
021
022
023

$MonitoredServers = @{}
$Exceptions = Get-Content "NonmonitoredServers.txt"
foreach ($Exception in $Exceptions)
{
if ($Exception.Contains(":"))
{
#Exception is a range
$range = $Exception.Replace($Exception.Substring(`
0,$Exception.IndexOfAny("123456789")),"")
$prefix = $Exception.Substring(0,$Exception.`
IndexOfAny("123456789"))
$lowerRange = $range.split(":")[0]
$upperRange = $range.split(":")[1]
$range = $lowerRange..$upperRange
foreach ($ServerNumber in $range)
{
$MonitoredServers.add("$prefix$ServerNumber".`
ToUpper(),"N/A")
}
}
else
{
#Exception is a single device
$MonitoredServers.add($Exception.ToUpper(),"N/A")
}
}
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: