Overview
Server users sometimes need to summarize build counts and durations over a specific period. This article will focus query and aggregate historical build data on CircleCI Server using MongoDB’s aggregation framework.
Instructions
1. Use MongoDB Aggregation Query for Historical Data
Instead of running multiple find queries and post-processing output, use MongoDB’s aggregation. The following steps describe how to extract build count and total build duration using the aggregation framework:
Filter Build Data with
$match
Specify the desired date range for the "starttime" field:kubectl exec mongodb-0 -n circleci-server -it -- bash -c 'mongo --quiet mongodb://${MONGODB_USERNAME}:${MONGODB_ROOT_PASSWORD}@localhost/circle_ghe?authSource=${MONGODB_DATABASE} --eval '"'"' db.builds.aggregate([ { $match: { "start_time": { $gte: new ISODate("2024-09-01T15:00:00.000Z"), $lte: new ISODate("2024-10-31T14:59:59.999Z") } }}, { $project: { "vcs_url": 1, "build_num": 1, "picard.executor": 1, "picard.resource_class.class": 1, "parallel": 1, "start_time": 1, "stop_time": 1, "duration_min": { $sum: { $divide: [ { $subtract: ["$stop_time", "$start_time"] }, 60000 ] } } }}, { $group: { _id: null, buildCount: { $sum: 1 }, totalDurationMin: { $sum: "$duration_min" } }} ]).forEach(printjson); '"'"'Group by Project (Optional)
To review statistics by project URL:kubectl exec mongodb-0 -n circleci-server -it -- bash -c 'mongo --quiet mongodb://${MONGODB_USERNAME}:${MONGODB_ROOT_PASSWORD}@localhost/circle_ghe?authSource=${MONGODB_DATABASE} --eval '"'"' db.builds.aggregate([ { $match: { "start_time": { $gte: new ISODate("2024-09-01T15:00:00.000Z"), $lte: new ISODate("2024-10-31T14:59:59.999Z") } }}, { $project: { "vcs_url": 1, "build_num": 1, "picard.executor": 1, "picard.resource_class.class": 1, "parallel": 1, "start_time": 1, "stop_time": 1, "duration_min": { $sum: { $divide: [ { $subtract: ["$stop_time", "$start_time"] }, 60000 ] } } }}, { $group: { _id: "$vcs_url", buildCount: { $sum: 1 }, totalDurationMin: { $sum: "$duration_min" } }} ]).forEach(printjson); '"'"'
2. Example Output
The output for the overall aggregation might resemble the following:
{
"_id" : null,
"buildCount" : 215,
"totalDurationMin" : 357.84236666666663
}When grouping by the project URL, you might see:
{
"_id" : "https://github.com/example/project-one",
"buildCount" : 176,
"totalDurationMin" : 169.85115
}
{
"_id" : "https://github.com/example/project-two",
"buildCount" : 2,
"totalDurationMin" : 0.10455
}
{
"_id" : "https://github.com/example/project-three",
"buildCount" : 17,
"totalDurationMin" : 57.4958
}Solution
The aggregation pipeline combines filtering, projection, and grouping stages. This approach avoids inconsistencies observed in sequential find queries and post-processing, ensuring that the aggregated data accurately covers the entire date range. The use of MongoDB’s aggregation framework simplifies the extraction of key build statistics while reducing potential issues related to command output limitations.