148 lines
6.6 KiB
Python
148 lines
6.6 KiB
Python
import subprocess
|
|
import csv
|
|
from datetime import datetime
|
|
import requests
|
|
from Controller import create_service
|
|
import socket
|
|
import random
|
|
import time
|
|
|
|
|
|
####
|
|
####
|
|
#### --- RUN AS SINGEL SERVICE --- ####
|
|
####
|
|
####
|
|
|
|
def request_post(function,variable):
|
|
url = (f"http://api.stines.de:8001/{function}")
|
|
response = requests.post(url,json = variable,headers={'access_token':'^YWUbG7yX*V!tV^KBSd*2c&vdN3wV9a2i7f3hfGFMBYFxi6#mMiJGiaA5KEHE%B*miK%qb7rQ67gmcYP@gqmux8'})
|
|
return response
|
|
|
|
def request_get(function,variable):
|
|
url = (f"http://api.stines.de:8001/{function}")
|
|
response = requests.get(url,headers={'access_token':'^YWUbG7yX*V!tV^KBSd*2c&vdN3wV9a2i7f3hfGFMBYFxi6#mMiJGiaA5KEHE%B*miK%qb7rQ67gmcYP@gqmux8'})
|
|
return response
|
|
|
|
create_service.addservice("REPORTS-AD")
|
|
|
|
def adcontroller (ip,name):
|
|
command = ["powershell", "-Command",
|
|
" Get-ADUser -Filter * -Properties * | Select-Object *, extensionAttribute1, extensionAttribute2 | Export-Csv -NoTypeInformation -Encoding UTF8 -Path 'users.csv'"]
|
|
subprocess.run(command)
|
|
# CSV-Datei einlesen und Feldnamen auslesen
|
|
with open("users.csv", "r", encoding='utf-8-sig') as file:
|
|
reader = csv.DictReader(file)
|
|
fieldnames = reader.fieldnames
|
|
#
|
|
# unwanted_columns = ["PropertyNames", "AddedProperties","RemovedProperties","ModifiedProperties","PropertyCount"]
|
|
# fieldnames = [col for col in fieldnames if col not in unwanted_columns]
|
|
# Tabelle erstellen, falls sie noch nicht existiert
|
|
table_name = "Active-Directory-User"
|
|
create_table_query = f"CREATE TABLE IF NOT EXISTS `{table_name}` (id INT AUTO_INCREMENT PRIMARY KEY, importdate BIGINT(11), "
|
|
for field in fieldnames:
|
|
create_table_query += f"`{field}` TEXT, "
|
|
create_table_query = create_table_query.rstrip(", ") + ")"
|
|
request_post("dbset", {"query": create_table_query, 'dbname': name})
|
|
|
|
# Daten aus der CSV-Datei in die Tabelle einfügen
|
|
with open("users.csv", "r", encoding='utf-8-sig') as file:
|
|
reader = csv.reader(file)
|
|
next(reader) # Überspringe die erste Zeile (Feldnamen)
|
|
for row in reader:
|
|
row = [cell if cell.strip() else "-" for cell in row]
|
|
unix_time = int(datetime.now().timestamp())
|
|
row = [unix_time] + row
|
|
insert_query = f"INSERT INTO `{table_name}` (importdate, `{'`, `'.join(fieldnames)}`) VALUES (%s, {', '.join(['%s'] * len(fieldnames))})"
|
|
# print(insert_query)
|
|
request_post("ad/adduser", {"query": insert_query, 'dbname': name, 'entry': row})
|
|
# mysql_connect.add_user(insert_query, name, row)
|
|
|
|
command = ["powershell", "-Command",
|
|
"Get-ADGroupMember -Identity G-RDP-User | Export-Csv -NoTypeInformation -Encoding UTF8 -Path 'group.csv'"]
|
|
subprocess.run(command)
|
|
|
|
# CSV-Datei einlesen und Feldnamen auslesen
|
|
with open("group.csv", "r", encoding='utf-8-sig') as file:
|
|
reader = csv.DictReader(file)
|
|
fieldnames = reader.fieldnames
|
|
table_name = "Active-Directory-RDS-User"
|
|
create_table_query = f"CREATE TABLE IF NOT EXISTS `{table_name}` (id INT AUTO_INCREMENT PRIMARY KEY, importdate BIGINT(11), "
|
|
for field in fieldnames:
|
|
create_table_query += f"`{field}` TEXT, "
|
|
create_table_query = create_table_query.rstrip(", ") + ")"
|
|
request_post("dbset", {"query": create_table_query, 'dbname': name})
|
|
|
|
# Daten aus der CSV-Datei in die Tabelle aktualisieren oder einfügen
|
|
with open("group.csv", "r", encoding='utf-8-sig') as file:
|
|
reader = csv.DictReader(file)
|
|
for row in reader:
|
|
sam_account_name = row["SamAccountName"]
|
|
|
|
# Abfrage, um die ID für den Benutzernamen zu erhalten
|
|
query_id = f"SELECT id FROM `{table_name}` WHERE SamAccountName = '{sam_account_name}'"
|
|
|
|
result = request_post("ad/getuserid", {"query": query_id, 'dbname': name}).text
|
|
|
|
if result != "false":
|
|
user_id = result
|
|
print(user_id)
|
|
update_query = f"UPDATE `{table_name}` SET "
|
|
for field in fieldnames:
|
|
if field != "SamAccountName": # Spaltenname nicht in das UPDATE einbeziehen
|
|
update_query += f"`{field}` = %s, "
|
|
update_query = update_query.rstrip(", ")
|
|
update_query += ", `importdate` = %s" # Importdate als Unix-Timestamp aktualisieren
|
|
update_query += " WHERE id = %s"
|
|
row_data = [row[field] for field in fieldnames if field != "SamAccountName"]
|
|
unix_time = int(datetime.now().timestamp())
|
|
row_data = row_data + [unix_time]
|
|
row_data.append(user_id)
|
|
print(request_post("ad/adduser",{"query": update_query, "dbname":name, "entry":row_data}).text)
|
|
else:
|
|
print("false in side else")
|
|
insert_query = f"INSERT INTO `{table_name}` ("
|
|
insert_query += ", ".join(fieldnames) # Spaltennamen in die INSERT-Abfrage einbeziehen
|
|
insert_query += ", `importdate`) VALUES ("
|
|
insert_query += ", ".join(["%s"] * (len(fieldnames) + 1)) # Zusätzliches %s für importdate
|
|
insert_query += ")"
|
|
row_data = [row[field] for field in fieldnames]
|
|
unix_time = int(datetime.now().timestamp())
|
|
row_data = row_data + [unix_time]
|
|
print(row_data)
|
|
request_post("ad/adduser",{"query": insert_query, "dbname":name, "entry":row_data})
|
|
|
|
def get_local_ip():
|
|
try:
|
|
s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
|
|
s.connect(("8.8.8.8", 80))
|
|
local_ip = s.getsockname()[0]
|
|
s.close()
|
|
return local_ip
|
|
except Exception as e:
|
|
print(f"Fehler beim Abrufen der lokalen IP-Adresse: {e}")
|
|
return None
|
|
|
|
service_time = f"23:{str(random.randint(1, 59)).zfill(2)}" ## ADD Random Time
|
|
|
|
while True:
|
|
current_time = datetime.now().strftime("%H:%M")
|
|
try:
|
|
with open ("log.txt","a") as datei:
|
|
datei.writelines(request_get("gettime",{}).text + "\n")
|
|
except Exception as e:
|
|
with open ("log.txt","a") as datei:
|
|
datei.writelines(f"{current_time}No Connection to Server!" + "\n")
|
|
if current_time == service_time:
|
|
ip = get_local_ip()
|
|
database = request_post("info/getdatabase",{"ip":ip}).text.replace('"',"")
|
|
adcontroller(ip,database)
|
|
with open ("log.txt","a") as datei:
|
|
datei.writelines(f"{current_time} - Sleep for {22*3600}" + "\n")
|
|
time.sleep(22*3600)
|
|
time.sleep(60)
|
|
|
|
ip = get_local_ip()
|
|
database = request_post("info/getdatabase",{"ip":ip}).text.replace('"',"")
|
|
adcontroller(ip,database)
|