locked
Find email in csv or excel file and send email RRS feed

  • Question

  • Hi friends, 

    In the office I work for, I am responsible for checking which servers are waiting to be restarted and notifying their owners.

    That's why I wrote the following script that checks the registry if a restart is needed and notifies me by email and I pass the email on to the owner of the server.

    $restart= invoke-command -scriptblock {test-path -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired"}
    if ($restart -eq $false) {Write-Host "false"}
    Else {
    
    Send-MailMessage –To 'systemowner@contoso.com' –From  'restartchecking@contoso.com' –SmtpServer 'EXC.contoso' –Subject “$env:COMPUTERNAME has restart pending” –Body “$env:COMPUTERNAME has restart pending” 
    
    }

    Now I want to automate the process of sending the email straight to the owner. I made a csv file like this:

    servername owneremail
    DC1	   alex@contoso.com
    DC2	   jack@contoso.com
    EXC	   Michael@contoso.com
    EXC0	   jord@contoso.com
    FSRV	   sami@contoso.com
    RDS	   dave@contoso.com

    So I'm looking for how to start changing the script to look up the server name in the file and send the email to the server owner's email address.

    Thanks

    Jon

    Tuesday, April 21, 2020 11:15 AM

Answers

  • The code isn't his, it's mine. And, yeah, there were syntactical errors in the code (and it is too long), but not logic errors.

    The loop just builds a hash to check the computer's name against his CSV. If it's present, send the mail to the owner.. If it's not, send the mail to him. Because he's running the code locally on each computer I figure there's a chance that the script file and CSV might become out of sync.

    I don't think he needs to know if a machine doesn't need a restart, that part (in his original post) was for him to know whether to notify the machine's owner.

    FYI to the OP, the file you posted isn't a CSV. Replace the spaces separating the column with a comma.

    $owners = @{}
    import-csv C:\junk\srv.csv |
        ForEach-Object{
            $owners[$_.ServerName] = $_.OwnerEmail
        }
    if (test-path -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired")
    {
        if ($owners.ContainsKey($ENV:COMPUTERNAME) ) {
            Send-MailMessage -To $owners[$ENV:COMPUTERNAME] -From . . . <fill in the rest>
        }
        Else{
            Send-MailMessage -To <YOU!>    # your CSV needs to be updated!
        }
    }
    


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by Jon Rush Tuesday, April 21, 2020 9:17 PM
    Tuesday, April 21, 2020 7:39 PM

All replies

  • Keeping to your original script, something like this should work for you:

    import-csv C:\junk\srv.csv |
        ForEach-Object {
            $restart= invoke-command -ComputerName $_.servername -scriptblock {test-path -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired"}
            if ($restart){
                Send-MailMessage -To $_.owneremail -From . . . <fill in the rest>
            }
        }
    


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, April 21, 2020 2:52 PM
  • Hi Rich,

    The script runs on each server locally, so the -ComputerName command doesn't fit here.

     Requires a command here to find $env:COMPUTERNAME in the csv file and then send an email to its owner.

    Tuesday, April 21, 2020 3:20 PM
  • If it runs locally, why use Invoke-Command???

    $owners = @{}
    import-csv C:\junk\srv.csv |
        ForEach-Object{
            $owners[$_.ServerName] = $_.OwnerEmail
        }
    if (test-path -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired")
    {
        if ($owners.ContainsKey($ENV:COMPUTERNAME) ) {
            Send-MailMessage -To $owners[$ENV:COMPUTERNAME] -From . . . <fill in the rest>
        }
        Else{
            Send-MailMessage -To <YOU!>    # your CSV needs to be updated!
        }
    }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Tuesday, April 21, 2020 3:42 PM

  • I ran it on three different servers and he did not find the servers in the import file. And the email was sent to me as defined in the Else

    $owners = @{}
    import-csv C:\ServersandOwners.csv |
        ForEach-Object{
            $owners.servername = $_.owneremail
        }
    if (test-path -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired")
    {
        if ($owners.ContainsKey($ENV:COMPUTERNAME) ) {
            Send-MailMessage -To $($owners.($ENV:COMPUTERNAME)) -From 'restartchecking@contoso.com' –SmtpServer 'EXC.Contoso' –Subject “$env:COMPUTERNAME has restart pending” –Body “$env:COMPUTERNAME has restart pending”
        }
        Else{
            Send-MailMessage -To 'system@contoso.com' -From 'restartchecking@contoso.com' –SmtpServer 'EXC.Contoso' –Subject “$env:COMPUTERNAME - Update The File” –Body “$env:COMPUTERNAME - Update The File”
        }
    }

    Tuesday, April 21, 2020 5:40 PM

  • I ran it on three different servers and he did not find the servers in the import file. And the email was sent to me as defined in the Else

    $owners = @{}
    import-csv C:\ServersandOwners.csv |
        ForEach-Object{
            $owners.servername = $_.owneremail
        }
    if (test-path -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired")
    {
        if ($owners.ContainsKey($ENV:COMPUTERNAME) ) {
            Send-MailMessage -To $($owners.($ENV:COMPUTERNAME)) -From 'restartchecking@contoso.com' –SmtpServer 'EXC.Contoso' –Subject “$env:COMPUTERNAME has restart pending” –Body “$env:COMPUTERNAME has restart pending”
        }
        Else{
            Send-MailMessage -To 'system@contoso.com' -From 'restartchecking@contoso.com' –SmtpServer 'EXC.Contoso' –Subject “$env:COMPUTERNAME - Update The File” –Body “$env:COMPUTERNAME - Update The File”
        }
    }

    Your code is syntactically, logically incorrect. You are looping on a file then never using the results.  Please look at what your code does on step at a time.


    \_(ツ)_/

    Tuesday, April 21, 2020 6:06 PM
  • The best guess we can make is the following.

    $owner = import-csv C:\ServersandOwners.csv | Where-Object{$_.ServerName -eq $env:COMPUTERNAME}
    $key = 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired'
    if (test-path $key){
        Send-MailMessage -To $owner.Mail -From 'restartchecking@contoso.com' –SmtpServer 'EXC.Contoso' –Subject “$env:COMPUTERNAME has restart pending” –Body “$env:COMPUTERNAME has restart pending”
    }else{
        Send-MailMessage -To 'system@contoso.com' -From 'restartchecking@contoso.com' –SmtpServer 'EXC.Contoso' –Subject “$env:COMPUTERNAME - Update The File” –Body “$env:COMPUTERNAME - Update The File”
    }
    


    \_(ツ)_/

    Tuesday, April 21, 2020 6:11 PM
  • The code isn't his, it's mine. And, yeah, there were syntactical errors in the code (and it is too long), but not logic errors.

    The loop just builds a hash to check the computer's name against his CSV. If it's present, send the mail to the owner.. If it's not, send the mail to him. Because he's running the code locally on each computer I figure there's a chance that the script file and CSV might become out of sync.

    I don't think he needs to know if a machine doesn't need a restart, that part (in his original post) was for him to know whether to notify the machine's owner.

    FYI to the OP, the file you posted isn't a CSV. Replace the spaces separating the column with a comma.

    $owners = @{}
    import-csv C:\junk\srv.csv |
        ForEach-Object{
            $owners[$_.ServerName] = $_.OwnerEmail
        }
    if (test-path -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired")
    {
        if ($owners.ContainsKey($ENV:COMPUTERNAME) ) {
            Send-MailMessage -To $owners[$ENV:COMPUTERNAME] -From . . . <fill in the rest>
        }
        Else{
            Send-MailMessage -To <YOU!>    # your CSV needs to be updated!
        }
    }
    


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Marked as answer by Jon Rush Tuesday, April 21, 2020 9:17 PM
    Tuesday, April 21, 2020 7:39 PM
  • I corrected the code in my post (and in my reply to JRV).

    Your "CSV" however, isn't a CSV. Replace the spaces separating the columns with a single comma.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, April 21, 2020 7:41 PM
  • Thank you. 

    The script is now working properly.

    Move all information to one column and comma to separate the data? like this: 

    servername,owneremail
    DC1,alex@contoso.com
    DC2,jack@contoso.com
    EXC,Michael@contoso.com
    EXC0,jord@contoso.com
    FSRV,sami@contoso.com
    RDS,dave@contoso.com

    Will the code work with this change or do we need to change the code?

    • Edited by Jon Rush Tuesday, April 21, 2020 8:17 PM
    Tuesday, April 21, 2020 8:06 PM
  • Yes, that's the correct format for the CSV.

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, April 21, 2020 8:18 PM
  • Will the code work with this change or do we need to change the code?

    Tuesday, April 21, 2020 8:20 PM
  • Hi jrv

    This code doesn't work properly and it sends the email even if the answer of test-path is false.

    Tuesday, April 21, 2020 9:19 PM
  • No need to change the code provided the CSV file is properly formatted.

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Tuesday, April 21, 2020 9:32 PM
  • Hi jrv

    This code doesn't work properly and it sends the email even if the answer of test-path is

    false.

    It is your test and not mine.  What are you testing?  That key may exist on all systems always.

    Your code is pretty vague.  


    \_(ツ)_/

    Tuesday, April 21, 2020 9:40 PM
  • What you are trying to do can be done without runn9ing it in every system.  If you are just trying to validate a CSV then that is simple.


    \_(ツ)_/

    Tuesday, April 21, 2020 9:42 PM
  • This key exists when a restart is required, for example, windows updates create this key if a restart is required to complete the installation of the update and after the restart key is deleted.

    The purpose of the script is to query whether a restart is required and notify the server owner to restart.

    Tuesday, April 21, 2020 10:55 PM
  • This key exists when a restart is required, for example, windows updates create this key if a restart is required to complete the installation of the update and after the restart key is deleted.

    The purpose of the script is to query whether a restart is required and notify the server owner to restart.

    That does not explain what the purpose of you code is. It appears to have conflicting purposes.  Are you trying to see if the computers in the file have that key or if the file is missing computers with that key?  This is the ambiguity between your code and comments.


    \_(ツ)_/

    Tuesday, April 21, 2020 11:38 PM
  • The file only contains the association between the computer name and the email address of the person responsible for the operation of that computer.

    The OP says that each computer runs its own copy of the script. I thought the same as you at first when I saw he was using Invoke-Command to run the Test-Path. I added the "-ComputerName" to the Invoke-Command and used the file to get the name of the remote machine, and that's when he said that the script was run locally, not remotely.

    The hash has nothing to do with getting the data from the registry. It's just used to 1) verify that the name of the computer running the script is in the CSV and 2) if it's there to get the email address of person that should receive the email. If the name of the local machine isn't in the hash, then send an email to some responsible person to correct the situation.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Wednesday, April 22, 2020 2:21 AM