How to realize it through Python — monitoring whether someone remotely logs on the server (detailed thinking + code)

Time:2021-4-19

@

First article:https://mp.weixin.qq.com/s/Lliuq-2rB6SIrcfkebv7Jg

origin

When multiple people are using the same remote server, if we want to know whether someone is using the server, we can’t log in directly, because it may squeeze the other party down. It’s not friendly, so here’s a way to monitor whether someone is connecting to the remote server

thinking

Encounter this problem, think about how to solve it

At the beginning, I wanted to monitor whether someone is connected to the server in an active way. That is to say, when I want to know whether someone is connected to the server, I run a script program and then return a result to me. It turns out that I didn’t solve this problem through this idea

Later, I wanted to monitor in a passive way, such as loading a script on the server and running the script every 5 minutes to determine whether someone is connected to the server. However, it also consumes resources. Later, I thought of a way to monitor the connection and disconnection of the server. Every time the server is connected and disconnected, I run the script to know whether the server is connected Someone’s connected

Put a flow chart

Here is a complete set of monitoring flow chart, including server monitoring and interface display

realization

Now that we have the idea, the next step is to realize it

It’s easy to implement

But we need to solve a few problems

  1. How to know if someone is connected to the server?
  2. How to monitor the connection and disconnection of remote server?

The CMD command netstat monitors port 3389

For the first question, how do you know if someone is connected to the server?

Here you can find out3389 portRemote desktopService port) andnetstatOrder, inwindonsIn the server, there is usually a remote connection port, that is to say, you can monitor this port to know whether there is a remote connection on the servernetstat(display protocol statistics and current TCP / IP network connection) is a CMD command, through which we can monitor the port condition

For example, we need to monitor port 3389 here

We first connect to the remote connection server, open the CMD command window, and enter the command:netstat -n -p tcp | find ":3389"

We get a result like this

TCP 10.11.174.373:55311 16.21.315.55:3389 ESTABLISHED

There are two IP addresses, one is 10.11.174.373, this is your local IP address, the other is 16.21.315.55, this is your server IP address

But when you run this command on the server, if there is a remote connection, you will get such a result (of course, your own connection is also counted). When no one is connected, you will get an empty one without any connection

Implementation of Python script

The script here has three functions:

  1. Get local IP address
  2. Get server connection status (IP)
  3. Relevant data is stored in the database

Although we use the netstat command to monitor port 3389 when there is a local IP, it only has IP information when there is a remote connection, but if there is no connection, it will not be available. Of course, you can also fill in the local IP, but this is not very good

Get the local IP, here we use the socket function in Python to achieve

socket.getaddrinfo(socket.gethostname(),None)[-1][-1][0]

The server connection in the back, it says

Database storage, also do not explain, directly up and down the code (here the database, please build)

#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   server_ip.py
@Time    :   2020/04/23 10:08:15
@Auspicious bird
@GitHub  :   https://github.com/jixn-hu
@CSDN    :   https://me.csdn.net/qq_37462361
'''


import os
import re
import pyodbc
import time
import socket

def now_time():
    """
    Formatting returns the current time
    :return:
    """
    now = int(time.time())
    local_time = time.localtime(now)
    format_now = time.strftime("%Y-%m-%d %H:%M:%S", local_time)
    return format_now

def open_184_db(db="rreporttask"):
    """
    Open local database
    : param DB: database (rreporttask by default)
    : Return: create a good database connection
    """
    print(now_ Time (),'connect 184 database% s% dB)
    driver = 'SQL Server'
    #Database IP
    serverName = '******'
    #Login user name and password
    userName = '******'
    passWord = '******'
    #Establish connection and get cursor
    conn = pyodbc.connect(driver=driver, server=serverName,
                          user=userName, password=passWord, database=db, charset="gbk")
    return conn


def insert_many(conn, items, table_name):
    """
    Insert multiple data into the database (Note: the inserted data contains the keywords in the table)
    : param conn: Database
    : param items: inserted data list Dictionary (the list contains dictionary types)
    :param table_ Name: database table name
    : Return: none
    """
    print(now_ Time (), "update data table {}...". Format (table)_ name))
    # print(now_time(),"item:", items)
    if items:
        cursor = conn.cursor()
        sql1 = "insert into %s" % table_name
        sql2 = "("
        sql3 = ") values("
        sql4 = ")"
        For key in items [0]. Keys(): # splicing SQL statements
            sql2 += "%s," % key
            sql3 += "?,"
            # sql4 += "%s=values(%s)," % (key, key)
        sql = sql1 + sql2[:-1] + sql3[:-1] + sql4
        item_values = []
        for item in items:
            item_values.append(list(item.values()))
        num = len(item_values)
        # print(num)
        print(now_ Time (),'a total of% s pieces of data need to be processed '% Num)
        # print(now_time(),"sql:", sql)

        try:
            for i in range(0, num, 1000):
                a = min(num, 1000 + i)
                # print(item_values[i:a])
                cursor.executemany(sql, item_values[i:a])
                conn.commit()
                print(now_ Time(), "currently processing% s data"% a)
        except Exception as e:
            print(now_time(), ' Update data failed, rollback ')
            print(e)
            conn.rollback()
    conn.close()


def main():
    items = []
    item = {}
    time.sleep(1)
    cmd = 'netstat -n -p  tcp | find ":3389"'
    command = os.popen(cmd)
    r = command.read()
    print(r)
    zz = r.split("\n")[0].split()
    if r=='':
        ip = "0000"
    else:
        local_ip = zz[1]
        local_ip = re.sub(r":.*$",'',local_ip)
        ip = zz[2]
    item["server_ ip"]= socket.getaddrinfo ( socket.gethostname (), none) [- 1] [- 1] [0] # local IP
    item["sign_ip"] = ip
    item["entrytime"] = now_time()
    items.append(item)
    print(item)
    conn = open_184_db()
    insert_many(conn,items,"server_sign")
    

if __name__ == "__main__":
    main()

You can package this Python script into an EXE, then put it on the server, set the trigger conditions, and run the script when someone connects and disconnects the server

As for how to set this trigger, this is the second problem, how to monitor the connection and disconnection of remote server?

Here, we can use the task planning program of Baidu and windons. Through this program, we can set the connection and disconnection of the EXE server, run the EXE script, put a picture, and then explore by ourselves

In fact, this is the end of the basic, detailed ideas and the main implementation, also have, we can directly query the database can know the server connection

Exhibition

However, in order to make it easier to see, I used flash to show the interface

I don’t want to explain more

Let’s put a result map first

Code directly

server_sign.py:

#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   server_sign.py
@Time    :   2020/04/07 10:47:52
@Auspicious bird
@GitHub  :   https://github.com/jixn-hu
@CSDN    :   https://me.csdn.net/qq_37462361
'''

import re
import pyodbc
import time
from flask import Flask,render_template

app=Flask(__name__)

def now_time():
    """
    Formatting returns the current time
    :return:
    """
    now = int(time.time())
    local_time = time.localtime(now)
    format_now = time.strftime("%Y-%m-%d %H:%M:%S", local_time)
    return format_now

def open_184_db(db="rreporttask"):
    """
    Open local database
    : param DB: database (rreporttask by default)
    : Return: create a good database connection
    """
    print(now_ Time (),'connect 184 database% s% dB)
    driver = 'SQL Server'
    serverName = '******'
    #Login user name and password
    userName = '******'
    passWord = '******'
    #Establish connection and get cursor
    conn = pyodbc.connect(driver=driver, server=serverName,
                          user=userName, password=passWord, database=db, charset="gbk")
    return conn


def select_one(conn, sql="select ios_id from ios_main"):
    """
    Query information
    :param time:
    :param type:
    :return:
    """
    print(now_ Time (), "querying database..."
    cursor = conn.cursor()
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        conn.commit()
        print(now_ "Data query succeeded")
        return result
    except:
        print(now_ Time (),'update data failed, rollback ')
        conn.rollback()
    conn.close()



@ app.route ('/ admin /')? Route() decorator tells flag what URL can trigger our function
def hello_world(name):
    conn = open_184_db()
    data_back = {}
    datas = select_one(conn,"select server_ip,sign_ip,entrytime from(select *,ROW_NUMBER()over(partition by server_ip order by entrytime desc)rank from rreporttask..server_sign) as z where rank=1 order by server_ip")
    for data in datas:
        if data[1]=='0000' or '3389' in data[1]:
            data_ Back [data [0]] = "not connected"
            Data [1] = "None"
        else:
            data_ Back [data [0]] = "connecting"
    # print(datas)
    return render_template("login.html",name=name,datas=datas,data_back=data_back)


@ app.route ('/ Hal /')? Route() decorator tells flag what URL can trigger our function
def hello_world1(name):
    return "hellodd {}".format(name)

if __name__ == "__main__":
    app.run (debug = true, host = 0.0.0.0 ')? Use the run() function to make the application run on the local server

login.html:

table.hovertable {
   font-family: verdana,arial,sans-serif;
   font-size:11px;
   color:#333333;
   border-width: 1px;
   border-color: #999999;
   border-collapse: collapse;
   }
   table.hovertable th {
   background-color:#c3dde0;
   border-width: 1px;
   padding: 8px;
   border-style: solid;
   border-color: #a9c6c9;
   }
   table.hovertable tr {
   background-color:#d4e3e5;
   }
   table.hovertable td {
   border-width: 1px;
   padding: 8px;
   border-style: solid;
   border-color: #a9c6c9;
   }
   
   
      Server login status view
      
         
            Server IP
            Login IP
            Connection time
            Connection status
         
         {% for data in datas %}
            
               {{ data[0] }}
               {{ data[1] }}
               {{ data[2] }}
               {{ data_back[data[0]] }}
            
         {% endfor %}

Mind map

Finally, put a mind map for reference

Follow me for more
Note: please indicate the source of the reprint, thank you_