Last active
November 30, 2017 11:38
-
-
Save pfurio/11294c7efc9762c739a1322e533a3f28 to your computer and use it in GitHub Desktop.
OpenCGA: Extract number of times every user logged in in a month
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env python | |
from pymongo import MongoClient | |
from bson import ObjectId | |
import datetime | |
import argparse | |
parser = argparse.ArgumentParser(description='Get the number of times each user have logged in during a period of time.') | |
parser.add_argument('host', metavar='host', help='Mongo host and port. Example: localhost:27017.') | |
# parser.add_argument('database', metavar='database', help='Mongo database name. Example: opencga_catalog.') | |
parser.add_argument('date_from', metavar='from', help='Start date. Format: YYYY-MM-DD.') | |
parser.add_argument('date_to', metavar='to', help='End date. Format: YYYY-MM-DD.') | |
args = parser.parse_args() | |
mongoHost = "mongodb://" + args.host | |
# databaseName = args.database | |
databaseName = "opencga_catalog" | |
db = MongoClient(mongoHost) | |
client = db[databaseName] | |
from_split = args.date_from.split("-") | |
from_time = datetime.datetime(int(from_split[0]), int(from_split[1]), int(from_split[2])) | |
from_id = ObjectId.from_datetime(from_time) | |
to_split = args.date_to.split("-") | |
to_time = datetime.datetime(int(to_split[0]), int(to_split[1]), int(to_split[2])) | |
to_id = ObjectId.from_datetime(to_time) | |
result = client['audit'].aggregate([ | |
{ | |
"$match": { | |
"action": "login", | |
"_id": { | |
"$gte": from_id, "$lte": to_id | |
} | |
} | |
}, | |
{ | |
"$group": { | |
"_id": "$userId", | |
"count": { "$sum": 1} | |
} | |
}, | |
{ | |
"$sort": { | |
"count": -1 | |
} | |
} | |
]) | |
for r in result: | |
print str(r["count"]) + "\t" + r["_id"] |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
db.audit.aggregate([ | |
{ | |
$match: {action: "login"} | |
}, | |
{ | |
$project: { | |
userId: 1, | |
year: {$substr: ["$after.date", 0, 4]}, | |
month: {$substr: ["$after.date", 4, 2]}, | |
day: {$substr: ["$after.date", 6, 2]}, | |
} | |
}, | |
{ | |
$project: { | |
userId: 1, | |
date: {$concat: ["$year", "$month", "$day" ]} | |
} | |
}, | |
{ | |
$match: { | |
date: { | |
$gte: "20170124", $lte: "20170825" | |
} | |
} | |
}, | |
{ | |
$group: { | |
_id: "$userId", | |
count: { $sum: 1} | |
} | |
}, | |
{ | |
$sort: { | |
count: -1 | |
} | |
} | |
]) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Fields to be edited | |
year = 2017 | |
month = 7 // July | |
// Don't touch from here | |
year = year.toString(); | |
month = (month > 9 ? '' : '0') + month; | |
db.audit.aggregate([ | |
{ | |
$match: {action: "login"} | |
}, | |
{ | |
$project: { | |
userId: 1, | |
year: {$substr: ["$after.date", 0, 4]}, | |
month: {$substr: ["$after.date", 4, 2]} | |
} | |
}, | |
{ | |
$match: { | |
year: year, month: month | |
} | |
}, | |
{ | |
$group: { | |
_id: "$userId", | |
count: { $sum: 1} | |
} | |
} | |
]) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment