Navigation

Retrieve Data Lake Query History

On this page

  • Syntax
  • Fields
  • Output
  • Example

You can retrieve details about the queries that were run in the past 24 hours using $queryHistory (aggregation). $queryHistory returns documents, one per query, that contain information about the aggregate, find, and count queries that were run in the past 24 hours. To run $queryHistory, use the db.aggregate helper. $queryHistory must be run against the admin database.

{
$queryHistory: {
allUsers: <boolean>
}
}
Field
Type
Description
Necessity
allUsers
boolean

Indicates whether or not to fetch documents for queries run by all users. Valid values are:

  • true to fetch documents for queries run by all users
  • false to fetch documents for queries run by the current user only

You must have the viewAllHistory privilege on the cluster resource to use this option. If you specify this option, but do not have viewAllHistory privilege on the cluster resource, Data Lake returns an error.

If omitted, defaults to false.

Optional

Each document returned by $queryHistory contains the following fields:

Field
Type
Description
appName
string
Name of the application that issued the query, if available.
background
boolean

Flag that indicates whether the query ran in the background. Value can be one of the following:

  • true - if the query ran with the background option set to true
  • false - if the query didn't specify the background option or if the query ran with the background option set to false
collection
string
Name of the collection on which the the query was executed.
comment
string
Comment associated with the query, if available. Empty if the query did not include any comment.
db
string
Name of the database that contains the collection on which the query was executed.
endTime
Query completion time.
error
string
Error, if any, returned by the query. Note that the query status 0 indicates errors. Empty string if query ran successfully.
ok
int

Status of the query. Value can be one of the following:

  • 1, if query ran successfully
  • 0, if there were errors when executing the query
opid
Unique identifier of the operation associated with the query.
startTime
Query start time.
query
document
Query operation that was run.
user
string
Username of the user who ran the query, if available, in the following format: <authenticationDatabase>.<username>. Note that the authentication database for Atlas Data Lake is always admin. If the username of the user who ran the query is not available, value is empty.

For the example below, suppose some of the queries described in the Getting Started tutorial were run by user1 on the Collection0 collection in the Database0 database. The following example returns information on the queries that were run by user1 on the Database0.Collection0 collection.

db.aggregate([{$queryHistory: {}}]).pretty()

$queryHistory returns one document for each query that ran on the Database0.Collection0 collection.

{
"_id" : ObjectId("613fa06cf9521f85777d5be8"),
"query" : [
{
"$match" : {
"bedrooms" : 3,
"review_scores.review_scores_rating" : {
"$gt" : 79
}
}
},
{
"$count" : "numProperties"
}
],
"appName" : "MongoDB Shell",
"user" : "admin.user1",
"db" : "Database0",
"collection" : "Collection0",
"opid" : ObjectId("16a476f40ac6d97f22e4aa1f"),
"startTime" : ISODate("2021-09-13T19:02:35.589Z"),
"endTime" : ISODate("2021-09-13T19:03:08.730Z"),
"ok" : 1,
"background" : false
}
{
"_id" : ObjectId("613fa0d4f9521f85777d6bc0"),
"query" : [
{
"$match" : {
"bedrooms" : 3
}
},
{
"$sort" : {
"review_scores_rating" : -1
}
},
{
"$limit" : NumberLong(5)
}
],
"appName" : "MongoDB Shell",
"user" : "admin.user1",
"db" : "Database0",
"collection" : "Collection0",
"opid" : ObjectId("16a4770e387f300c22e4bdf2"),
"startTime" : ISODate("2021-09-13T19:04:28.184Z"),
"endTime" : ISODate("2021-09-13T19:04:52.898Z"),
"ok" : 1,
"background" : false
}
{
"_id" : ObjectId("613fa0eef9521f85777d6f6f"),
"query" : [
{
"$match" : {
"limit" : {
"$eq" : 10000
},
"products" : "Commodity"
}
},
{
"$limit" : NumberLong(5)
}
],
"appName" : "MongoDB Shell",
"user" : "admin.user1",
"db" : "Database0",
"collection" : "Collection0",
"opid" : ObjectId("16a477163555e4aa22e4c53b"),
"startTime" : ISODate("2021-09-13T19:05:02.342Z"),
"endTime" : ISODate("2021-09-13T19:05:18.774Z"),
"ok" : 1,
"background" : false
}
{
"_id" : ObjectId("613fa147f9521f85777d7b11"),
"query" : [
{
"$match" : {
"name" : /Lannister/
}
},
{
"$limit" : NumberLong(10)
}
],
"appName" : "MongoDB Shell",
"user" : "admin.user1",
"db" : "Database0",
"collection" : "Collection0",
"opid" : ObjectId("16a4771ba072c0a122e4c9bd"),
"startTime" : ISODate("2021-09-13T19:05:25.736Z"),
"endTime" : ISODate("2021-09-13T19:06:47.147Z"),
"ok" : 1,
"background" : false
}
{
"_id" : ObjectId("613fb9eccaba4e6430c7dcb7"),
"query" : [
{
"$group" : {
"_id" : "$movies",
"Collection0" : {
"$push" : "$title"
}
}
},
{
"$out" : {
"atlas" : {
"projectId" : "{PROJECT_ID}",
"clusterName" : "mySbx",
"db" : "my_test",
"coll" : "sample"
}
}
}
],
"appName" : "MongoDB Shell",
"user" : "admin.user1",
"db" : "admin",
"collection" : "Collection0",
"opid" : ObjectId("16a47ceba943e9cc00c98c62"),
"startTime" : ISODate("2021-09-13T20:51:56.617Z"),
"endTime" : ISODate("2021-09-13T20:51:56.642Z"),
"ok" : 1,
"background" : true
}
Give Feedback

On this page

  • Syntax
  • Fields
  • Output
  • Example