1.mongodb常用操作
1.1聚合，查询，更新
// 3.1
db.sensitive_info_base.aggregate([
{
"$match" :{"date":{ "$gte" : 20201223}}
},
{ "$group" : { "_id" : "$sensitive_type", "name" : { "$first" : "$sensitive_type_name" }, "value" : { "$sum" : 1 } }
},
{ "$sort" : { "value" : -1 } }
])
db.sensitive_info_base.update({
"_id":ObjectId("5feaec041729c16fb4e6dce6")
},
{$set:{"Fat":"daaa"}},
false,true
)
db.sensitive_info_base.update(
{"visit_time.20201229":{$exists: true }},
{$set:{"date": 20201229}},
false,
true
)
db.sensitive_info_base.find({visit_time: {$ne:{}}})
db.sensitive_info_base.createIndex({"visit_time":1,"sensitive_type":1}, {background: true})
db.sensitive_info_base.createIndex({"visit_time":1,"sensitive_type":1})
db.sensitive_info_base.dropIndex({visit_time:1})
db.sensitive_info_base.dropIndex({"visit_time":1,"sensitive_type":1})
// 3.2
db.sensitive_info_base.aggregate([
{
"$match" :{"date":{ "$gte" : 20201223}}
},
{ "$group" : {
"_id" : "$src_ip",
"srcIp" : { "$first" : "$src_ip" },
"totalCount" : { "$sum" : 1 }
}},
{ "$sort" : {
"totalCount" : -1
}},
{ "$limit" : 10
},
{ "$group" : {
"_id" : null,
"srcIp" : { "$push" : "$srcIp" },
"totalCount" : { "$push" : "$totalCount" }
}}
])
db.sensitive_info_base.createIndex({"srcIp":1})
db.sensitive_info_base.createIndex({"timestamp":1})
// 3.3
db.sensitive_info_base.aggregate([
{
"$match" :{"date":{ "$gte" : 20201223}}
},
{"$group":{
"_id":"$url",
"srcIp":{
"$first":"$url"
},
"totalCount":{
"$sum":1
}
}
},
{
"$sort":{
"totalCount":-1
}
},
{ "$limit" : 10
},
{
"$group":{
"_id":null,
"srcIp":{
"$push":"$srcIp"
},
"totalCount":{
"$push":"$totalCount"
}
}
}
])
// 3.4
db.sensitive_info_base.aggregate([
{ "$match" : { "date" : { "$gte" : 20201230 } } },
{ "$group" : {
"_id" : { "application" : "$application", "sensitive_type" : "$sensitive_type" },
"app" : { "$first" : "$application" }, "domain" : { "$first" : "$hostname" },
"rulename" : { "$first" : "$sensitive_type_name" }, "rulehit" : { "$sum" : 1 },
"urls" : { "$addToSet" : "$url" }
} },
{ "$group" : { "_id" : "$app", "app" : { "$first" : "$app" }, "domain" : { "$first" : "$domain" }, "urls" : { "$addToSet" : "$urls" }, "senHit" : { "$sum" : "$rulehit" },
"info" : {
"$push" : {
"rulename" : "$rulename",
"rulehit" : {
"$sum" : "$rulehit"
}
}
}
}
},
{ "$sort" : { "senHit" : -1, "info.rulename" : -1, "info.rulehit" : -1 }},
{ "$skip" : 0 },
{ "$limit" : 20 }
])
db.sensitive_info_base.aggregate([
{ "$match" : { "timestamp" : { "$gte" : 1609516800000 } , "application" : "10.100.101.1:2379" } },
{ "$group" : { "_id" : { "url" : "$url", "sensitive_type" : "$sensitive_type" },
"hostname" : { "$first" : "$hostname" },
"app" : { "$first" : "$application" },
"url" : { "$first" : "$url" },
"ruleid" : { "$first" : "$sensitive_type" },
"rulename" : { "$first" : "$sensitive_type_name" },
"last_visit" : { "$max" : "$timestamp" },
"rulehit" : { "$sum" : 1 } } },
{ "$group" : { "_id" : "$url", "url" : { "$first" : "$url" }, "lastVisit" : { "$max" : "$last_visit" }, "info" : { "$push" : { "rulename" : "$rulename",
"rulehit" : { "$sum" : "$rulehit" } } }, "senHit" : { "$sum" : "$rulehit" } } },
{ "$sort" : { "senHit" : -1 } },
{ "$skip" : 0 },
{ "$limit" : 20 }
)]
// url day aggregate
db.sensitive_info_base.aggregate([
{ "$match" : { "date" : 20210112}},
{ "$group" : { "_id" : { "url" : "$url", "sensitive_type" : "$sensitive_type", "date": "$date" },
"hostname" : { "$first" : "$hostname" },
"app" : { "$first" : "$application" },
"url" : { "$first" : "$url" },
"ruleid" : { "$first" : "$sensitive_type" },
"rulename" : { "$first" : "$sensitive_type_name" },
"date" : {"$first" : "$date"}
"last_visit" : { "$max" : "$timestamp" },
"rulehit" : { "$sum" : 1 } } }
// ,
// {
// $count: "totalCount"
// }
)]
db.sensitive_info_base.aggregate([
{ "$match" : { "date" : 20210112}},
{ "$group" : { "_id" : { "url" : "$url", "sensitive_type" : "$sensitive_type", "date": "$date" },
} }
// ,
// {
// $count: "totalCount"
// }
)]
db.sensitive_info_base.aggregate([
{ "$match" : { "date" : 20210112}},
{ "$group" : { "_id" : { "app" : "$application", "sensitive_type": "$sensitive_type", "date": "$date"}
} }
,
{
$count: "totalCount"
}
)]
db.sensitive_info_base.aggregate([
{ "$match" : { "date" : 20210112}},
{
"$group" : { "_id" : { "app" : "$application", "sensitive_type": "$sensitive_type", "date": "$date"},
"domain" : { "$first" : "$hostname" },
"app" : { "$first" : "$application" },
"rulename" : { "$first" : "$sensitive_type_name" },
"date" : {"$first" : "$date"},
"rulehit" : { "$sum" : 1 },
"urls": {"$addToSet" : "$url"}
}
},
{ "$group" :{ "_id" : {"app":"$app", "date": "$date"},
"app" : { "$first" : "$app" },
"domain" : { "$first" : "$domain" },
"urls" : { "$addToSet" : "$urls" },
"senHit" : { "$sum" : "$rulehit" },
"date" : {"$first" : "$date"},
"info" : {
"$push" : {
"rulename" : "$rulename",
"rulehit" : {
"$sum" : "$rulehit"
}
}
}
}
}
// ,
// { "$skip" : 0 },
// { "$limit" : 20 }
// ,
// {
// $count: "totalCount"
// }
)]
db.sensitive_info_base.aggregate([
// { "$match" : { "date" : 20210112}},
// {
// "$group" : { "_id" : { "app" : "$application", "sensitive_type": "$sensitive_type", "date": "$date"}
// }
// },
{ "$group" :{ "_id" : {"app":"$application", "date": "$date"}
}
}
,
{
$count: "totalCount"
}
)]
# 聚合后，在$reduce之后使用$setUnion 合并子数组
db.sensitive_info_base.aggregate([
{ "$match" : { "date" :{"$gte":20210111}}},
{
"$group" : { "_id" : { "app" : "$application", "sensitive_type": "$sensitive_type", "date": "$date"},
"domain" : { "$first" : "$hostname" },
"app" : { "$first" : "$application" },
"ruleid" : { "$first" : "$sensitive_type" },
"rulename" : { "$first" : "$sensitive_type_name" },
"date" : {"$first" : "$date"},
"rulehit" : { "$sum" : 1 },
"urls": {"$addToSet" : "$url"}
}
}
,
{ "$group" :{ "_id" : {"app":"$app", "rulename": "$rulename"},
"app" : { "$first" : "$app" },
"domain" : { "$first" : "$domain" },
"urls" : { "$addToSet" : "$urls"},
"rulehit" : { "$sum" : "$rulehit" },
"date" : {"$first" : "$date"},
"rulename" : {"$first" : "$rulename" },
}
}
,
{
"$project" : {
"urls" : 1,
"app" : 1,
"rulehit": 1,
"rulename" :1,
"domain" :1,
"date" :1,
"urls" : {
"$reduce" : {
"input" : "$urls",
"initialValue" : [],
"in" : {"$setUnion" : ["$$value", "$$this"]}
}
}
}
}
// ,
// { "$addFields": {
// "urls": {
// "$reduce": {
// "input": "$urls",
// "initialValue": [],
// "in": { "$setUnion": [ "$$value", "$$this" ] }
// }
// }
// }}
,
{ "$group" :{ "_id" : {"app":"$app"},
"app" : { "$first" : "$app" },
"domain" : { "$first" : "$domain" },
"urls" : { "$addToSet" : "$urls" },
"senHit" : { "$sum" : "$rulehit" },
"date" : {"$first" : "$date"},
"info" : {
"$push" : {
"rulename" : "$rulename",
"rulehit" : {
"$sum" : "$rulehit"
}
}
}
}
}
// ,
// { "$skip" : 0 },
// { "$limit" : 20 }
// ,
// {
// $count: "totalCount"
// }
)]
db.sensitive_info_base.update({
"application" : "10.100.5.2:80"
},
{$set:{"date":20210104}},
false,true
)
db.sensitive_info_base.aggregate([
{ "$match" : { "timestamp" : { "$gte" : 1607443200000 } } },
{ "$group" : { "_id" : "$application" } },
{ "$count" : "cnt" }
])
db.sensitive_info_base.createIndex({"application":1})
db.sensitive_info_base.createIndex({"date":1})
// 3.5
db.ret_statistic_record.createIndex({"sen_hit":1,"date":1})
db.ret_statistic_record.aggregate([
{ "$match" : { "sen_hit" : { "$gt" : 0 }, "date" : { "$gte" : 20201209 } } },
{ "$group" : { "_id" : "$app" } },
{ "$count" : "cnt" }
])
db.ret_statistic_record.aggregate([
{ "$match" : { "date" : { "$gte" : 20201209 } } },
{ "$group" : { "_id" : "$app" } },
{ "$count" : "cnt" }
]).explain()
db.ret_statistic_record.aggregate([
{"$match" : { "sen_hit" : { "$gt" : 0 }, "date" : { "$gte" : 20201209 } } },
{ "$group" : { "_id" : "$url" } },
{ "$count" : "cnt" }
])
db.ret_statistic_record.aggregate([
{"$match" : { "date" : { "$gte" : 20201209 } } },
{ "$group" : { "_id" : "$url" } },
{ "$count" : "cnt" }
])
db.sensitive_info_base.count({"timestamp":{"$gte": 1607443200000}})
// 3.6
db.ret_statistic_record.aggregate(
[{ "$match" : { "date" : { "$gte" : 20201223, "$lt" : 20201230 } } },
{ "$group" : { "_id" : "$date", "date" : { "$first" : "$date" },
"count" : { "$sum" : "$sen_hit" } } },
{ "$sort" : { "date" : -1 } }
])
db.ret_access_count.find({"date" : { "$gte" : 20201209, "$lt" : 20201215 } })
db.sensitive_info_base.find({ "visit_time.20201211" : { "$exists" : true }})
db.sensitive_info_base.remove({visit_time: {}})
db.sensitive_info_base.deleteMany({visit_time: {}})
db.ret_statistic_record.find({"date" : {"$lt" : 20201201 } })
db.sensitive_info_base.find({visit_time: {}})
db.sensitive_info_base.aggregate([
{ "$match" : { "date" : { "$gte" : 20201230 } } },
{ "$group" : {
"_id" : { "application" : "$application", "sensitive_type" : "$sensitive_type" }
} },
// { "$group" : { "_id" : "$app", "app" : { "$first" : "$app" }, "domain" : { "$first" : "$domain" }, "urls" : { "$addToSet" : "$urls" }, "senHit" : { "$sum" : "$rulehit" },
// "info" : {
// "$push" : {
// "rulename" : "$rulename",
// "rulehit" : {
// "$sum" : "$rulehit"
// }
// }
// }
// }
// },
// { "$sort" : { "senHit" : -1, "info.rulename" : -1, "info.rulehit" : -1 }},
// { "$skip" : 0 },
{ "$limit" : 20 }
])
db.sensitive_info_base.remove({
"timestamp" : { "$gte" : 1609100000000 }
})
// 更新7天date字段
// 0101
db.sensitive_info_base.update({
"timestamp" : { "$gte" : 1609430400000, "$lt" : 1609516800000 }
},
{$set:{"date":20210101}},
false,true
)
db.sensitive_info_base.find(
{
"timestamp" : { "$gte" : 1609430400000, "$lt" : 1609516800000 }
}
)
.sort({timestamp:-1})
.limit(100)
// 0102
db.sensitive_info_base.update({
"timestamp" : { "$gte" : 1609516800000, "$lt" : 1609603200000 }
},
{$set:{"date":20210102}},
false,true
)
db.sensitive_info_base.find(
{
"timestamp" : { "$gte" : 1609516800000, "$lt" : 1609603200000 }
}
)
.sort({timestamp:-1})
.limit(100)
// 0103
db.sensitive_info_base.update({
"timestamp" : { "$gte" : 1609603200000, "$lt" : 1609689600000 }
},
{$set:{"date":20210103}},
false,true
)
db.sensitive_info_base.find(
{
"timestamp" : { "$gte" : 1609603200000, "$lt" : 1609689600000 }
}
)
.sort({timestamp:-1})
.limit(100)
// 0104
db.sensitive_info_base.update({
"timestamp" : { "$gte" : 1609689600000, "$lt" : 1609776000000 }
},
{$set:{"date":20210104}},
false,true
)
db.sensitive_info_base.find(
{
"timestamp" : { "$gte" : 1609689600000, "$lt" : 1609776000000 }
}
)
.sort({timestamp:-1})
.limit(100)
// 0105
db.sensitive_info_base.update({
"timestamp" : { "$gte" : 1609776000000, "$lt" : 1609862400000 }
},
{$set:{"date":20210105}},
false,true
)
db.sensitive_info_base.find(
{
"timestamp" : { "$gte" : 1609776000000, "$lt" : 1609862400000 }
}
)
.sort({timestamp:-1})
.limit(100)
// 0106
db.sensitive_info_base.update({
"timestamp" : { "$gte" : 1609862400000, "$lt" : 1609948800000 }
},
{$set:{"date":20210106}},
false,true
)
db.sensitive_info_base.find(
{
"timestamp" : { "$gte" : 1609862400000, "$lt" : 1609948800000 }
}
)
.sort({timestamp:-1})
.limit(100)
// 0107
db.sensitive_info_base.update({
"timestamp" : { "$gte" : 1609948800000, "$lt" : 1610035200000 }
},
{$set:{"date":20210107}},
false,true
)
db.sensitive_info_base.find(
{
"timestamp" : { "$gte" : 1609948800000, "$lt" : 1610035200000 }
}
)
.sort({timestamp:-1})
.limit(100)
2.插入
for (var i=0;i<1;i++) {
db.sensitive_info_base.insertMany([
/* 1 */
{
"application" : "10.100.101.1",
"create_at" : ISODate("2021-01-01T20:21:46.645+08:00"),
"hostname" : "10.100.101.1:2379",
"sensitive_data" : "1",
"sensitive_type" : 55,
"sensitive_type_name" : "照",
"src_ip" : "10.100.101.3",
"timestamp" : 1610441156000,
"url" : "http://10.100.101.1/name/key/",
"urlpath" : "/name/key/",
"username" : "--",
"date": 20210112,
"visit_time" : {
"20210112" : [
1610441156000
]
}
}
])
}
