# import geoip2.database # def get_user_location(ip_address): # try: # gi = geoip2.database.Reader('/var/www/html/ovpn-moniter/GeoLite2-City.mmdb') # response = gi.city(ip_address) # return { # 'country': response.country.name, # 'region': response.subdivisions.most_specific.name, # 'city': response.city.name, # 'latitude': response.location.latitude, # 'longitude': response.location.longitude # } # except geoip2.errors.AddressNotFoundError: # return None # except Exception as e: # print(f"Error: {e}") # return None # # Example usage # public_ip = '152.58.61.41' # location_data = get_user_location(public_ip) # print(f"IP: {public_ip} => Location Data: {location_data}") #---------------------------------------------------------------------- # import requests # def get_user_location(public_ip): # response = requests.get(f"http://ip-api.com/json/{public_ip}") # data = response.json() # if data['status'] == 'success': # return { # 'country': data.get('country', ''), # 'region': data.get('regionName', ''), # 'city': data.get('city', ''), # 'latitude': data.get('lat', ''), # 'longitude': data.get('lon', ''), # } # else: # return None # # Example usage # public_ip = '152.58.34.153' # Replace with the actual IP # location_info = get_user_location(public_ip) # print(location_info) #------------------------------------------------------------------------ # import geoip2.database # # Path to your GeoLite2-City.mmdb database file # db_path = '/var/www/html/ovpn-moniter/GeoLite2-City.mmdb' # # Initialize the GeoIP2 reader # reader = geoip2.database.Reader(db_path) # # The IP address to look up # ip_address = '152.58.34.153' # # Perform the lookup # response = reader.city(ip_address) # # Extract the information # city = response.city.name # country = response.country.name # region = response.subdivisions.most_specific.name # latitude = response.location.latitude # longitude = response.location.longitude # # Display the information # print(f"City: {city}") # print(f"Region: {region}") # print(f"Country: {country}") # print(f"Latitude: {latitude}") # print(f"Longitude: {longitude}") # # Close the reader when done # reader.close() #--------------------------------------------------- # import pygeoip # def get_location_from_pygeoip(ip_address): # # Path to your GeoLiteCity.dat file # gi = pygeoip.GeoIP('/var/www/html/ovpn-moniter/GeoLiteCity.dat') # Change this to your actual path # location = gi.record_by_addr(ip_address) # if location: # return { # 'city': location.get('city'), # 'region': location.get('region'), # 'country': location.get('country_code'), # 'latitude': location.get('latitude'), # 'longitude': location.get('longitude'), # } # else: # print(f"IP address {ip_address} not found.") # return None # # Example usage # remote_ip = '152.58.34.153' # Replace with the actual IP address # location_info = get_location_from_pygeoip(remote_ip) # print(location_info) # import re # import subprocess # status_data = """ # OpenVPN CLIENT LIST # Updated,2024-10-25 07:51:54 # Common Name,Real Address,Bytes Received,Bytes Sent,Connected Since # user16,152.58.35.87:40816,28719,27679,2024-10-25 07:51:48 # ROUTING TABLE # Virtual Address,Common Name,Real Address,Last Ref # 192.168.255.6,user16,152.58.35.87:40816,2024-10-25 07:51:50 # GLOBAL STATS # Max bcast/mcast queue length,0 # END # """ # clients = [] # for line in status_data.splitlines(): # match = re.search(r"(\S+),(\d+\.\d+\.\d+\.\d+:\d+),", line) # if match: # username = match.group(1) # real_address = match.group(2) # e.g., 152.58.35.87:40816 # clients.append((username, real_address)) # for username, real_address in clients: # ip, port = real_address.split(":") # try: # result = subprocess.check_output(["netstat", "-tunap"], stderr=subprocess.STDOUT).decode() # print(result) # pid_match = re.search(rf"{ip}:{port}\s+\w+\s+\d+/\w+\s+\d+", result) # if pid_match: # pid_search = re.search(rf"{ip}:{port}\s+\w+\s+(\d+)/\w+", result) # if pid_search: # pid = pid_search.group(1) # print(f"Username: {username}, Remote IP: {ip}, Port: {port}, PID: {pid}") # else: # print(f"Username: {username}, Remote IP: {ip}, Port: {port}, PID not found") # else: # print(f"Username: {username}, Remote IP: {ip}, Port: {port}, PID not found") # except subprocess.CalledProcessError as e: # print(f"Error executing netstat command: {e}") #--------------------------------------------------------- # import http.server # import socketserver # from datetime import datetime # PORT = 8000 # class MyHandler(http.server.SimpleHTTPRequestHandler): # def do_GET(self): # # Define dynamic content for the HTML page # site_title = "My OpenVPN Status Monitor" # server_name = "OpenVPN Server 1" # current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S") # status_message = "All systems operational" # # HTML content with embedded Python variables # html_content = f""" # # # # # # # {site_title} # # # #

{site_title}

#

{server_name}

#

Current time: {current_time}

#

Status: {status_message}

# # # # """ # # Send response headers # self.send_response(200) # self.send_header("Content-type", "text/html") # self.end_headers() # # Send the HTML content # self.wfile.write(html_content.encode("utf-8")) # # Set up and start the server # with socketserver.TCPServer(("", PORT), MyHandler) as httpd: # print(f"Serving at http://localhost:{PORT}") # httpd.serve_forever() # import paramiko # from flask import Flask, render_template_string # import os # # Define server details (update with actual server details) # SERVERS = [ # # {"name": "VPN_blue_staging", "host": "52.38.82.122", "user": "vpn-sftp", "password": "upsquare@123"}, # {"name": "VPN_blue", "host": "52.36.182.131", "user": "vpn-sftp", "password": "upsquare@123"} # ] # # Define the log file path on each server # LOG_FILE_PATH = "/var/www/html/vpn-python-scripting/logs/NEW_VPN_2024-11-13.log" # # Flask app # app = Flask(__name__) # TEMPLATE = """ # # # # Cron Job Monitor # # # #
#

Cron Job Monitor

# {% for server in servers %} #
#

{{ server.name }}

#
# 
{{ server.log_content }}
#
#
# {% endfor %} #
# # # """ # def fetch_last_log_from_server(server): # """Fetch the last log entry from a remote server.""" # client = paramiko.SSHClient() # client.set_missing_host_key_policy(paramiko.AutoAddPolicy()) # try: # # Connect to the server # client.connect(server["host"], username=server["user"], password=server["password"]) # # Fetch the last line of the log file # sftp = client.open_sftp() # with sftp.open(LOG_FILE_PATH, 'r') as log_file: # # Move to the end of the file # log_file.seek(0, os.SEEK_END) # # Read backwards to find the last line # if log_file.tell() == 0: # If file is empty # log_content = "No logs found." # else: # # Read the last line # log_file.seek(max(log_file.tell() - 1024, 0), os.SEEK_SET) # Seek back 1 KB (adjust if necessary) # import asyncio # import re # import asyncio # servers = [ # ('13.200.64.163', 5555), # ('13.200.64.163', 5555), # ] # MAX_CONCURRENT_CONNECTIONS = 100 # semaphore = asyncio.Semaphore(MAX_CONCURRENT_CONNECTIONS) # async def parse_openvpn_response(server_ip, server_port, response): # """ # Parse the OpenVPN management interface response and return structured data. # """ # # Extract version and release information from the response # release_match = re.search(r'OpenVPN\s([^\s]+)\s(.*?)\n', response) # if release_match: # version = release_match.group(1) # release = release_match.group(2) # else: # version = None # release = None # # Example structure for sessions # sessions = {} # # Construct the result dictionary # result = { # 'host': server_ip, # 'name': 'UDP', # 'port': server_port, # 'socket_connected': True, # 'release': release, # 'version': version, # 'sessions': sessions, # } # return result # async def connect_to_openvpn(server_ip, server_port): # """ # Connect to the OpenVPN management interface, send a command, and parse the response. # """ # async with semaphore: # try: # reader, writer = await asyncio.open_connection(server_ip, server_port) # print(f"Connected to OpenVPN management interface at {server_ip}:{server_port}") # # Send the 'status' command to the OpenVPN server # command = "status\n" # writer.write(command.encode()) # await writer.drain() # # Read the response from the OpenVPN server # response = await reader.read(4096) # Adjust buffer size as needed # print('response----------=-=',response) # response_str = response.decode('utf-8') # print(f"Raw response from {server_ip}:{server_port}:\n{response_str}") # # Parse the response to extract structured data # parsed_data = await parse_openvpn_response(server_ip, server_port, response_str) # print(f"Parsed data from {server_ip}:{server_port}:\n{parsed_data}") # # Close the connection # writer.close() # await writer.wait_closed() # except Exception as e: # print(f"Failed to connect to {server_ip}:{server_port} - Error: {e}") # async def main(): # # Create a list of tasks to connect to all servers # tasks = [connect_to_openvpn(server_ip, server_port) for server_ip, server_port in servers] # # Run all the tasks asynchronously, with controlled concurrency # await asyncio.gather(*tasks) # if __name__ == '__main__': # asyncio.run(main()) #========================This is working get data from OVPN========================== # import re # import asyncio # servers = [ # ('13.200.64.163', 5555), # ('13.200.64.163', 5555), # ] # MAX_CONCURRENT_CONNECTIONS = 100 # semaphore = asyncio.Semaphore(MAX_CONCURRENT_CONNECTIONS) # async def parse_openvpn_response(server_ip, server_port, response): # """ # Parse the OpenVPN management interface response and return structured data. # """ # # Extract version and release information from the response # release_match = re.search(r'OpenVPN\s([^\s]+)\s(.*?)\n', response) # if release_match: # version = release_match.group(1) # release = release_match.group(2) # else: # version = None # release = None # # Example structure for sessions (you can enhance this based on your requirements) # sessions = {} # # Construct the result dictionary # result = { # 'host': server_ip, # 'name': 'UDP', # 'port': server_port, # 'socket_connected': True, # 'release': release, # 'version': version, # 'sessions': sessions, # } # return result # async def connect_to_openvpn(server_ip, server_port): # """ # Connect to the OpenVPN management interface, send a command, and parse the response. # """ # async with semaphore: # try: # reader, writer = await asyncio.open_connection(server_ip, server_port) # print(f"Connected to OpenVPN management interface at {server_ip}:{server_port}") # # Send the 'status' command to the OpenVPN server # command = "status\n" # writer.write(command.encode()) # await writer.drain() # # Read the response in a loop until 'END' is encountered # response = "" # while True: # chunk = await reader.read(4096) # if not chunk: # break # response += chunk.decode('utf-8') # if "END" in chunk.decode('utf-8'): # break # if not response: # print(f"No data received from {server_ip}:{server_port}") # return # print(f"Raw response from {server_ip}:{server_port}:\n{response}") # # Parse the response to extract structured data # parsed_data = await parse_openvpn_response(server_ip, server_port, response) # print(f"Parsed data from {server_ip}:{server_port}:\n{parsed_data}") # # Close the connection # writer.close() # await writer.wait_closed() # except Exception as e: # print(f"Failed to connect to {server_ip}:{server_port} - Error: {e}") # async def main(): # # Create a list of tasks to connect to all servers # tasks = [connect_to_openvpn(server_ip, server_port) for server_ip, server_port in servers] # # Run all the tasks asynchronously, with controlled concurrency # await asyncio.gather(*tasks) # if __name__ == '__main__': # asyncio.run(main()) #==================THIS IS WORKING CODE=============== # import asyncio # from datetime import datetime # from ipaddress import IPv4Address # try: # import geoip2.database # from geoip2.errors import AddressNotFoundError # geoip2_available = True # except ImportError: # geoip2_available = False # class OpenVPNClient: # def __init__(self, servers, max_concurrent_connections=100): # """ # Initialize the OpenVPN client with a list of servers, geoip_version, and max concurrent connections. # """ # self.servers = servers # self.gi = geoip2.database.Reader('/var/www/html/1375-staysolid-ovpn-backend/GeoLite2-City.mmdb') # self.geoip_version = 2 # self.max_concurrent_connections = max_concurrent_connections # self.semaphore = asyncio.Semaphore(self.max_concurrent_connections) # def get_geoip_data(self,session, geoip_version): # try: # if geoip_version == 1: # # Legacy GeoIP lookup # gir = self.gi.record_by_addr(str(session['remote_ip'])) # if gir is not None: # session['location'] = gir.get('country_code', '') # session['region'] = gir.get('region', '') # session['city'] = gir.get('city', '') # session['country'] = gir.get('country_name', '') # session['longitude'] = gir.get('longitude', '') # session['latitude'] = gir.get('latitude', '') # elif geoip_version == 2: # # GeoIP2 lookup # gir = self.gi.city(str(session['remote_ip'])) # session['location'] = gir.country.iso_code if gir.country else '' # session['region'] = gir.subdivisions.most_specific.iso_code if gir.subdivisions.most_specific else '' # session['city'] = gir.city.name if gir.city else '' # session['country'] = gir.country.name if gir.country else '' # session['longitude'] = gir.location.longitude if gir.location else '' # session['latitude'] = gir.location.latitude if gir.location else '' # except geoip2.errors.AddressNotFoundError: # pass # except SystemError: # pass # def parse_status(self, data, server_ip, server_port, gi): # """Parse the raw data and convert it to a structured dictionary.""" # if not data or data.strip() == '': # print("No valid data to parse.") # return {} # lines = data.splitlines() # result = { # 'host': server_ip, # 'name': 'UDP', # 'port': str(server_port), # 'socket_connected': True, # 'release': 'OpenVPN 2.6.11 x86_64-alpine-linux-musl [SSL (OpenSSL)] [LZO] [LZ4] [EPOLL] [MH/PKTINFO] [AEAD]', # 'version': '2.6.11', # 'sessions': {} # } # parsing_clients = False # parsing_routing_table = False # for line in lines: # # Start parsing the CLIENT_LIST section # if line.startswith('Common Name'): # parsing_clients = True # continue # elif line.startswith('ROUTING TABLE'): # parsing_clients = False # parsing_routing_table = True # continue # elif line.startswith('GLOBAL STATS'): # break # if parsing_clients and line: # columns = line.split(',') # username = columns[0].strip() # remote_ip = columns[1].split(':')[0].strip() # bytes_received = int(columns[2].strip()) # bytes_sent = int(columns[3].strip()) # connected_since = datetime.strptime(columns[4].strip(), '%Y-%m-%d %H:%M:%S') # total_data_usage = bytes_received + bytes_sent # session = { # 'username': username, # 'remote_ip': IPv4Address(remote_ip), # 'bytes_recv': bytes_received, # 'bytes_sent': bytes_sent, # 'connected_since': connected_since, # 'total_data_usage': total_data_usage, # 'last_seen': '', # 'location': '', # 'region': '', # 'city': '', # 'country': '', # 'longitude': '', # 'latitude': '', # 'local_ip': '' # } # self.get_geoip_data(session, self.geoip_version) # session_key = f"{username}_{remote_ip}" # result['sessions'][session_key] = session # elif parsing_routing_table and line: # if line.startswith('Virtual Address'): # continue # columns = line.split(',') # if len(columns) >= 4: # local_ip = columns[0].strip() # username = columns[1].strip() # remote_ip = columns[2].split(':')[0].strip() # last_seen = columns[3].strip() # session_key = f"{username}_{remote_ip}" # if session_key in result['sessions']: # result['sessions'][session_key]['local_ip'] = local_ip # result['sessions'][session_key]['last_seen'] = last_seen # return result # async def connect_to_openvpn(self, server_ip, server_port): # """ # Connect to the OpenVPN management interface, send a command, and parse the response. # """ # async with self.semaphore: # try: # reader, writer = await asyncio.open_connection(server_ip, server_port) # print(f"Connected to OpenVPN management interface at {server_ip}:{server_port}") # command = "status\n" # writer.write(command.encode()) # await writer.drain() # response = "" # while True: # chunk = await reader.read(4096) # if not chunk: # break # response += chunk.decode('utf-8') # if "END" in chunk.decode('utf-8'): # break # if not response: # print(f"No data received from {server_ip}:{server_port}") # return # parsed_data = self.parse_status(response, server_ip, server_port, gi=None) # print(f"Parsed data from {server_ip}:{server_port}:\n{parsed_data}") # writer.close() # await writer.wait_closed() # except Exception as e: # print(f"Failed to connect to {server_ip}:{server_port} - Error: {e}") # async def run(self): # """ # Run the tasks for all servers asynchronously with controlled concurrency. # """ # tasks = [self.connect_to_openvpn(server_ip, server_port) for server_ip, server_port in self.servers] # await asyncio.gather(*tasks) # # Entry point # if __name__ == '__main__': # servers = [ # ('13.200.64.163', 5555), # ('13.200.64.163', 5555), # ] # openvpn_client = OpenVPNClient(servers) # asyncio.run(openvpn_client.run()) # import asyncio # from datetime import datetime # from ipaddress import IPv4Address # try: # import geoip2.database # from geoip2.errors import AddressNotFoundError # geoip2_available = True # except ImportError: # geoip2_available = False # class OpenVPNClient: # def __init__(self, servers, max_concurrent_connections=100): # """ # Initialize the OpenVPN client with a list of servers, geoip_version, and max concurrent connections. # """ # self.servers = servers # self.gi = geoip2.database.Reader('/var/www/html/1375-staysolid-ovpn-backend/GeoLite2-City.mmdb') # self.geoip_version = 2 # self.max_concurrent_connections = max_concurrent_connections # self.semaphore = asyncio.Semaphore(self.max_concurrent_connections) # def get_geoip_data(self,session, geoip_version): # try: # if geoip_version == 1: # # Legacy GeoIP lookup # gir = self.gi.record_by_addr(str(session['remote_ip'])) # if gir is not None: # session['location'] = gir.get('country_code', '') # session['region'] = gir.get('region', '') # session['city'] = gir.get('city', '') # session['country'] = gir.get('country_name', '') # session['longitude'] = gir.get('longitude', '') # session['latitude'] = gir.get('latitude', '') # elif geoip_version == 2: # # GeoIP2 lookup # gir = self.gi.city(str(session['remote_ip'])) # session['location'] = gir.country.iso_code if gir.country else '' # session['region'] = gir.subdivisions.most_specific.iso_code if gir.subdivisions.most_specific else '' # session['city'] = gir.city.name if gir.city else '' # session['country'] = gir.country.name if gir.country else '' # session['longitude'] = gir.location.longitude if gir.location else '' # session['latitude'] = gir.location.latitude if gir.location else '' # except geoip2.errors.AddressNotFoundError: # pass # except SystemError: # pass # def parse_status(self, data, server_ip, server_port, gi): # """Parse the raw data and convert it to a structured dictionary.""" # if not data or data.strip() == '': # print("No valid data to parse.") # return {} # lines = data.splitlines() # result = { # 'host': server_ip, # 'name': 'UDP', # 'port': str(server_port), # 'socket_connected': True, # 'release': 'OpenVPN 2.6.11 x86_64-alpine-linux-musl [SSL (OpenSSL)] [LZO] [LZ4] [EPOLL] [MH/PKTINFO] [AEAD]', # 'version': '2.6.11', # 'sessions': {} # } # parsing_clients = False # parsing_routing_table = False # for line in lines: # # Start parsing the CLIENT_LIST section # if line.startswith('Common Name'): # parsing_clients = True # continue # elif line.startswith('ROUTING TABLE'): # parsing_clients = False # parsing_routing_table = True # continue # elif line.startswith('GLOBAL STATS'): # break # if parsing_clients and line: # columns = line.split(',') # username = columns[0].strip() # remote_ip = columns[1].split(':')[0].strip() # bytes_received = int(columns[2].strip()) # bytes_sent = int(columns[3].strip()) # connected_since = datetime.strptime(columns[4].strip(), '%Y-%m-%d %H:%M:%S') # total_data_usage = bytes_received + bytes_sent # session = { # 'username': username, # 'remote_ip': IPv4Address(remote_ip), # 'bytes_recv': bytes_received, # 'bytes_sent': bytes_sent, # 'connected_since': connected_since, # 'total_data_usage': total_data_usage, # 'last_seen': '', # 'location': '', # 'region': '', # 'city': '', # 'country': '', # 'longitude': '', # 'latitude': '', # 'local_ip': '' # } # self.get_geoip_data(session, self.geoip_version) # session_key = f"{username}_{remote_ip}" # result['sessions'][session_key] = session # elif parsing_routing_table and line: # if line.startswith('Virtual Address'): # continue # columns = line.split(',') # if len(columns) >= 4: # local_ip = columns[0].strip() # username = columns[1].strip() # remote_ip = columns[2].split(':')[0].strip() # last_seen = columns[3].strip() # session_key = f"{username}_{remote_ip}" # if session_key in result['sessions']: # result['sessions'][session_key]['local_ip'] = local_ip # result['sessions'][session_key]['last_seen'] = last_seen # return result # async def connect_to_openvpn(self, server_ip, server_port): # """ # Connect to the OpenVPN management interface, send a command, and parse the response. # """ # async with self.semaphore: # try: # reader, writer = await asyncio.open_connection(server_ip, server_port) # print(f"Connected to OpenVPN management interface at {server_ip}:{server_port}") # command = "status\n" # writer.write(command.encode()) # await writer.drain() # response = "" # while True: # chunk = await reader.read(4096) # if not chunk: # break # response += chunk.decode('utf-8') # if "END" in chunk.decode('utf-8'): # break # if not response: # print(f"No data received from {server_ip}:{server_port}") # return # parsed_data = self.parse_status(response, server_ip, server_port, gi=None) # print(f"Parsed data from {server_ip}:{server_port}:\n{parsed_data}") # writer.close() # await writer.wait_closed() # except Exception as e: # print(f"Failed to connect to {server_ip}:{server_port} - Error: {e}") # async def run(self): # """ # Run the tasks for all servers asynchronously with controlled concurrency. # """ # tasks = [self.connect_to_openvpn(server_ip, server_port) for server_ip, server_port in self.servers] # await asyncio.gather(*tasks) # # Entry point # if __name__ == '__main__': # servers = [ # ('13.200.64.163', 5555), # ('13.200.64.163', 5555), # ] # openvpn_client = OpenVPNClient(servers) # asyncio.run(openvpn_client.run()) #===================================================================== # import asyncio # from datetime import datetime # from ipaddress import IPv4Address # import mysql.connector # import json # import requests # import time # import calendar # import os # import logging # import traceback # try: # import geoip2.database # from geoip2.errors import AddressNotFoundError # geoip2_available = True # except ImportError: # geoip2_available = False # class OpenVPNClient: # def __init__(self, servers, db_conn,data_limit_mb,max_concurrent_connections=100): # """ # Initialize the OpenVPN client with a list of servers, geoip_version, and max concurrent connections. # """ # self.servers = servers # self.gi = geoip2.database.Reader('/var/www/html/1375-staysolid-ovpn-backend/GeoLite2-City.mmdb') # self.geoip_version = 2 # self.max_concurrent_connections = max_concurrent_connections # self.semaphore = asyncio.Semaphore(self.max_concurrent_connections) # self.session = {} # self.servers_data = [] # self.connected_servers = [] # self.db_conn = db_conn # self.data_limit_bytes = data_limit_mb * 1024 * 1024 # def disconnect_user(self, username, remote_ip, total_data_usage): # try: # disconnect_command = f"kill {username}\n" # self.s.sendall(disconnect_command.encode('utf-8')) # print(f"Sent disconnect command for user: {username}") # except Exception as e: # print(f"Error sending disconnect command: {e}") # def log_disconnect(self, username, remote_ip, total_data_usage, disconnect_time): # log_entry = f"{disconnect_time},{username},{remote_ip},{total_data_usage}\n" # with open(self.log_file, 'a') as log_file: # log_file.write(log_entry) # print(f"Logged disconnect: {log_entry.strip()}") # def n_sessions(self): # """Update session data into the database.""" # try: # with self.db_conn.cursor(dictionary=True) as db_cursor: # # sessions = vpn['sessions'] # connected_servers_str = "" # disconnected_users = [] # print(self.servers_data) # for session_data in self.servers_data: # username = session_data['username'] # remote_ip = str(session_data['remote_ip']) # incoming_bytes_recv = session_data['bytes_recv'] # incoming_bytes_sent = session_data['bytes_sent'] # connected_since = str(session_data['connected_since']) # total_data_usage = session_data['total_data_usage'] # last_seen = str(session_data['last_seen']) # location = session_data['location'] # region = session_data['region'] # latitude = session_data['latitude'] # city = session_data['city'] # country = session_data['country'] # longitude = session_data['longitude'] # local_ips = session_data['local_ip'] # server_ip = session_data['host_name'] # incoming_total_bytes = incoming_bytes_recv + incoming_bytes_sent # db_cursor.execute("SELECT current_bytes_recv, current_bytes_sent, current_total_bytes FROM ovpn_server_logs WHERE username = %s", (username,)) # result = db_cursor.fetchone() # # print('result',result) # if result: # self.connected_servers.append(username) # connected_servers_str = "'" + "', '".join(self.connected_servers) + "'" # current_bytes_recv, current_bytes_sent, current_total_bytes = result['current_bytes_recv'],result['current_bytes_sent'],result['current_bytes_sent'] # new_total_bytes = incoming_bytes_recv + incoming_bytes_sent # if (current_total_bytes+new_total_bytes) > self.data_limit_bytes: # print(f"User {username} has exceeded the data limit ({current_total_bytes+new_total_bytes} bytes). Disconnecting...") # disconnect_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S') # # self.log_disconnect(username, remote_ip, current_total_bytes+new_total_bytes, disconnect_time) # # self.disconnect_user(username, remote_ip, current_total_bytes+new_total_bytes) # dt = time.gmtime() # ts = calendar.timegm(dt) # update_query = f"UPDATE ovpn_server_logs SET is_latest = 0 ,updated_at = {ts} WHERE username = '{username}'" # db_cursor.execute(update_query) # self.db_conn.commit() # disconneted_user = { # 'username': username, # 'bytes_recv': incoming_bytes_recv, # 'bytes_sent': incoming_bytes_recv, # 'total_bytes': incoming_total_bytes, # 'remote_ip': remote_ip, # 'country': country, # 'region': region, # 'city': city, # 'local_ip': local_ips, # 'latitude': latitude, # 'longitude': longitude, # 'connected_since': connected_since, # 'last_seen': last_seen, # 'server_ip': server_ip # } # disconnected_users.append(disconneted_user) # else: # print('-----1------') # dt = time.gmtime() # ts = calendar.timegm(dt) # if current_total_bytes < new_total_bytes: # update_query = """ UPDATE ovpn_server_logs SET current_bytes_recv = %s, current_bytes_sent = %s, current_total_bytes = %s, is_latest = %s, connected_since = %s, last_seen = %s, region = %s, latitude = %s, city = %s, country = %s, longitude = %s, remote_ip = %s, local_ip = %s, server_ip = %s, updated_at = %s WHERE username = %s""" # db_cursor.execute(update_query, (incoming_bytes_recv, incoming_bytes_sent, new_total_bytes, 1, connected_since, last_seen, region, latitude, city, country, longitude, remote_ip, local_ips, server_ip,ts, username)) # self.db_conn.commit() # else: # print('-----2------') # update_query = """ UPDATE ovpn_server_logs SET current_bytes_recv = %s, current_bytes_sent = %s, current_total_bytes = %s, is_latest = %s, connected_since = %s, last_seen = %s, region = %s, latitude = %s, city = %s, country = %s, longitude = %s, remote_ip = %s, local_ip = %s, server_ip = %s,updated_at = %s WHERE username = %s""" # db_cursor.execute(update_query, (incoming_bytes_recv, incoming_bytes_sent, new_total_bytes, 1, connected_since, last_seen, region, latitude, city, country, longitude, remote_ip, local_ips, server_ip,ts, username)) # self.db_conn.commit() # else: # try: # self.connected_servers.append(username) # connected_servers_str = "'" + "', '".join(self.connected_servers) + "'" # dt = time.gmtime() # ts = calendar.timegm(dt) # insert_query = """ # INSERT INTO ovpn_server_logs (username, current_bytes_recv, current_bytes_sent, current_total_bytes,is_latest ,connected_since , last_seen, region, latitude, city, country, longitude, remote_ip, local_ip, server_ip,created_at,updated_at) # VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, # %s, %s, %s) # """ # db_cursor.execute(insert_query, (username, incoming_bytes_recv, incoming_bytes_sent, incoming_bytes_recv + incoming_bytes_sent, 1, connected_since, last_seen, region, latitude, city, country, longitude, remote_ip, local_ips, server_ip,ts,ts)) # self.db_conn.commit() # print(f"Inserted new data usage record for {username}: recv = {incoming_bytes_recv}, sent = {incoming_bytes_sent}") # except Exception as e: # print(e) # if not connected_servers_str: # connected_servers_str = "''" # db_cursor.execute(f"""SELECT username, current_bytes_recv, current_bytes_sent, current_total_bytes, total_bytes_recv, total_bytes_sent, total_bytes FROM ovpn_server_logs WHERE username NOT IN ({connected_servers_str}) AND is_latest = 1""") # connected_servers_data = db_cursor.fetchall() # # print('user_data_dict',connected_servers_data) # if connected_servers_data: # update_data = [ # ( # row['total_bytes_recv'] + row['current_bytes_recv'], # row['total_bytes_sent'] + row['current_bytes_sent'], # row['total_bytes'] + row['current_total_bytes'], # row['username'] # ) # for row in connected_servers_data # ] # update_query = """UPDATE ovpn_server_logs SET total_bytes_recv = %s, total_bytes_sent = %s, total_bytes = %s, current_bytes_recv = 0, current_bytes_sent = 0, current_total_bytes = 0, is_latest = 0 WHERE username = %s""" # db_cursor.executemany(update_query, update_data) # self.db_conn.commit() # except mysql.connector.Error as err: # print(f"Error: {err}") # self.db_conn.rollback() # def get_geoip_data(self,session, geoip_version): # try: # if geoip_version == 1: # # Legacy GeoIP lookup # gir = self.gi.record_by_addr(str(session['remote_ip'])) # if gir is not None: # session['location'] = gir.get('country_code', '') # session['region'] = gir.get('region', '') # session['city'] = gir.get('city', '') # session['country'] = gir.get('country_name', '') # session['longitude'] = gir.get('longitude', '') # session['latitude'] = gir.get('latitude', '') # elif geoip_version == 2: # # GeoIP2 lookup # gir = self.gi.city(str(session['remote_ip'])) # session['location'] = gir.country.iso_code if gir.country else '' # session['region'] = gir.subdivisions.most_specific.iso_code if gir.subdivisions.most_specific else '' # session['city'] = gir.city.name if gir.city else '' # session['country'] = gir.country.name if gir.country else '' # session['longitude'] = gir.location.longitude if gir.location else '' # session['latitude'] = gir.location.latitude if gir.location else '' # except geoip2.errors.AddressNotFoundError: # pass # except SystemError: # pass # def parse_status(self, data, server_ip, server_port, gi): # """Parse the raw data and convert it to a structured dictionary.""" # if not data or data.strip() == '': # print("No valid data to parse.") # return {} # lines = data.splitlines() # parsing_clients = False # parsing_routing_table = False # for line in lines: # # Start parsing the CLIENT_LIST section # if line.startswith('Common Name'): # parsing_clients = True # continue # elif line.startswith('ROUTING TABLE'): # parsing_clients = False # parsing_routing_table = True # continue # elif line.startswith('GLOBAL STATS'): # break # if parsing_clients and line: # columns = line.split(',') # username = columns[0].strip() # remote_ip = columns[1].split(':')[0].strip() # bytes_received = int(columns[2].strip()) # bytes_sent = int(columns[3].strip()) # connected_since = datetime.strptime(columns[4].strip(), '%Y-%m-%d %H:%M:%S') # total_data_usage = bytes_received + bytes_sent # self.session = { # 'host_name' : server_ip, # 'session_key': f"{username}_{remote_ip}", # 'username': username, # 'remote_ip': IPv4Address(remote_ip), # 'bytes_recv': bytes_received, # 'bytes_sent': bytes_sent, # 'connected_since': connected_since, # 'total_data_usage': total_data_usage, # 'last_seen': '', # 'location': '', # 'region': '', # 'city': '', # 'country': '', # 'longitude': '', # 'latitude': '', # 'local_ip': '' # } # self.get_geoip_data(self.session, self.geoip_version) # self.servers_data.append(self.session) # session_key = f"{username}_{remote_ip}" # elif parsing_routing_table and line: # if line.startswith('Virtual Address'): # continue # columns = line.split(',') # if len(columns) >= 4: # local_ip = columns[0].strip() # username = columns[1].strip() # remote_ip = columns[2].split(':')[0].strip() # last_seen = columns[3].strip() # session_key = f"{username}_{remote_ip}" # if session_key in self.session['session_key']: # self.session['local_ip'] = local_ip # self.session['last_seen'] = last_seen # async def connect_to_openvpn(self, server_ip, server_port): # """ # Connect to the OpenVPN management interface, send a command, and parse the response. # """ # async with self.semaphore: # try: # reader, writer = await asyncio.open_connection(server_ip, server_port) # print(f"Connected to OpenVPN management interface at {server_ip}:{server_port}") # command = "status\n" # writer.write(command.encode()) # await writer.drain() # response = "" # while True: # chunk = await reader.read(4096) # if not chunk: # break # response += chunk.decode('utf-8') # if "END" in chunk.decode('utf-8'): # break # if not response: # print(f"No data received from {server_ip}:{server_port}") # return # self.parse_status(response, server_ip, server_port, gi=None) # # print(f"Parsed data from {server_ip}:{server_port}:\n{parsed_data}") # writer.close() # await writer.wait_closed() # self.n_sessions() # except Exception as e: # excp = traceback.format_exc() # print("exp------", excp) # print(f"Failed to connect to {server_ip}:{server_port} - Error: {e}") # async def run(self): # """ # Run the tasks for all servers asynchronously with controlled concurrency. # """ # tasks = [self.connect_to_openvpn(server_ip, server_port) for server_ip, server_port in self.servers] # await asyncio.gather(*tasks) # # Entry point # if __name__ == '__main__': # db_conn = mysql.connector.connect( # host="13.200.64.163", # user="sD160VACL7", # password="j8J2W5LZLGpX", # database="vpnsolid", # # port=5306 # ) # servers = [ # ('172.245.234.181', 5555), # # ('13.200.64.163', 5555), # ] # openvpn_client = OpenVPNClient(servers,max_concurrent_connections=100, data_limit_mb=100000, db_conn=db_conn) # asyncio.run(openvpn_client.run()) # import asyncio # async def fetch_openvpn_status(): # # Connect to the OpenVPN management interface # reader, writer = await asyncio.open_connection('172.245.234.181', 5555) # # Wait for the initial message # initial_message = await reader.readuntil(b"\r\n") # print(initial_message.decode('utf-8')) # # Send 'status' command # writer.write(b'status\n') # await writer.drain() # # Read and parse the response # while True: # line = await reader.readline() # if b"END" in line: # Stop reading at the end of the status block # break # print(line.decode('utf-8').strip()) # # Close the connection # writer.close() # await writer.wait_closed() # asyncio.run(fetch_openvpn_status()) # import socket # def connect_to_ovpn_management(host: str, port: int, command: str) -> str: # """ # Connect to the OpenVPN management interface and send a command. # :param host: IP address of the OpenVPN management interface. # :param port: Port of the OpenVPN management interface. # :param command: Command to send to the management interface. # :return: Response from the OpenVPN server. # """ # try: # # Create a socket connection # with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as client_socket: # client_socket.connect((host, port)) # print(f"Connected to OpenVPN management interface at {host}:{port}") # # Receive initial banner # banner = client_socket.recv(4096).decode('utf-8') # print("Banner received:") # print(banner) # # Send the command # client_socket.sendall(command.encode('utf-8') + b'\n') # print(f"Command sent: {command}") # # Receive response # response = client_socket.recv(4096).decode('utf-8') # print("Response received:") # return response # except Exception as e: # return f"An error occurred: {e}" # if __name__ == "__main__": # # OpenVPN management interface details # HOST = "172.245.234.181" # Change to the actual host if not localhost # PORT = 5555 # Change to the port specified in the OpenVPN config # # Command to send, e.g., 'status', 'load-stats', 'exit' # COMMAND = "status" # # Connect and fetch data # response = connect_to_ovpn_management(HOST, PORT, COMMAND) # print(response) #================================ADD DATA INTO JSON==================================== # import mysql.connector # import json # import time # # Database connection configuration # db_config = { # "host":"ovpn-centrealised-server.cl66yeq0k0b7.us-east-2.rds.amazonaws.com", # "user": "xeg3ua7ooqu1", # "password": "ahv3eigee2zo", # "database": "xeg3ua7ooqu12" # } # # Connect to the database # try: # connection = mysql.connector.connect(**db_config) # cursor = connection.cursor(dictionary=True) # # Query to fetch data # query = """ SELECT username, server_ip, total_bytes_recv, user_device_id FROM ovpn_server_logs """ # cursor.execute(query) # rows = cursor.fetchall() # # Convert rows to the desired JSON format # data = { # row['username']: { # 'server_ip': row['server_ip'], # 'total_bytes_recv': row['total_bytes_recv'], # 'restart_time': int(time.time()), # 'user_device_id': row['user_device_id'] # } # for row in rows # } # # Convert to JSON string # json_output = json.dumps(data, indent=2) # print(json_output) # except mysql.connector.Error as err: # print(f"Error: {err}") # finally: # if 'cursor' in locals() and cursor: # cursor.close() # if 'connection' in locals() and connection.is_connected(): # connection.close() #======================================================================= import random import time import json import redis # Connect to Redis redis_client = redis.StrictRedis(host='localhost', port=6379, db=0) # Static data to store in Redis data = { "us-chicago-node1-user33": { "server_ip": "172.245.234.181", "total_bytes_recv": 69258.0, "restart_time": 1734667836, "user_device_id": "0" }, "us-chicago-node1-user17": { "server_ip": "172.245.234.181", "total_bytes_recv": 2294721.0, "restart_time": 1734667836, "user_device_id": "0" }, "us-chicago-node1-user16": { "server_ip": "172.245.234.181", "total_bytes_recv": 1224515.0, "restart_time": 1734667836, "user_device_id": "0" }, "us-chicago-node1-user23": { "server_ip": "172.245.234.181", "total_bytes_recv": 436568.0, "restart_time": 1734667836, "user_device_id": "0" }, "us-chicago-node1-user11": { "server_ip": "172.245.234.181", "total_bytes_recv": 1697647.0, "restart_time": 1734667836, "user_device_id": "0" }, "us-chicago-node1-user3": { "server_ip": "172.245.234.181", "total_bytes_recv": 14578505.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user5": { "server_ip": "172.245.234.181", "total_bytes_recv": 1170062.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user9950": { "server_ip": "172.245.234.181", "total_bytes_recv": 31870.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user6": { "server_ip": "172.245.234.181", "total_bytes_recv": 3728032.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user9": { "server_ip": "172.245.234.181", "total_bytes_recv": 10962437.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user45": { "server_ip": "172.245.234.181", "total_bytes_recv": 196501.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user44": { "server_ip": "172.245.234.181", "total_bytes_recv": 194555.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user42": { "server_ip": "172.245.234.181", "total_bytes_recv": 2106698.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user43": { "server_ip": "172.245.234.181", "total_bytes_recv": 33252.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user30": { "server_ip": "172.245.234.181", "total_bytes_recv": 135954.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user31": { "server_ip": "172.245.234.181", "total_bytes_recv": 54116.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user29": { "server_ip": "172.245.234.181", "total_bytes_recv": 461137.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user15": { "server_ip": "172.245.234.181", "total_bytes_recv": 183466.0, "restart_time": 1734667836, "user_device_id": None }, "us-chicago-node1-user48": { "server_ip": "172.245.234.181", "total_bytes_recv": 228329.0, "restart_time": 1734667836, "user_device_id": None } } # redis_client.set("all_data", json.dumps(data)) # print("Data has been successfully stored in Redis.") # ======================================================================================= import redis # Connect to Redis redis_client = redis.StrictRedis(host='localhost', port=6379, db=0) # Clear the current database redis_client.flushdb() print("Redis database cleared.") # Alternatively, clear all databases redis_client.flushall() print("All Redis databases cleared.") # #====================STORE 1000 data=================== # import redis # import random # import time # # Connect to Redis # redis_client = redis.StrictRedis(host='localhost', port=6379, db=0, decode_responses=True) # # Function to generate a sample user data # def generate_user_data(user_id): # return { # "server_ip": "172.245.234.181", # "total_bytes_recv": random.uniform(1000, 10000000), # Random number between 1000 and 10 million # "restart_time": int(time.time()), # Current timestamp # "user_device_id": str(random.choice([None, 0, random.randint(1, 100)])) # Randomly None, 0 or a random number # } # # Store 1000 records in Redis # for i in range(1, 1001): # user_key = f"us-chicago-node1-user{i}" # user_data = generate_user_data(i) # redis_client.hmset(user_key, user_data) # Store data as hash # print("1000 test data stored successfully in Redis.") #================================================================================= import redis import json # Connect to Redis redis_client = redis.StrictRedis(host='localhost', port=6379, decode_responses=True) # JSON data data = { "new-test-15": { "user_device_id": "E235F452-AB06-48FB-A82F-05DE951BF895", "server_ip": "172.245.234.181", "total_bytes_recv": 1291646, "total_bytes_sent": 110526551, "total_bytes": 111818197, "current_bytes_recv": 23617, "current_bytes_sent": 41370, "current_total_bytes":64987, "is_deleted":0 } } # Store each key-value pair in Redis # for user_key, user_data in data.items(): # redis_client.hmset(user_key, user_data) print("Data successfully added to Redis.") #================================================================================= import redis import time import json # Connect to Redis redis_client = redis.StrictRedis(host='localhost', port=6379, db=0, decode_responses=True) # Function to scan all keys using SCAN def scan_all_keys(redis_client, pattern='*', count=1000): cursor = 0 keys = [] while True: cursor, new_keys = redis_client.scan(cursor=cursor, match=pattern, count=count) keys.extend(new_keys) if cursor == 0: break return keys # Function to retrieve data for each key based on its type def get_all_data(redis_client, keys): all_data = {} for key in keys: key_type = redis_client.type(key) if key_type == 'hash': all_data[key] = redis_client.hgetall(key) elif key_type == 'string': all_data[key] = redis_client.get(key) elif key_type == 'list': all_data[key] = redis_client.lrange(key, 0, -1) elif key_type == 'set': all_data[key] = list(redis_client.smembers(key)) # Convert set to list for JSON serialization elif key_type == 'zset': all_data[key] = redis_client.zrange(key, 0, -1, withscores=True) return all_data # Measure time for scanning and retrieving data start_time = time.time() all_keys = scan_all_keys(redis_client) all_data_retrieved = get_all_data(redis_client, all_keys) end_time = time.time() # Print the retrieved data in a readable JSON format print(json.dumps(all_data_retrieved, indent=2)) print(f"Time taken to scan {len(all_keys)} keys and retrieve data: {end_time - start_time:.4f} seconds") # redis_client.delete('new-test-15') #THIS IS CODE FOR OVPN MONITOR # if connected_servers_data: # update_data = [] # disconnected_data_analytics = [] # delete_from_still_connected = [] # for row in connected_servers_data: # username = row['username'] # print('Filename : ',username) # utc_tz = pytz.utc # ist_tz = pytz.timezone("Asia/Kolkata") # # Get current time in IST # now_ist = datetime.now(ist_tz) # connected_since_utc = datetime.strptime(row['connected_since'], "%Y-%m-%d %H:%M:%S") # connected_since_utc = utc_tz.localize(connected_since_utc) # connected_since_ist = connected_since_utc.astimezone(ist_tz) # utc_time = datetime.strptime(row['connected_since'], '%Y-%m-%d %H:%M:%S') # # Convert UTC datetime to IST # ist_time = utc_time.replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Kolkata')) # # Get the Unix timestamp in IST # ist_timestamp = int(ist_time.timestamp()) # if connected_since_ist < now_ist: # print('into past date') # db_cursor.execute("""SELECT current_bytes_recv, current_bytes_sent, current_total_bytes, filename FROM still_connected_files_usage WHERE filename = %s""", (username,)) # result = db_cursor.fetchone() # print('result',result) # if result: # still_current_bytes_recv = row['current_bytes_recv'] - result['current_bytes_recv'] # still_current_bytes_sent = row['current_bytes_sent'] - result['current_bytes_sent'] # still_current_total_bytes = row['current_total_bytes'] - result['current_total_bytes'] # else: # still_current_bytes_recv = row['current_bytes_recv'] # still_current_bytes_sent = row['current_bytes_sent'] # still_current_total_bytes = row['current_total_bytes'] # disconnected_data_analytics.extend([row['username'],row['user_device_id'],row['remote_ip'],ist_timestamp,row['created_at'],still_current_bytes_recv,still_current_bytes_sent,still_current_total_bytes]) # delete_from_still_connected.append(username) # else: # disconnected_data_analytics.extend([row['username'],row['user_device_id'],row['remote_ip'],ist_timestamp,row['created_at'],row['current_bytes_recv'],row['current_bytes_sent'],row['current_total_bytes']]) # redis_exists = self.redis_helper.redis_client.exists(username) # print(redis_exists) # if redis_exists: # print('into redis condition') # redis_data = self.all_data_retrieved.get(username, {}) # current_bytes_recv = row['current_bytes_recv'] + int(redis_data.get('current_bytes_recv', 0)) # current_bytes_sent = row['current_bytes_sent'] + int(redis_data.get('current_bytes_sent', 0)) # current_total_bytes = row['current_total_bytes'] + int(redis_data.get('current_total_bytes', 0)) # update_query = """UPDATE ovpn_server_logs SET current_bytes_recv = %s, current_bytes_sent = %s, current_total_bytes = %s WHERE username = %s""" # db_cursor.execute(update_query, (current_bytes_recv, current_bytes_sent, current_total_bytes, username)) # self.db_conn.commit() # total_bytes_recv = row['total_bytes_recv'] + row['current_bytes_recv'] + int(redis_data.get('current_bytes_recv', 0)) # total_bytes_sent = row['total_bytes_sent'] + row['current_bytes_sent'] + int(redis_data.get('current_bytes_sent', 0)) # total_bytes = row['total_bytes'] + row['current_total_bytes'] + int(redis_data.get('current_total_bytes', 0)) # update_data.append((total_bytes_recv, total_bytes_sent, total_bytes, username)) # self.redis_helper.redis_client.delete(username) # else: # print('normal condition') # total_bytes_recv = row['total_bytes_recv'] + row['current_bytes_recv'] # total_bytes_sent = row['total_bytes_sent'] + row['current_bytes_sent'] # total_bytes = row['total_bytes'] + row['current_total_bytes'] # # Add the updated data to the list # update_data.append((total_bytes_recv, total_bytes_sent, total_bytes, username)) # if update_data: # print('update_data') # update_query = """UPDATE ovpn_server_logs SET total_bytes_recv = %s, total_bytes_sent = %s, total_bytes = %s, current_bytes_recv = 0, current_bytes_sent = 0, current_total_bytes = 0, is_latest = 0 WHERE username = %s""" # db_cursor.executemany(update_query, update_data) # self.db_conn.commit() # if disconnected_data_analytics: # print('disconnected_data_analytics',disconnected_data_analytics) # insert_data_query = """ # INSERT INTO disconnected_files_usage # (filename, user_device_id, remote_ip, connected_since, file_creation_date, current_bytes_recv, current_bytes_sent, current_total_bytes, created_at) # VALUES (%s, %s, %s, %s, %s, %s, %s, %s ,%s)""" # # disconnected_data_analytics = [tuple(disconnected_data_analytics) + (int(datetime.now().timestamp()),)] # disconnected_data_analytics = [ # tuple(disconnected_data_analytics[i:i+8]) + (int(datetime.now().timestamp()),) # for i in range(0, len(disconnected_data_analytics), 8) # ] # print('disconnected_data_analytics', disconnected_data_analytics) # # Execute the query # db_cursor.executemany(insert_data_query, disconnected_data_analytics) # self.db_conn.commit() # if delete_from_still_connected: # print('delete_from_still_connected') # query = "DELETE FROM still_connected_files_usage WHERE filename IN ({})".format(','.join(['%s'] * len(delete_from_still_connected))) # db_cursor.execute(query, delete_from_still_connected) # self.db_conn.commit()