pritunl mongodb query for user info

pritunl_mongo_audit_query.py

# https://gist.github.com/jonathanhle/4bb44d2e5d3ace8a62928ec2cb3e39a7
# Requires pymongo 3.6.0+
from datetime import datetime, timedelta
from pymongo import MongoClient
from bson.tz_util import FixedOffset
from bson.son import SON
from collections import OrderedDict

# Setup logger
import logging
import logging.handlers
import syslog
import pytz

logger = logging.getLogger('myLogger')
logger.setLevel(logging.INFO)

#add handler to the logger
handler = logging.handlers.SysLogHandler(address = '/dev/log', facility = "local1")

#add formatter to the handler
#formatter = logging.Formatter('%(module)s.%(funcName)s: %(message)s')
formatter = logging.Formatter('%(module)s: %(message)s')

handler.setFormatter(formatter)
logger.addHandler(handler)

# Get 10 minutes previous ISO
fiveminutespast = (datetime.now() - timedelta(minutes=10)).strftime("%Y-%m-%d %H:%M:%S.%f")

client = MongoClient("mongodb://localhost:27017/", document_class=OrderedDict)
database = client["pritunl"]
collection = database["users_audit"]

pipeline = [
    {
        "$lookup": {
            "from": "users",
            "localField": "user_id",
            "foreignField": "_id",
            "as": "matched_user"
        }
    },
    {
        "$unwind": {
            "path": "$matched_user"
        }
    },
    {
        "$match": {
            "timestamp": {
                "$gte": datetime.strptime(fiveminutespast, "%Y-%m-%d %H:%M:%S.%f").replace(tzinfo = FixedOffset(480, "+0800"))
            }
        }
    },
    {
        "$project": {
            "_id": 0.0,
            "user_id": 1.0,
            "remote_addr": 1.0,
            "timestamp": 1.0,
            "org_id": 1.0,
            "message": 1.0,
            "type": 1.0,
            "matched_user.auth_type": 1.0,
            "matched_user.name": 1.0,
            "matched_user.type": 1.0,
            "matched_user.email": 1.0
        }
    },
    {"$sort": SON([("count", -1), ("_id", -1)])}
]

cursor = collection.aggregate(
    pipeline,
    allowDiskUse = False
)

try:
    for doc in cursor:
        logger.info(doc["matched_user"]["type"] + ": " + doc["matched_user"]["email"] + " (" + str(doc["user_id"]) + ") " + doc["message"] + " fr                                                                                            om [" + doc["remote_addr"] + "] at " + doc["timestamp"].replace(tzinfo=FixedOffset(0, "+0000")).astimezone(pytz.timezone("Asia/Taipei")).strftime                                                                                            ("%Y-%m-%d %H:%M:%S") + " (type: " + doc["type"] + ")")
        # All output goes to syslog; uncomment the following line to troubleshoot
        #print(doc["matched_user"]["type"] + ": " + doc["matched_user"]["email"] + " (" + str(doc["user_id"]) + ") " + doc["message"] + " from ["                                                                                             + doc["remote_addr"] + "] at " + doc["timestamp"].replace(tzinfo=FixedOffset(0, "+0000")).astimezone(pytz.timezone("Asia/Taipei")).strftime("%Y-                                                                                            %m-%d %H:%M:%S") + " (type: " + doc["type"] + ")")
finally:
    client.close()