Python Mongodb examples

1) count of hits within day

db.aclog.find().limit(1)
{ "_id" : ObjectId("5c8668883896e834e079d372"), "ip" : "74.208.58.75", "datetime" : "06/Mar/2019:06:25:15 +0000", "getpost" : "GET /wppluginfile/defscript HTTP/1.0", "status" : "200", "sbyte" : "31", "refer" : "-", "agent" : "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36", "loadtime" : "0.011" }


from pymongo import MongoClient
import pprint

conn = MongoClient()
db = conn.accesslog
coll = db.aclog

pipeline = [{"$project": {"_id": 0, "day_of_month": {"$substr": ["$datetime", 0, 2]}}},
    {"$group": {"_id": {"day_of_month": "$day_of_month"}, "hits": {"$sum": 1}}},
    {"$sort": {"hits": -1}}
]

pprint.pprint(list(db.aclog.aggregate(pipeline)))

conn.close()

result

[{'_id': {'day_of_month': '05'}, 'hits': 157148},

 {'_id': {'day_of_month': '06'}, 'hits': 61932}]

"$substr": ["$datetime", 0, 2] - cut the date from "06/Mar/2019:06:25:15 +0000"

2) count of hits ip in whole log file 

pipeline = [{"$project": {"ip": "$ip"}},

    {"$group": {"_id": {"ip": "$ip"}, "hits": {"$sum": 1}}},

    {"$sort": {"hits": -1}},

    {"$limit": 50}

]

result

[{'_id': {'ip': '177.187.118.164'}, 'hits': 26504},

 {'_id': {'ip': '262.213.252.236'}, 'hits': 23501},

3) ip and status

pipeline = [{"$project": {"ip": "$ip", "status": "$status"}},

    {"$group": {"_id": {

                       "ip": "$ip",

                       "status": "$status"

                       },

                "hits": {"$sum": 1}}},

    {"$sort": {"hits": -1}},

    {"$limit": 50}

]

result

[{'_id': {'ip': '162.233.252.36', 'status': '200'}, 'hits': 23501},

 {'_id': {'ip': '177.177.118.14', 'status': '200'}, 'hits': 15902},

 {'_id': {'ip': '74.258.58.75', 'status': '200'}, 'hits': 10759},

 {'_id': {'ip': '203.192.219.141', 'status': '200'}, 'hits': 10715},

 {'_id': {'ip': '177.187.118.14', 'status': '301'}, 'hits': 10602},

4) ip, status and day

pipeline = [{"$project": {"ip": 1, "status": 1, "day": {"$substr": ["$datetime", 0, 2]}}},
    {"$group": {"_id": {
                       "ip": "$ip",
                       "status": "$status",
                       "day": "$day"
                       },
                "hits": {"$sum": 1}}},
    {"$sort": {"hits": -1}},
    {"$limit": 50}
]

result

 {'_id': {'day': '05', 'ip': '177.157.118.14', 'status': '301'}, 'hits': 7768},

 {'_id': {'day': '06', 'ip': '162.263.252.36', 'status': '200'}, 'hits': 6141},

5) ip, status, day and list of api requests

# ip, status, day and list of requests
pipeline = [{"$project": {
                          "_id": 0,
                          "ip": 1,
                          "status": 1,
                          "day": {"$substr": ["$datetime", 0, 2]},
                          "getpost": 1
                         }
            },
    {"$group": {"_id": {
                        "ip": "$ip",
                        "status": "$status",
                        "day": "$day"
                       },
                "hits": {"$sum": 1},
                "requests": {"$addToSet": "$getpost"}
               }
    },
    {"$sort": {"hits": -1}},
    {"$limit": 50}
]

result

 {'_id': {'day': '06', 'ip': '5.157.169.140', 'status': '200'},
  'hits': 3060,
  'requests': ['GET /api/get-pool-stats/web HTTP/1.0',
               'GET /api/profile/get-stats/web HTTP/1.0']},

$addToSet - Returns an array of all unique value. $addToSet is only available in the $group stage.