Question

0

158

Views

Group By IP class

I save log access in MongoDB like

{
   "Host": "www.foo.com"
   "CustomField":"X-FORWARDED-FROM 10.10.10.10"
},{
   "Host": "www.foo.com"
   "CustomField":"X-FORWARDED-FROM 10.20.10.192"
},{
   "Host": "www.foo.com"
   "CustomField":"X-FORWARDED-FROM 10.10.20.159"
},{
   "Host": "www.foo.com"
   "CustomField":"X-FORWARDED-FROM 10.10.10.150"
}

I want to query with an output for summary ip access like

{
   "_id":"10.10.10.0", "count":2,
   "_id":"10.10.20.0", "count":1,
   "_id":"10.20.10.0", "count":1,
}

How do I go about this?

over 4 years ago · Santiago Trujillo

2 answers

Answer question

0

Answer question

Find remote jobs

Answer 1 · 2022-05-25T15:00:24.312Z

If we make assumption that the collection name is ips and the "CustomField" property is always being represented as "X-FORWARDED-FROM THE_IP_ADDRESS", then the following query aggregation gives the desired result:

db.ips.aggregate([{
    $project:{
        _id:{
            $substr:["$CustomField", 17, -1]
        }
    },
},{
    $project: {
      ip: {$split:["$_id", "."]}
    },
},{
    $project: {
      ip: {$slice:["$ip", 3]}
    },
}, {
    $project: {
      ip: {
        $reduce: {
          input: "$ip",
          initialValue: "",
          in: { $concat : ["$$value", "$$this", "."] }
        }
      }
    }
}, {
    $group:{
        _id: "$ip", count:{$sum:1}
    }
}, {
    $project: {
      _id:{$concat:["$_id", "0"]},
      count: 1
    }
}])

It does the following aggregation:

Takes IP address from the string
Splits the string into arrays of 4 parts of IP address
Removes the last part from the array
Concatenates array elements into a string
Groups them by IP address
Finally concatenates "0" to the _id field as a last part of IP address

Answer 2 · 2022-05-25T15:00:24.324Z

If X-FORWARDED-FROM string is fixed for every CustomField then can solve by using $substr.

db.CollectionName.aggregate([
    {$group:{
        _id:"$CustomField",
        count:{$sum:1}
        }
    },
    {$project:{
        _id: { $substr: [ "$_id", 17, -1] },
        count:1
        }
    }
])

where 17 means start from. that's the length of X-FORWARDED-FROM string

Updated:

db.CollectionName.aggregate([
    {$project:{
        ip: {$concat: [{ $substr: [ "$CustomField", 17,8] },'.0']}
        }
    },
    {$group:{
        _id:"$ip",
        count:{$sum:1}
        }
    }
])

for MongoDB 3.4 can use

db.CollectionName..aggregate([
    {$project:{
        ip:{ $split: [ { $substr: [ "$CustomField", 17,-1] }, "." ] }//ip: ["10","10","10","192"]
       }
    },
    {$project:{
        ip:{ $concat: [ 
            { $arrayElemAt: [ "$ip", 0 ] },
            " . ", 
            { $arrayElemAt: [ "$ip", 1 ] },
            ".",
            { $arrayElemAt: [ "$ip", 2 ] },
            ".0"
            ] }
       },
    },
    {$group:{
        _id:"$ip",
        count:{$sum:1}
        }
    }
])

0

158

Group By IP class

2 answers

0

0

Find remote jobs

Andres GPT