Send-Email Alert with powershell

Today we had an issue with the power in one of our buildings. Only a section of the power would go off. This section did not include servers and did not have the battery backups that would alert via email if there was a power outage. I need a fast way to know when the power went out in this area while the electricians worked to find the problem over the week. Here was my solution.

Since the servers were not going down in this particular instance I could run a script on the server. It would ping the computer every so often and then if that computer was not reachable it would send an email to my account notifying me that the power was out for that section of the building or at least that computer had been turned off.

So here was the approach.
Check to see if the computer is on

Test-Connection computername

That was easy, but how do we set a condition with this to notify us only when the power is down?

If (Test-Connection -comp computername -count 1 -quiet) {
  Write-Host Online!
  }
Else {
  Write Host Offline!
  }

Good now we just need to find a way to send email through powershell. Now you can use the Send-MailMessage commandlet however that will not work if you do not have an smtp relay setup for your email. This especially will not work if you want to use a gmail account.

Here was a solution provided by another.

Function Send-EMail {
$emailSmtpServer = "mail.somesite.com"
$emailSmtpServerPort = "587"
$emailSmtpUser = "user@somesite.com"
$emailSmtpPass = "password1234"

$emailFrom = "IT Department <user@somesite.com>"
$emailTo = "user2@somesite.com"

$emailMessage = New-Object System.Net.Mail.MailMessage( $emailFrom , $emailTo )
$emailMessage.Subject = "Panel B Power Failure"
$emailMessage.IsBodyHtml = $true
$emailMessage.Body = @"
<p><strong>Power Has Failed on Panel B</strong>.</p>
<p>Power will need to be restored</p>
"@

$SMTPClient = New-Object System.Net.Mail.SmtpClient( $emailSmtpServer , $emailSmtpServerPort )
$SMTPClient.EnableSsl = $true
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential( $emailSmtpUser , $emailSmtpPass );

$SMTPClient.Send( $emailMessage )
}

Clearly you will need to change the username and passwords to fit your needs
Now all we need to do is insert the function where we had the write-host
we can even create a second function labeled  Send-AltEmail if you would like to get an email if the computer is online or offline and change the subject and message accordingly.

We are almost there. The last two steps are to put it in an endless loop and then insert a delay.
we can put the script in an endless loop by using the following lines of code.

While ($True)
{
IF (Test-Connection Computername -count 1 -quiet )
{
Send-EMail
}
ELSE
{
Send-AltEmail
}

Make sure you create your email functions first in the script. Put it all together with a start-sleep at then end choosing how long of a delay you would like to have. Now you have a complete solution to know the exact time that computer goes down.

Find Used Range in Excel Worksheet with Powershell.

Today's challenge was to open an excel file and then find the last used row and start adding information to it.
Here is how I chose to approach this. Please keep in mind this is building off the last posts and the variables set there. In this example I have not gone back and recreated each variable like I would if I were creating a complete script. This will not work unless you set these variables first.

First we need to open an existing file. We can do this by creating a path variable or we can just declare it directly. It might be handy to set a variable if you are going to make this a function or if you are going to call various excel files within one script.

$Excel.Workbooks.open("C:\Filepath\Filename.xlsx")

Next we are going to need to make the worksheet that we want to work in active
Again note this will not work unless you create the $worksheet1 variable. This has been shown in the previous post.

$WorkSheet1.Activate()

So far so good but now to see what we came here for. We have to get the last row in this worksheet

$LastRowUsed = $WorkSheet1.UsedRange.Rows.Count

However, we don't really want the last used row. If we choose to make this row active then we overwrite the existing data. I think we better add a row to that. Let's try something like this.

$LastRow = $WorkSheet1.UsedRange.Rows.Count + 1

Now that we have the correct row lets make it active

$Excel.Range("A" + $LastRow).Activate()

Now if you recall in our last script we were entering in data this way.

$WorkSheet1.Range("A1:A1").cells= "Cell a1)

However, I feel there is a better and cleaner way to write this that will make it easier for us to follow what we are doing when entering in variables for the row count. Lets try it this way.

$Worksheet1.Cells.Item($LastRow, 1).Value2 = "Look at this!"

I would like to point out that we can do the same with columns. If we run

$worksheet1.UsedRange | Get-Member
we can see that there is an option to use columns just like we did for rows. Pretty much the same thing really only we will now have to place the $LastColumn variable in the next position when entering in data. It would look something like this.

$Worksheet1.Cells.Item($LastRow, $LastColumn).Value2 = "Look at this!"

Today's sources are
http://stackoverflow.com/questions/8452408/using-powershell-to-append-a-table-to-the-end-of-an-excel-file-the-last-row
and to
http://social.msdn.microsoft.com/Forums/en-US/22b11766-6b33-451d-b43c-7596472bcd73/excel-read-using-powershell

Next time I will try and put something together on how to read a cell. Once I get done with all of this I will look into putting a series together on connecting with SQL. Oh the fun we can have with that!

Manipulate Excel with Powershell

Powershell can out put in a lot of different files and formats. The ability to easily customize its output is certainly one of its strengths. However, one day you might find yourself wanting to read information from an excel worksheet or have the ability manipulate and existing on. With powershell you can easily Open exisitng files, create new ones, point it to a particular cell, search through it for certain results, input data, add worksheets, rename them etc.

This article is only meant to be an introduction to working with powershell and Excel.

Lets start with how we will call up Excel to start working with it. You will need to create a variable that is calling up the COM Object.

$Excel = New-Object -COMObject Excel.Application

# Now we will want to make it so we can see what it is doing (for debugging purposes)
$Excel.Visible - $True

# Now we need to create a new workbook in excel
$Workbook = $Excel.Workbooks.add()

# If we want to remove the extra sheets we can run something like this
$Worksheet2 = $Workbook.Sheet | Where {$_.Name -eq "Sheet2"}
$Worksheet3 = $Workbook.Sheet | Where {$_.Name -eq "Sheet3"}
$Worksheet2.Delete()
$Worksheet3.Delete()

# You might also want to change the name of the workbook
$Worksheet1 = $Workbook.Sheets | Where {$_.Name -eq "Sheet1"}
$Worksheet.Name = "My Workbook"

#You can also change the properties of the workbook
$Workbook.author = "Powershell Master"
$Workbook.Title = "Example"
$Worekbook.Subject = "The Power of Powershell"

# Enter Data into the spreadsheet
$Worksheet.Cells.Item(1,1) = "This is My Title

# You can also make the cells a variables
$Row = 1

# This can let you sequentially go through excel and enter data in like this
$WorkSheet.Cells.Items($Row,1) = "First Name"
$WorkSheet.Cells.Items($Row,2) = "Last Name"
$Row++
$WorkSheet.Cells.Items($Row,$1) = "John"
$WorkSheet.Cells.Items($Row,$2) = "Doe"

# Now we might want to sort and auto fit a column
$Range1 = $WorkSheet.range('A2:D2500')
$Range2 = $Worksheet.Range('B2')
$Range1.Sort($Range2, 1)
$Range.entirecolumn.autofit()

## We can't forget to save the file
$Excel.Quit()

#If you take a look at your processes you will find that excel is still running. To completely close out with #additional commands.
[System.RunTime.IntropServices.Marshall]::ReleaseComObject ($Excel)
#Then remove variable since the comobject has been release there is nothing we can call from $excel
Remove-Variable $Excel



#I have tested most of this code and found it to work. Some of it I have not. Other things I have done in the past.
1. Open an existing excel file
2. Find last used row and start entering data on the unused row.
3. Read from certain cells and use that data as a variable.
4. Highlight Rows and change the color of fonts and even bold or Underline.

Please take a look at the sources for more information and examples.
Sources 
http://theolddogscriptingblog.wordpress.com/
http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/33dbc1c4-40ee-4ef8-b25b-f29440ab3194/
http://technet.microsoft.com/en-us/library/ff730962.aspx

Run Powershell against each computer listed in one CSV column.

Today's challenge was a continuation of a previous script. Here I wanted to take a CSV file with two columns. One Column with PCNAME and another with the header MAC. What I want to do here is keep a list of my computers with the MAC Addresses and Computer Names. This will make it easier when auditing the list making sure it is up to date and be able to find mistakes if I have the names associated with the MAC addresses. So here is how to do it.

One we are going to dot source our wake on LAN Function.
./Send-WakeOnLAN.ps1

Now set the path
$Path = X:\Folder\ComputerList.CSV
now for the tricky part
Import-CSV $Path | Select MAC | foreach {Send-WakeOnLAN $_.MAC}

Here we can see the obvious the Import-CSV and the select MAC
This will give us the column we want. However the problem with stopping there is that you will get @MAC=00:22:33:44 etc.
The reason is that it is including its header. Even if you write in such a way to not include the header you will end up getting only one of the many objects you are looking for.

This is the reason for piping it into a foreach command. Now you can send your WOL packet to all listed computers in one column within a csv.

Problem with changing network passwords

All of us admins have had this happen to us at least once. We need to change network passwords of system accounts. Either someone in the company has been let go or you are the new IT admin. Well if you don't have enough documentation to know where all the services are that use network accounts or special service accounts this can be problematic. Not to mention it could throw your users in complete chaos and havoc. One thing for sure it is a quick way to get yourself in trouble, make you and your department look bad and maybe even cause a loss of production time. Well now that we know it is bad what are we going to do about it. Well you could log into every server and check each service one by one. Depending on number of services and servers you may loose more than a day of your time to this. And well let's face it if you have to change passwords its better to do this as quickly as possible. What is the answer then?

Powershell!! Yes that is right in one quick line I can determine what accounts are being used for all services on a particular computer. If you are familiar Powershell then I am sure you have dealt with WMI already. Here we will use the
Get-WMIObject Win32_Service
Now all we need to do is add | Select-Object *
For example
Get-WMIObject Win32_Service | Select-Object *

That will give use the following and a lot more. Just take one of the services as an example to see what you need.

PSComputerName          : ServerName
Name                    : SomeService
Status                  : OK
ExitCode                : 0
DesktopInteract         : False
ErrorControl            : Normal
PathName                : "C:\Program Files (x86)\Common
                          Files\File\Path\SomeService.exe"
ServiceType             : Own Process
StartMode               : Auto
__GENUS                 : 2
__CLASS                 : Win32_Service
__SUPERCLASS            : Win32_BaseService
__DYNASTY               : CIM_ManagedSystemElement
__RELPATH               : Win32_Service.Name="SomeService"
__PROPERTY_COUNT        : 25
__DERIVATION            : {Win32_BaseService, CIM_Service, CIM_LogicalElement,
                          CIM_ManagedSystemElement}
__SERVER                : ServerName
__NAMESPACE             : root\cimv2
__PATH                  : \\ServerName\root\cimv2:Win32_Service.Name="SomSV
                          ch2Svc"
AcceptPause             : False
AcceptStop              : True
Caption                 : SomeService Scheduler2 Service
CheckPoint              : 0
CreationClassName       : Win32_Service
Description             : Provides scheduling for SomeServicecomponents' tasks.
DisplayName             : SomeService SomeService2Service
InstallDate             :
ProcessId               : 2200
ServiceSpecificExitCode : 0
Started                 : True
<b>StartName               : LogonName</b>
State                   : Running
SystemCreationClassName : Win32_ComputerSystem
SystemName              : ServerName
TagId                   : 0
WaitHint                : 0
Scope                   : System.Management.ManagementScope
Path                    : \\ServerName\root\cimv2:Win32_Service.Name="SOMSV
                          ch2Svc"
Options                 : System.Management.ObjectGetOptions
ClassPath               : \\ServerName\root\cimv2:Win32_Service
Properties              : {AcceptPause, AcceptStop, Caption, CheckPoint...}
SystemProperties        : {__GENUS, __CLASS, __SUPERCLASS, __DYNASTY...}
Qualifiers              : {dynamic, Locale, provider, UUID}
Site                    :
Container               :

Here we can see what we need. Name StartName and StartMode Now it looks like
Get-WMIObject win32_service | Select-Object name, startname, startmode If we want to pipe that to a log file we add | Export-CSV C:\filepath\filename.csv Now you might want to check more than one server. Easy enough all you need to do is the following. Create a list of the servers to check in a txt file
$Servers = Get-content c:\filepath\servers.txt
get-wmiobject win32_services | select-object Name, Startname, Startmode | Export-CSV C:\Filepath\FileName.csv

Oh but now that we are checking all servers we need to add one more thing to be able identify what server these services are on. We need to add __SERVER to the select-object parameter. And there you have it a quick way to find the services if. We could also add something to filter out the local and network accounts. Alas I did not go that far.

Power Shell Today

I absolutely love powershell and all the wonderful things you can do. Every day I work with it I find yet another thing it can do and another way it can automate my work process. Here was my problem. A series of about 11 servers on the network. Each server must be started in a particular order and be completely running before the next one gets started. The problem is when there is a power outage and with the current technology we have, I am required to be on site and start each server in the right order. Depending when the power goes down this may be when I am at another site or in the early morning hours. So the question remains is there a way to automate this without me being onsite and without spending a dime of the company's money. With powershell there is a way. So lets consider what we need. 1. First server power back on in the event of a power failure. 2. Start servers from script or remotely. 3. Check and verify not only that the server is started but services are started before proceeding to the next. 4. Repeat until complete with all servers. The first challenge can be addressed two ways. One we could have the server power up once power is restored through BIOS setting. However, there are some dangers to this. For instance if the power is fluctuating after full battery backup is depleted then that server might have several hard reboots. This would not be good for the power. Since I can get alerts when the power is down, I can setup the drac on this dell server for remote access and remote startup. However, since all servers do not have this I cannot do this with the rest. Now how to start up the next servers remotely or from script without DRAC? We could enable to WOL feature. Now some will say this is a security risk. Honestly it is not with a properly configured firewall. In addition since servers are supposed to be on all the time anyway the worst thing that could happen is that an attacker connected locally to the network with the MAC address of the server could turn it on. Not really going to be a problem. WOL will have to be enabled in the OS at the card and also enabled at the BIOS level. This will require a NIC with the right driver and a BIOS that is designed to do this. You might have to update one or both. If your server is really old you might not have this as an option. The next step is to create the WOL packet. I have chosen to do this in Powershell. No surprise there. I created a function based on this guys script found here. http://andrewmorgan.ie/2011/08/22/simple-wake-on-lan-powershell-module/

function send-wakeonlan{
    param(
        [string]$mac)
    if (!($mac -like "*:*:*:*:*") -or ($mac -like "*-*-*-*-*")){
    write-error "mac address not in correct format"
    break
    }

    $string=@($mac.split(":""-") | foreach {$_.insert(0,"0x")})
    $target = [byte[]]($string[0], $string[1], $string[2], $string[3], $string[4], $string[5])

    $UDPclient = new-Object System.Net.Sockets.UdpClient
    $UDPclient.Connect(([System.Net.IPAddress]::Broadcast),4000)
    $packet = [byte[]](,0xFF * 102)
    6..101 |% { $packet[$_] = $target[($_%6)]}
    $UDPclient.Send($packet, $packet.Length) | out-null
}
# Author ITBABLE.BLOGGER.COM This is an edited and altered scrip from the link below.
# source http://andrewmorgan.ie/2011/08/22/simple-wake-on-lan-powershell-module/

The reason I am making this a function is so that I can dot source this in a later script. It keeps the end result clean and simple. to dot source something just browse powershell to the right folder path and then ./script-name.ps1 Now to call the function I will only need to find the correct MAC Address to use and then enter it into the following command. send-wakeonlan -mac “00:11:22:33:AA:BB" There that was not so hard was it? Now for the next step. How to know when the server has fully started and is ready. For this I have adapted a script that was not exactly set to do what we are doing with it but it works great. Please understand I am a big believer in not rewriting what someone else has already done. I big thank you for those have gone before and worked out the bigger problems. This script I did not modify. Make sure to take a look at the original site I found it it on. Located here http://gallery.technet.microsoft.com/scriptcenter/PowerShell-queryService-94ecfac6

# This Function Will Query A Dependancy Service and wait until the timer expires OR for the service to start.

function Query-Service { param($Service,$timer1,$Computer)
    $success = ""
    write-host "Waiting on $Service Service..."
   
    # Create a for loop to INC a timer Every Second
    for ($b=1; $b -lt $timer1; $b++) {
            $servicestat = get-service $Service -ComputerName $Computer
            $status = $servicestat.status
            $b2 = $timer1 - $b

            # Determine the Percent Complete for the seconds.
            $percent = $b * (100 / $timer1)
           
            # Display the progress on the Screen
            Write-Progress -Activity "Waiting on $Service Service..." -PercentComplete $percent -CurrentOperation "$b2 Seconds Remaining" -Status "Current WMI Status: $status"
           
            # Determine if the Process is Running. If not, reloop. If so exit loop.
            if ($status -eq "Running") {
                write-host "$Service Service Started Successfully: $status in $b Seconds"
                [int]$b = $timer1    
                    $success = "yes"
               
                # Tells the Loop to Stop Incrementing as the Service is running
                Write-Progress -Activity "Completed" -Status "Current $Service Status: $status in $b Seconds" -Completed
               
            }
       
        # Start-Sleep is available for the write-progress. Its value is in seconds.
        start-sleep 1

    }
    # The script will now stop as the above loop has meet its time criteria and the success is not set to yes.time has expired.
    if ($success -ne "yes") {
        write-host "ERROR in Script: $Service Service Did Not Start In $timer1 Seconds. Status: $status"
        # Stop the Script
       BREAK
    }
}

# The service must be the actual executible name, not the friendly name
# The Below Examples are Querying SQL Services for startup waiting 120 seconds for a timeout.
# Source http://gallery.technet.microsoft.com/scriptcenter/PowerShell-queryService-94ecfac6
</blockquote>

Now we have all the pieces we need. All we need to do now is create a new script dot source the two functions and we have something that looks like this.
 . "c:\Scripts\Samples\QueryService.ps1"
. "c:\Scripts\functions\Send-WakeOnLan.PS1" #Wake SERVER1 send-wakeonlan -mac 00:11:22:AA:BB:CC" Queryservice "Some Service" "300" "192.168.1.10" #Wake SERVER2 send-wakeonlan -mac 11:22:33:BB:CC:DD"
Queryservice "Another Service" "300" "192.168.1.11"

 The cool thing is the script will hold and wait for as long as you tell it to. Here the wait is 300 seconds and you can change that based on how long the server can take to start. You can also add the names of multiple services to wait for on one server making sure all the needed ones have started. That is the quick and dirty part. Later it would be wise to enter error coding to send you an email alert if a service has failed. Or change it to move on any way gather the alerts and send them off in one email. That however, can get a little tricky and would be best to save for another post. When I have the time to take that on.