Monitor uptime and connectivity with PowerShell

Concept

Powershell is a command-line scripting environment that runs on Windows machines to implement system and application management automation. You can think of it as an extension of the command line prompt cmd.exe, no, it should be subversion. PowerShell needs. NET environment, while supporting it. NET object. The reason Microsoft is positioning PowerShell as power is not an exaggeration, as it fully supports objects, and the convenience of object-oriented is conceivable. Its readability, ease of use are very perfect.

In the operation of Windows Server, its role is very large. Simple and efficient and so on. Here I simply use a place where I apply to show it.

Background

Sometimes we need to monitor the connection from a particular user's computer to the database. For example, the user says he occasionally disconnects or sometimes has problems connecting to a program, and the database is my database. This problem really makes me anxious at first (fuck), because the problem is not always able to reproduce difficult to track. Even if you reproduce the error, the information that may be provided is not complete.

To help implement the troubleshoot of this kind of problem, I wrote two PowerShell scripts. The script can be unattended, and the log is queried after a certain period of time. I'm going to run this script on the user's PC, and the PC is on a different network or a different subnet. You can also run scripts on SQL Server servers at the same time. Generates three different log files and then compares them. These scripts are lightweight programs for CPU usage and very little load consumption. However, it is important to use a running query with caution, to query for small tables as much as possible, such as SELECT COUNT (*) from a small table.

Basically all of these scripts are implemented primarily by connecting to SQL Server and executing queries. The loop execution query knows to manually terminate it. If an error recurs, the log is logged to the log file and then connected again to execute the query.

OPENCONNQUERYCLOSECONN.PS1-The script is to open the connection database instance, run the SQL command, and then close the connection. This processing will be repeated again and again until you manually close it.

OPENCONNLOOPQUERY_LONG.PS1-The function of the second script is to open the connection, keep it open and then execute the query script repeatedly until you manually terminate the loop to interrupt the loop to close the connection.

Figure 1 The biggest difference is the loop position

Two scripts are both in terminal and log logging. We also want to see the error message in the log file if the connection is having problems. When the error message is in only one log file, it can sometimes lead us to discover the network or some particular kind of error.

Overview of PowerShell Scripts

First, let's take a look at the Openconnquerycloseconn.ps1 script. This script starts by setting the connection parameters first. Make sure that the following parameters are available.

The 21-27 rows are our connection parameters and query command variables.

Fig. 2 Main parameter variables

$serverName = "server name" $databaseName = "database name" $uid = "User ID" $pwd = "User Password" $sqlCommand = "command statement-such as querying the number of rows of a small table" $   logloaction= "Logs the location of the log file. ---c:\ps\OpenConnQueryCloseConn.log "

The bottom of the script is the main part of the loop.

Figure 3 Loop section

Fn_hitsql function Most of the work has been done. Open the database connection and then continue to execute the query. There are many places where the main job is logging. As follows:

Figure 4 The Log record section

There is also a function to log connections and errors. The Fn_writetolog function adds the error information to the terminal and then to the log file after attaching the date.

After doing all the above work, execute the script, and when the network connection problem is injured, a log file error log will be generated. As follows:

Error log information

The second foot (OPENCONNLOOPQUERY_LONG.PS1) is basically the same as in the first 1, which is followed by a loop statement following the execution of the statement to avoid closing the connection. Repeats the statement in a single connection. There is no more careful explanation here.

Running two scripts requires a PowerShell script, with the following instructions and, of course, the ability to spell it yourself:

PowerShell.exe -ExecutionPolicy Bypass -File C:\PS\OpenConnLoopQuery_Long.ps1
PowerShell.exe -ExecutionPolicy Bypass -File C:\PS\OpenConnQueryCloseConn.ps1

It should be noted that the path "C:\PS" should be set in advance.

Script:

No way to find out how to upload the script ..., which one of the great gods can tell. Specific script can ask me to leave a message. Send e-mail

Summarize

This article mainly introduces a self-developed script that uses PowerShell to implement a loop monitoring connection with network anomalies. It is essentially a circular connection query database that logs connection failures or query failures. Previously, both Windowsservice and other small programs were implemented through C # and other development tools. But it feels like PowerShell is easier to develop and less impact on machine performance, and of course Python is the same. The choice of using PowerShell primarily to learn the response syntax facilitates future operations of Windows Server. More and more we find the power of PowerShell, compared with the master there is still a lot of space to learn.

Monitor uptime and connectivity with PowerShell