I had a request to check connectivity to a SQL server and to email the results, however I thought of doing something different. Have the application check the connectivity but create an Event ID and Event Log entry on Success and Failure and you can monitor that with any application like SCOM, SolarWinds, Labtech and many others.

The first thing we need to do is create a new event log. You can do this by running the command below:

  • New-Eventlog -LogName Application -Source “ScriptName.PS1”

After that it is time to showcase our script. With the help of Adam’s script from this link, I was able to put everything together and his script had a step in that I missed initially.

I created Event ID 1 or success and Event ID 2 for failure/error, you can create any Event ID you want. This is the output in the Application Event Log.

Success:

Error:

You can now create a scheduled task and let this run every 30 min or whatever your preference is. Take note that when you create the scheduled task, you need to specify Arguments when you start a program (PowerShell), here is a sample of the “Add Arguments section”:

  • -NonInteractive -WindowStyle Hidden -command “& ‘C:\MyScripts\MyScript.ps1′”

Here is the code used, you more than welcome to customize it or update it you wish. Here are a few things you need to update.

  • ‘Source’ = ‘ ‘ – This is the name you registered with the command: New-Eventlog
  • $ipaddress = ” ” – Add the IP address of the server you are quering
  • $port = Add the port that will be open for the application, example is 1433
  • ‘Message’ = Update this message to what you prefer as this will appear in the Event Logs General Section.
$parameters = @{
 'LogName' = 'Application'
 'Source' = “ScriptName.PS1"
 }
$ipaddress = “11.111”.111.111
$port = xxxxxx
$connection = New-Object System.Net.Sockets.TcpClient($ipaddress, $port)
if ($connection.Connected) {
    $parameters += @{
    'EventId' = 1
    'Entrytype' = 'Information'
    'Message' = 'The server can connect to SQL'
    }
    Write-EventLog @parameters
    Write-Host "Success"
}
else {
    $parameters += @{
    'EventId' = 2
    'EntryType' = 'Error'
    'Message' = 'The server cannot connect to SQL'
    }
    Write-Eventlog @parameters
    Write-Host "Failed"
    
}

Hope it helps.


0 Comments

Leave a Reply