Query1.py

import os
import pandas as pd
import json
import smtplib, ssl, email
import pyodbc
from datetime import timedelta, datetime, timezone
from azure.monitor.query import LogsQueryClient, LogsBatchQuery, LogsQueryStatus
from azure.identity import DefaultAzureCredential, ClientSecretCredential
from email import encoders
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart


date = datetime.now().strftime("%Y_%m_%d")
server = 'tcp:' # Paste your DB IP address here
database = 'MicrosoftAAD' # Paste your DB Name that you created in DB here
username = '' # Paste the account that you will use to login and write date into here
password = '' # Paste the password that you will use to login and write date into here

#DB driver Definition from Python library
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

def __Convert__(string):
    li = list(string.split(" "))
    return li




TENANT_ID = ''
CLIENT_ID = ''
CLIENT_SECRET = '' # Client Secret
WORKSPACE_ID = '' # from the log analytics workspace



sender_email = "IT.Notifier@123.com"
receiver_email = "IT.Watchdog@123.com"
SMTP = "localhost"


credential  = ClientSecretCredential(
        client_id = CLIENT_ID,
        client_secret = CLIENT_SECRET,
        tenant_id = TENANT_ID
    )

client = LogsQueryClient(credential)

query = (
    """AuditLogs
    | where OperationName == "User registered security info"
    """
 )

request = [
    LogsBatchQuery(
        query = query,
        timespan = timedelta(days=1),
        workspace_id = WORKSPACE_ID
    ),
]

results = client.query_batch(request)

for res in results:
    if res.status == LogsQueryStatus.FAILURE:
        # This will be a LogsQueryError
        print(res)
    elif res.status == LogsQueryStatus.PARTIAL:
        # This will be a LogsQueryPartialResult
        print(res.partial_error)
        for table in res.partial_data:
            df = pd.DataFrame(table.rows, columns=table.columns)
    elif res.status == LogsQueryStatus.SUCCESS:
        # This will be a LogsQueryResult
        table = res.tables[0]
        df = pd.DataFrame(table.rows, columns=table.columns)

output = df.to_dict(orient = 'records')

cursor = conn.cursor()
  
a = [value1['ResultDescription'] for value1 in output if 'ResultDescription' in value1]
b = userPrincipalName = [
    json.loads(value2['InitiatedBy'])['user']['userPrincipalName']
    for value2 in output if 'InitiatedBy' in value2
]
c = ipAddr = [
    json.loads(value3['InitiatedBy'])['user']['ipAddress']
    for value3 in output if 'InitiatedBy' in value3
]
d = [value4['LoggedByService'] for value4 in output if 'LoggedByService' in value4]
e = [value5['Result'] for value5 in output if 'Result' in value5]
f = [value6['ResultReason'] for value6 in output if 'ResultReason' in value6]
g = [value7['ActivityDisplayName'] for value7 in output if 'ActivityDisplayName' in value7]
h = [value8['ActivityDateTime'] for value8 in output if 'ActivityDateTime' in value8]


number_of_d = len(d)
print(number_of_d)
cursor.execute("DELETE FROM _2FA_Auditlog_")

z = -1
for a1 in a:
    z+=1
    b1 = b[z]
    c1 = c[z]
    d1 = d[z]
    e1 = e[z]
    f1 = f[z]
    g1 = g[z]
    h1 = h[z]
    #Check the table names and column names are matched yours.
    cursor.execute("INSERT INTO _2FA_Auditlog_ (ResultDescription, InitiatedBy, IPaddr, LoggedByService, Result, ResultReason, ActivityDisplayName, UTC) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", a1, b1, c1, d1, e1, f1, g1, h1)

output = cursor.execute("Select * FROM _2FA_Auditlog_")

output_html = """
<table border='1' cellpadding='5' cellspacing='0' style='font-family: Arial, sans-serif; font-size: 12px;'>
    <tr>
        <th style='font-size: 12px;'>ResultDescription</th>
        <th style='font-size: 12px;'>InitiatedBy</th>
        <th style='font-size: 12px;'>IP Address</th>
        <th style='font-size: 12px;'>LoggedByService</th>
        <th style='font-size: 12px;'>Result</th>
        <th style='font-size: 12px;'>Result Reason</th>
        <th style='font-size: 12px;'>ActivityDisplayName</th>
        <th style='font-size: 12px;'>UTC</th>
    </tr>
"""

for row in output:
    output_html += "<tr>"
    output_html += "".join([f"<td style='font-size: 12px;'>{str(item)}</td>" for item in row])
    output_html += "</tr>"
output_html += "</table>"

# Insert the generated HTML into the email template
html = f"""
<html>
<body style='font-family: Arial, sans-serif; font-size: 10px;'>
    <h2 style='font-size: 14px; font-weight: normal;'>Hello,</h2>
    <h2 style='font-size: 14px; font-weight: normal;'>This is the daily digest of Advantech users' MFA activity from Microsoft Azure Active Directory, the list has also included global employees. You might check for your users if you see any atypical activities on their accounts. If you have any questions, please feel free to contact ANA.IT.Helpdesk@advantech.com.</h2>
    <br></br>
    {output_html}
    
</body>
</html>
"""

part = MIMEText(html, "html")
msg = MIMEMultipart("alternative")
msg["Subject"] = "Daily Digest of Advantech MFA Activity"
msg["From"] = sender_email
msg["To"] = receiver_email
#msg["X-Priority"] = '2'

msg.attach(part)

with smtplib.SMTP('localhost') as server:
    server.sendmail(
        sender_email, receiver_email, msg.as_string()
        )


conn.commit()
cursor.close()