CODEWITHSUNDEEP
python-3.xmongodbpymongo
Rohit Maharashi
Rohit Maharashi

Reputation: 71

MongoDB Aggregation pipeline python

I have a collection of log files and i am required to find the number of times a system shows a message "Average limit exceeded while connecting ..." in a given date range and display the result for all the systems in the given date range in descending order

Currently my documents in the mongodb collection look like

{'computerName':'APOOUTRDFG', 'datetime': 11/27/2019 10:45:23.123 'message': 'Average limit ....' } So, I have tried filtering my result by first matching the message string and then grouping them by computer name but this does not help out the case

db.collection.aggregate([
{ "$match": {
   'message': re.compile(r".*Average limit.*")
},
{ "$group": {
    "_id": { "$toLower": "$computerName" },
    "count": { "$sum": 1 }
} }

])

Expected results

Date : 01-01-2012 to 31-01-2012

Computer Name Number of Average limit exceeded
computername1 120
computername2 83
computername3 34

Upvotes: 0

Views: 167

Answers (1)

Himanshu Sharma
Himanshu Sharma

Reputation: 3010

Assuming you have the following data in DB:

[
  {
    "computerName": "APOOUTRDFG",
    "datetime": "11/27/2019 10:45:23.123",
    "message": "Average limit ...."
  },
  {
    "computerName": "BPOOUTRDFG",
    "datetime": "01/02/2012 10:45:23.123",
    "message": "Average limit ...."
  },
  {
    "computerName": "CPOOUTRDFG",
    "datetime": "01/30/2012 10:45:23.123",
    "message": "Average limit ...."
  },
  {
    "computerName": "DPOOUTRDFG",
    "datetime": "01/30/2012 10:45:23.123",
    "message": "Some other message ...."
  }
]

Note: 'datetime' is format %m/%d/%Y %H:%M:%S.%L and input date range is in the format: %d-%m-%Y

The following query can get you the expected output:

db.collection.aggregate([
    {
        $match:{
            "message": /.*Average limit.*/i,
            $expr:{
                $and:[
                    {
                        $gte:[
                            {
                                $dateFromString:{
                                    "dateString":"$datetime",
                                    "format":"%m/%d/%Y %H:%M:%S.%L"
                                }
                            },
                            {
                                $dateFromString:{
                                    "dateString":"01-01-2012",
                                    "format":"%d-%m-%Y"
                                }
                            }
                        ]
                    },
                    {
                        $lte:[
                            {
                                $dateFromString:{
                                    "dateString":"$datetime",
                                    "format":"%m/%d/%Y %H:%M:%S.%L"
                                }
                            },
                            {
                                $dateFromString:{
                                    "dateString":"31-01-2012",
                                    "format":"%d-%m-%Y"
                                }
                            }
                        ]
                    }
                ]
            }
        }
    },
    {
        $group:{
            "_id":{
                $toLower:"$computerName"
            },
            "count":{
                $sum:1
            }
        }
    }
]).pretty()

Recommended: Its better to save date as ISODate or as timestamp in DB.

Upvotes: 1

Related Questions