Solved

Powershell - read multiple values from TSV text file

Posted on 2014-10-16
12
347 Views
1 Endorsement
Last Modified: 2014-10-20
I'm creating a script that will monitor the status of one or more services running on one or more servers and will send out an email alert if ever one of them is stopped.  Admins will modify a single TSV text file, add their server name, and add one or more services they want to monitor on that server separated by commas.

TSV input file:

    Hostname	Services
    S-UTILITY	Actserv,AdobeARMservice
    S-SCCM	RServer3,AdobeARMservice,VaultSvc

Open in new window


Code:

    Import-Csv C:\temp\services.txt -Delimiter "`t" | ForEach {
       $Service = $_.Services -split ','
       Get-Service -ComputerName $_.Hostname -Name $Service  | Select-Object | ft $_.Hostname, DisplayName,status -AutoSize
       }

Open in new window


This results in:

    S-UTILITY DisplayName                   Status
    --------- -----------                   ------
              Radmin Activation Server V1  Running
              Adobe Acrobat Update Service Running
    
    S-SCCM DisplayName                      Status
    ------ -----------                      ------
           Adobe Acrobat Update Service    Running
           Radmin Server V3                Running
           Credential Manager              Stopped

Open in new window


This is fine so far.  However, what I don't know how to do is I would like to be able to handle each of the services listed for a given server individually within a variable.  As it is now, within the ForEach loop, the value for $Service returns all of the entered services for a server:

    PS C:\Windows\System32\WindowsPowerShell\v1.0> $Service
    
    RServer3
    AdobeARMservice
    VaultSvc

Open in new window


...instead of just one at a time:

RServer3

In other words, I would like to run each listed service through the Get-Service command individually as I will later be using the variable to report on a specific service that is down on a specific server.

Thanks for looking!
1
Comment
Question by:curt2000
  • 7
  • 4
12 Comments
 
LVL 28

Expert Comment

by:becraig
Comment Utility
If I am reading you correctly something like this should work:
It first groups the unique server names into an array, then loads the csv based on server name and does what you need to based on service name:

$servers = (Import-Csv .\compsvc.csv -Delimiter `t | select Hostname | group-object -Property Hostname | select -expa Name)
$servers | % {
	$srv = $_
	import-csv -Delimiter `t .\compsvc.csv | ? { $_.hostname -eq $srv } | % {
		$Service = $_.Services -split ','
		Get-Service -ComputerName $_.Hostname -Name $Service | Select-Object | ft $_.Hostname, DisplayName, status -AutoSize
	}
}

Open in new window

0
 
LVL 1

Author Comment

by:curt2000
Comment Utility
Thanks for the feedback!  However, I found this altered code returns each service separately from the other services instead of them being grouped together.  $Service now holds an individual service instead of multiple services as before.

Import-Csv C:\temp\services.txt -Delimiter "`t" | ForEach-Object {
    $Hostname = $_.Hostname;
    $Services = $_.Services -split ',';
    $Services | ForEach-Object {
        $Service = $_;
        Get-Service -ComputerName $Hostname -Name $Service | ft $Hostname, DisplayName,status -AutoSize;
        }
    }

Open in new window

0
 
LVL 1

Author Comment

by:curt2000
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for curt2000's comment #a40385098
Assisted answer: 500 points for becraig's comment #a40384946

for the following reason:

The $Service variable now holds a single service instead of multiple services grouped together.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
That's too much, and you do not need to do it that clumsy way. More, becraig's code repeats the same error the original script has, and should not be accepted as a solution.
Import-Csv C:\temp\services.txt -Delimiter "`t" | ForEach-Object {
  Get-Service -ComputerName $_.Hostname -Name ($_.Services -split ',')
} | ft -a MachineName, DisplayName, Status

Open in new window

And since you want to filter for stopped:
Import-Csv C:\temp\services.txt -Delimiter "`t" | ForEach-Object {
  Get-Service -ComputerName $_.Hostname -Name ($_.Services -split ',')
} | ? { $_.Status -eq "Stopped" } | ft -a MachineName, DisplayName

Open in new window

0
 
LVL 1

Author Comment

by:curt2000
Comment Utility
Thanks Qlemo for chiming in.  I like your more efficient code and indeed it returns a nicely formatted table.  

My ultimate goal is to be able to assign a variable to the server name and another variable to the associated service name so that I can later use them as part of "alert emails" that I would send out if a service is found to be stopped.  I started with making a table just to see if I could get Powershell to correctly parse through the input file, given the way the data is arranged within the file.  And it was important to use just one file as the input source instead of two or more, so that other administrators who are not completely familiar with the system can just add their information to a single file.

I came up with the following code.  It gives me a variable each for the server name and service, which I would then use in an "If" statement to follow:

Import-Csv C:\temp\services.txt -Delimiter "`t" | ForEach-Object {
    $Hostname = $_.Hostname;
    $Services = $_.Services -split ','
    $Services | ForEach-Object {
        $Service = $_
        $DisplayName = Get-Service -ComputerName $Hostname -Name $Service | Select-Object -ExpandProperty DisplayName
        $Status = Get-Service -ComputerName $Hostname -Name $Service | Select-Object -ExpandProperty Status
        Write-Host $Hostname $DisplayName $Status
        }
        
    }

Open in new window


I know, it's ugly and probably a big "no no" because I'm running the Get-Service command twice, once just to select the DisplayName of the service and once to get the service status.  The server name is previously defined $Hostname.

So that's the trick.  My programming skills are weak and all I know how to do is cobble together scripts based on examples I see out there.  I'll post my completed script when it's "perfected", but if you know of a more efficient way to rearrange my above code I'd appreciate it.  I'll move points as well.  Thanks.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Not finished with the question yet, so objecting to keep the question open.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
Yes, you are correct - it is ugly, and the performance is degraded because of the duplicated call to Get-Service. I understand you used format-table as display and test option only - good move, and that shows that you know more about PowerShell than you know :D. Most folks will try to work further on the ft (= text) output instead of using the objects.

My second code snippet should be used and refined (btw, where-object and foreach-object are used so often that they have nice abbreviations as ? and %):
Import-Csv C:\temp\services.txt -Delimiter "`t" | % {
  Get-Service -ComputerName $_.Hostname -Name ($_.Services -split ',')
} | ? { $_.Status -eq "Stopped" } | % {
  Send-MailMessage -SmtpServer mx.your.domain -From script@your.domain -To admins@your.domain.com `
    -Subject 'Important service stopped' `
    -Body "The service '$($_.DisplayName)' on server '$($_.MachineName)' has been found dead"
}

Open in new window

Or, as you will prefer, using intermediate vars (which is ok, as their content is temporary and and will not consume much memory):
Import-Csv C:\temp\services.txt -Delimiter "`t" | % {
  Get-Service -ComputerName $_.Hostname -Name ($_.Services -split ',')
} | ? { $_.Status -eq "Stopped" } | % {
  $HostName = $_.MachineName
  $DisplayName = $_.DisplayName
  Send-MailMessage -SmtpServer mx.your.domain -From script@your.domain -To admins@your.domain.com `
    -Subject 'Important service stopped' `
    -Body "The service '$DisplayName' on server '$HostName' has been found dead"
}

Open in new window

Some folks say using the line continuation character (backtick at the very end of a line) is no good style, and right they are - if you make the mistake to put a space after the backtick, it does not work, and you won't see the error with ease. So, and that works for all parameters of cmdlets, you can use "splatting", that is using a hash table to store (static) parameters:
$mailparm = @{
  SmtpServer = 'mx.your.domain'
  From = 'script@your.domain'
  To = 'admins@your.domain.com'
  Subject = 'Important service stopped'
}
Import-Csv C:\temp\services.txt -Delimiter "`t" | % {
  Get-Service -ComputerName $_.Hostname -Name ($_.Services -split ',')
} | ? { $_.Status -eq "Stopped" } | % {
  Send-MailMessage @mailparm -Body "The service '$($_.DisplayName)' on server '$($_.HostName)' has been found dead"
}

Open in new window

0
 
LVL 1

Author Comment

by:curt2000
Comment Utility
Very nice Qlemo.  This section of your code answers my question precisely:

Import-Csv C:\temp\services.txt -Delimiter "`t" | % {
  Get-Service -ComputerName $_.Hostname -Name ($_.Services -split ',')
} | ? { $_.Status -eq "Stopped" } | % {
  $HostName = $_.MachineName
  $DisplayName = $_.DisplayName

Open in new window


Adding "$Service = $_.Name" also gets me the service name too, should I need it.

Is it true that the information pieces pulled from the text file are treated as objects instead of strings?  Is that the reason to use "ForEach-Object" instead of "ForEach"?  Is the text file I'm using in this case called a "hash table"?  I thought I read somewhere that items read from "hash tables" are treated as objects instead of strings.

I will award points to Qlemo unless anyone objects.

Thank you again!
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Is it true that the information pieces pulled from the text file are treated as objects instead of strings?
Import-CSV creates an array of objects with properties derived from either -Header or the first line of the CSV file. The object's properties will again be objects, of very simple (.NET) types (String, Int, Double).

Is that the reason to use "ForEach-Object" instead of "ForEach"?
No, this is not related.
You need to be careful with "foreach", as it can stand for the statement
 foreach ($i in 1..10)  {
or as an abbreviation for foreach-object
  1..10 | foreach-object  {
Both work with object collections. The statement does not process the pipeline.

Is the text file I'm using in this case called a "hash table"? I thought I read somewhere that items read from "hash tables" are treated as objects instead of strings.
No, the text file is still a text file :D. As described above, the internal objects are also no hash tables. My "splat" parameter $mailparm is one. I understand your confusion, because an object and a hash table can look very much alike (as PowerShell does a lot behind the scenes to keep that semblance), but they are different in a lot of ways. To increase confusion, see this:
New-Object PsObject -Property @{property1 = 'String1'; property2 = 1}

Open in new window

which creates an object from a hash table :D.
0
 
LVL 1

Author Comment

by:curt2000
Comment Utility
Nice explanations!  Thanks for taking the time to educate me.  I've learned a few things today.

When I'm done I'll post my finished script to share.

Thanks!
0
 
LVL 1

Author Closing Comment

by:curt2000
Comment Utility
The new, more efficient coding worked great to get exactly the results I was looking for!
0
 
LVL 1

Author Comment

by:curt2000
Comment Utility
Here's the finished script.  It monitors any number of services on any number of servers based on information entered in a single TSV text file.  

If a service is found to be stopped it will send a Lync IM, SMS, and email notification.  If the machine running the script has speakers it will also play an alert sound and speak the actual problem out loud.  Alert types can easily commented out for your environment.  The alert sound is a WAV file converted to base64 and embedded in the script, rather than referencing an external file.  

Sorry for all the commenting in the script.  Clean and remove as desired.

Thank you Qlemo for helping me figure out an efficient way to list servers and services!
MonitorServices.txt
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now