> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt # Retrieve Federated Database Instance Query History You can retrieve details about the queries that were run in the past 7 days using `$queryHistory` (aggregation). `$queryHistory` returns documents, one per query, that contain information about the [aggregate](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#std-label-aggregate-database-command), [find](https://www.mongodb.com/docs/manual/reference/command/find.md#std-label-find-database-command), and [count](https://www.mongodb.com/docs/manual/reference/command/count.md#std-label-count-database-command) queries that were run in the past 7 days. You can filter on the fields returned by `$queryHistory` in subsequent pipeline stages such as [`$match`.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) To run `$queryHistory`, use the [`db.aggregate()`](https://www.mongodb.com/docs/manual/reference/method/db.aggregate.md#mongodb-method-db.aggregate) helper. Run `$queryHistory` against the `admin` database. ## Syntax ```json { $queryHistory: { allUsers: } } ``` ## Fields | 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 Federation returns an error. If omitted, defaults to `false`. | Optional | ## Output 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 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` | [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) | 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` | [ObjectId](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-objectid) | Unique identifier of the operation associated with the query in the [ObjectId](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-objectid) format. The field value is the same as the `correlationID` that you can see in errors and logs. | | `startTime` | [ISODate](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-ISODate) | 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: `.`. Note that the authentication database for Atlas Data Federation is always `admin`. If the username of the user who ran the query is not available, value is empty. | ## Examples The examples below use the `Database0.Collection0` collection described in the [Get Started tutorial.](https://www.mongodb.com/docs/atlas/data-federation/tutorials.md#std-label-adf-tutorials) ### Retrieve Details About the Queries For the example below, suppose some of the queries described in the [Getting Started tutorial](https://www.mongodb.com/docs/atlas/data-federation/tutorial/run-queries-sample-data.md#std-label-gst-run-queries) 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. ```shell db.aggregate([{$queryHistory: {}}]).pretty() ``` `$queryHistory` returns one document for each query that ran on the `Database0.Collection0` collection. ```json { "_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 } ``` ### Filter `$queryHistory` Output for Specific Queries In the following example, we run a query similar to the following against the `Database0.Collection0` collection described in the [Getting Started tutorial.](https://www.mongodb.com/docs/atlas/data-federation/tutorials.md#std-label-adf-tutorials) The query includes unique strings to help identify the query in the results returned by the `$queryHistory` stage. You can attach a comment to a query using the [aggregate](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#std-label-aggregate-database-command) command `comment` option. ```shell use Database0 db.Collection0.aggregate([ { $match: {"account_id": 557378}},{$sort: {"transactions.symbol": -1}} ],{"comment":"exampleQuery"}) ``` To retrieve the query history using the string value of the `comment` option of the [aggregate](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#std-label-aggregate-database-command) command, run the following commands against the `admin` database: ```json use admin db.aggregate([{$queryHistory: {}}, {$match: {"comment": "exampleQuery"}} ]) ``` `$queryHistory` finds the query that included the specified string and returns results similar to the following example: ```json [ { "_id": ObjectId("61e1e4c29e62172566d8e9b6"), "query": [ { "$match": { "account_id": 557378} }, { "$sort": { "transactions.symbol": -1 } } ], "comment": "exampleQuery", "appName": "mongosh 1.1.8", "user": "admin.user1", "db": "Database0", "collection": "Collection0", "opid": ObjectId("16ca3ed2577016e68d60358c"), "startTime": ISODate("2022-01-14T21:01:27.346Z"), "endTime": ISODate("2022-01-14T21:01:54.627Z"), "ok": 1, "error": "", "background": false, } ] ```