{"_id":"57fcc4870312b20e00ac64e5","parentDoc":null,"version":{"_id":"57fcc4860312b20e00ac64c0","project":"5435687035740020002a1c04","__v":1,"createdAt":"2016-10-11T10:52:54.637Z","releaseDate":"2016-10-11T10:52:54.637Z","categories":["57fcc4860312b20e00ac64c1","57fcc4860312b20e00ac64c2","57fcc4860312b20e00ac64c3","57fcc4860312b20e00ac64c4","57fcc4860312b20e00ac64c5","57fcc4860312b20e00ac64c6","57fcc4860312b20e00ac64c7","57fcc4860312b20e00ac64c8","57fcc4860312b20e00ac64c9","57fcc4860312b20e00ac64ca","57fcc4860312b20e00ac64cb"],"is_deprecated":false,"is_hidden":false,"is_beta":false,"is_stable":true,"codename":"[APP-1265], [APP-1035]","version_clean":"5.2.0","version":"5.2"},"project":"5435687035740020002a1c04","user":"5435682035740020002a1c01","category":{"_id":"57fcc4860312b20e00ac64c8","__v":0,"version":"57fcc4860312b20e00ac64c0","project":"5435687035740020002a1c04","sync":{"url":"","isSync":false},"reference":false,"createdAt":"2015-11-04T23:13:08.376Z","from_sync":false,"order":7,"slug":"lua-scripting","title":"Agent Lua Scripting"},"__v":0,"updates":[],"next":{"pages":[],"description":""},"createdAt":"2015-11-24T19:33:13.879Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"settings":"","auth":"required","params":[],"url":""},"isReference":false,"order":7,"body":"The Agent's Lua environment comes with built-in support for accessing a variety of external database sources, including SQL-based RDBMSs and MongoDB.\n\nThis functionality is aimed primarily at _extracting data_ as opposed to providing more general-purpose functionality like inserting or deleting rows.\n\n# Accessing SQL databases \n\nThe `telemetry/sql` library provides access to SQL-based RDBMSs. The Agent supports these database systems:\n\n- MySQL\n- PostgreSQL\n\nYou can use the `open` function to access a database by providing a data source connection string in the appropriate format. For example:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local sql = require(\\\"telemetry/sql\\\")\\n\\nlocal mysql_local_db = sql.open(\\\"mysql\\\", \\\"user:pass:::at:::unix(/tmp/mysql.sock)/mydb\\\")\\nlocal mysql_remote_db = sql.open(\\\"mysql\\\", \\\"user:pass@tcp(server.example.com)/mydb\\\")\\n\\nlocal pgsql = sql.open(\\\"postgres\\\", \\\"postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full\\\")\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]\nYou can find more information about connection strings for [MySQL](https://github.com/go-sql-driver/mysql#dsn-data-source-name) and [PostgreSQL](https://godoc.org/github.com/lib/pq) on the websites for the respective drivers used internally by the library.\n[block:callout]\n{\n  \"type\": \"info\",\n  \"title\": \"Closing connections is not necessary\",\n  \"body\": \"Note that the Agent automatically manages and culls open connections to a RDBMS. There is no need to close a connection once you have opened it; you cannot, however, keep connections open between executions of a Lua script—they need to be reopened every time.\"\n}\n[/block]\n# Extracting data\n\nOnce you have obtained a connection to your database, you can execute queries against it by calling the `query` function of the connection table. For example:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local sql = require(\\\"telemetry/sql\\\")\\nlocal db = sql.open(\\\"mysql\\\", \\\"user:pass@unix(/tmp/mysql.sock)/mydb\\\")\\n\\nlocal rows = db.query(\\\"SELECT * FROM my_table WHERE name LIKE :1\\\", \\\"Marco\\\")\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]\nThe function returns a table array that contains one element for each row. For this reason, it's a good idea to be conservative with the amount of data that you extract at one time—if possible, for performance reason, it's better to let the database server perform aggregations and collations rather than attempting to do so inside an Agent script.\n\nThe `query` function supports named parameters; the exact nature of the syntax used depends on the underlying database server.\n\n# Accessing MongoDB databases\n\nThe `telemetry/mongodb` library can be used to access a MongoDB database. Due to the range of features that MongoDB offers, this is a little more complicated than the SQL library, though it should be quite easy to use in most cases.\n\nIn order to access a MongoDB database, you must connect to it by using the `open` function, to which you provide the address of your server or cluster. For example:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local mongo = require(\\\"telemetry/mongodb\\\")\\nlocal session = mongo.open(\\\"mongodb://localhost:27017/local\\\")\\n\\n-- Always close your session at the end of the script\\n\\nsession.close()\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]\n\n[block:callout]\n{\n  \"type\": \"danger\",\n  \"title\": \"MongoDB connections must be closed\",\n  \"body\": \"You are responsible for closing all the connections you open to a MongoDB server or server cluster by calling the `close` function of your session object. Failing to do so may lead to resource starvation and eventually cause the Agent to crash.\"\n}\n[/block]\n# Accessing MongoDB server metadata\n\nThe `live_servers` function of a MongoDB connection can be used to extract metadata about the servers themselves; this is useful, for example, if you want to write an Agent script to report on the health of your MongoDB cluster:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local mongo = require(\\\"telemetry/mongodb\\\")\\nlocal session = mongo.open(\\\"mongodb://localhost:27017/local\\\")\\n\\nlocal data = session.live_servers()\\n\\nsession.close()\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]\n# Accessing MongoDB databases and collections\n\nIn order to extract data from a MongoDB instance you must first select a database. The `database_names` function allows you to preview a list of available databases within the current Mongo session. Once you have identified a database you can use `db` to connect to it:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local mongo = require(\\\"telemetry/mongodb\\\")\\nlocal session = mongo.open(\\\"mongodb://localhost:27017/local\\\")\\n\\n-- Get a list of each of the databases accessible within mongodb://localhost:27017/local\\nlocal databases = session.database_names()\\n\\n-- Access the `local` database\\nlocal db = session.db(\\\"local\\\")\\n\\nsession.close()\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]\nMongo databases contain datasets called collections. We can view a list of collections within the current database by using `collections` followed by `collection` to connect to one:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local mongo = require(\\\"telemetry/mongodb\\\")\\nlocal session = mongo.open(\\\"mongodb://localhost:27017/local\\\")\\n\\n-- Access the `local` database\\nlocal db = session.db(\\\"local\\\")\\n\\n-- Get all the collections in this database\\nlocal collections = db.collections()\\n\\n-- Access the `startup_log` collection\\nlocal collection = db.collection(\\\"startup_log\\\")\\n\\nsession.close()\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]\nOnce you have access to a collection you can run queries against it using `find`. After executing the query you have access to the `count`, `distinct`, and `all` functions:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local mongo = require(\\\"telemetry/mongodb\\\")\\nlocal session = mongo.open(\\\"mongodb://localhost:27017/local\\\")\\nlocal db = session.db(\\\"local\\\")\\nlocal collection = db.collection(\\\"startup_log\\\")\\n\\n-- Create a query\\nlocal query = {}\\nquery[\\\"cmdLine.net.bindIp\\\"] = \\\"127.0.0.1\\\"\\n\\nlocal skip = 0\\nlocal limit = 10\\n\\n-- Execute the query\\nlocal result = collection.find(query, skip, limit)\\n\\n-- Get a count on the results\\nlocal count = result.count()\\n\\n-- Get results that have a distinct name\\nlocal distinct = result.distinct(\\\"name\\\")\\n\\n-- Get the actual values\\nlocal values = result.all()\\n\\nsession.close()\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]\n# Executing commands\n\nYou can use the `command` function of a database object to execute MongoDB commands against it. For example:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local mongo = require(\\\"telemetry/mongodb\\\")\\nlocal session = mongo.open(\\\"mongodb://localhost:27017/local\\\")\\nlocal db = session.db(\\\"admin\\\")\\n\\noutput.data = db.command({ ping = 1 })\\nsession.close()\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]","excerpt":"","slug":"databases","type":"basic","title":"Databases"}
The Agent's Lua environment comes with built-in support for accessing a variety of external database sources, including SQL-based RDBMSs and MongoDB. This functionality is aimed primarily at _extracting data_ as opposed to providing more general-purpose functionality like inserting or deleting rows. # Accessing SQL databases The `telemetry/sql` library provides access to SQL-based RDBMSs. The Agent supports these database systems: - MySQL - PostgreSQL You can use the `open` function to access a database by providing a data source connection string in the appropriate format. For example: [block:code] { "codes": [ { "code": "local sql = require(\"telemetry/sql\")\n\nlocal mysql_local_db = sql.open(\"mysql\", \"user:pass@unix(/tmp/mysql.sock)/mydb\")\nlocal mysql_remote_db = sql.open(\"mysql\", \"user:pass@tcp(server.example.com)/mydb\")\n\nlocal pgsql = sql.open(\"postgres\", \"postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full\")", "language": "lua" } ] } [/block] You can find more information about connection strings for [MySQL](https://github.com/go-sql-driver/mysql#dsn-data-source-name) and [PostgreSQL](https://godoc.org/github.com/lib/pq) on the websites for the respective drivers used internally by the library. [block:callout] { "type": "info", "title": "Closing connections is not necessary", "body": "Note that the Agent automatically manages and culls open connections to a RDBMS. There is no need to close a connection once you have opened it; you cannot, however, keep connections open between executions of a Lua script—they need to be reopened every time." } [/block] # Extracting data Once you have obtained a connection to your database, you can execute queries against it by calling the `query` function of the connection table. For example: [block:code] { "codes": [ { "code": "local sql = require(\"telemetry/sql\")\nlocal db = sql.open(\"mysql\", \"user:pass@unix(/tmp/mysql.sock)/mydb\")\n\nlocal rows = db.query(\"SELECT * FROM my_table WHERE name LIKE :1\", \"Marco\")", "language": "lua" } ] } [/block] The function returns a table array that contains one element for each row. For this reason, it's a good idea to be conservative with the amount of data that you extract at one time—if possible, for performance reason, it's better to let the database server perform aggregations and collations rather than attempting to do so inside an Agent script. The `query` function supports named parameters; the exact nature of the syntax used depends on the underlying database server. # Accessing MongoDB databases The `telemetry/mongodb` library can be used to access a MongoDB database. Due to the range of features that MongoDB offers, this is a little more complicated than the SQL library, though it should be quite easy to use in most cases. In order to access a MongoDB database, you must connect to it by using the `open` function, to which you provide the address of your server or cluster. For example: [block:code] { "codes": [ { "code": "local mongo = require(\"telemetry/mongodb\")\nlocal session = mongo.open(\"mongodb://localhost:27017/local\")\n\n-- Always close your session at the end of the script\n\nsession.close()", "language": "lua" } ] } [/block] [block:callout] { "type": "danger", "title": "MongoDB connections must be closed", "body": "You are responsible for closing all the connections you open to a MongoDB server or server cluster by calling the `close` function of your session object. Failing to do so may lead to resource starvation and eventually cause the Agent to crash." } [/block] # Accessing MongoDB server metadata The `live_servers` function of a MongoDB connection can be used to extract metadata about the servers themselves; this is useful, for example, if you want to write an Agent script to report on the health of your MongoDB cluster: [block:code] { "codes": [ { "code": "local mongo = require(\"telemetry/mongodb\")\nlocal session = mongo.open(\"mongodb://localhost:27017/local\")\n\nlocal data = session.live_servers()\n\nsession.close()", "language": "lua" } ] } [/block] # Accessing MongoDB databases and collections In order to extract data from a MongoDB instance you must first select a database. The `database_names` function allows you to preview a list of available databases within the current Mongo session. Once you have identified a database you can use `db` to connect to it: [block:code] { "codes": [ { "code": "local mongo = require(\"telemetry/mongodb\")\nlocal session = mongo.open(\"mongodb://localhost:27017/local\")\n\n-- Get a list of each of the databases accessible within mongodb://localhost:27017/local\nlocal databases = session.database_names()\n\n-- Access the `local` database\nlocal db = session.db(\"local\")\n\nsession.close()", "language": "lua" } ] } [/block] Mongo databases contain datasets called collections. We can view a list of collections within the current database by using `collections` followed by `collection` to connect to one: [block:code] { "codes": [ { "code": "local mongo = require(\"telemetry/mongodb\")\nlocal session = mongo.open(\"mongodb://localhost:27017/local\")\n\n-- Access the `local` database\nlocal db = session.db(\"local\")\n\n-- Get all the collections in this database\nlocal collections = db.collections()\n\n-- Access the `startup_log` collection\nlocal collection = db.collection(\"startup_log\")\n\nsession.close()", "language": "lua" } ] } [/block] Once you have access to a collection you can run queries against it using `find`. After executing the query you have access to the `count`, `distinct`, and `all` functions: [block:code] { "codes": [ { "code": "local mongo = require(\"telemetry/mongodb\")\nlocal session = mongo.open(\"mongodb://localhost:27017/local\")\nlocal db = session.db(\"local\")\nlocal collection = db.collection(\"startup_log\")\n\n-- Create a query\nlocal query = {}\nquery[\"cmdLine.net.bindIp\"] = \"127.0.0.1\"\n\nlocal skip = 0\nlocal limit = 10\n\n-- Execute the query\nlocal result = collection.find(query, skip, limit)\n\n-- Get a count on the results\nlocal count = result.count()\n\n-- Get results that have a distinct name\nlocal distinct = result.distinct(\"name\")\n\n-- Get the actual values\nlocal values = result.all()\n\nsession.close()", "language": "lua" } ] } [/block] # Executing commands You can use the `command` function of a database object to execute MongoDB commands against it. For example: [block:code] { "codes": [ { "code": "local mongo = require(\"telemetry/mongodb\")\nlocal session = mongo.open(\"mongodb://localhost:27017/local\")\nlocal db = session.db(\"admin\")\n\noutput.data = db.command({ ping = 1 })\nsession.close()", "language": "lua" } ] } [/block]