# Read metrics from SQL queries
[[inputs.sql]]
  ## Database Driver
  ## See https://github.com/influxdata/telegraf/blob/master/docs/SQL_DRIVERS_INPUT.md for
  ## a list of supported drivers.
  driver = "mysql"

  ## Data source name for connecting
  ## The syntax and supported options depends on selected driver.
  dsn = "username:password@tcp(mysqlserver:3307)/dbname?param=value"

  ## Timeout for any operation
  ## Note that the timeout for queries is per query not per gather.
  # timeout = "5s"

  ## Connection time limits
  ## By default the maximum idle time and maximum lifetime of a connection is unlimited.
  ## Connections will not be closed automatically. If you specify a positive time, the connections will be closed after
  ## idling or existing for at least that amount of time, respectively.
  # connection_max_idle_time = "0s"
  # connection_max_life_time = "0s"

  ## Connection count limits
  ## By default the number of open connections is not limited and the number of maximum idle connections
  ## will be inferred from the number of queries specified. If you specify a positive number for any of the
  ## two options, connections will be closed when reaching the specified limit. The number of idle connections
  ## will be clipped to the maximum number of connections limit if any.
  # connection_max_open = 0
  # connection_max_idle = auto

  ## Specifies plugin behavior regarding disconnected servers
  ## Available choices :
  ##   - error: telegraf will return an error on startup if one the servers is unreachable
  ##   - ignore: telegraf will ignore unreachable servers on both startup and gather
  # disconnected_servers_behavior = "error"

  [[inputs.sql.query]]
    ## Query to perform on the server
    query="SELECT user,state,latency,score FROM Scoreboard WHERE application > 0"
    ## Alternatively to specifying the query directly you can select a file here containing the SQL query.
    ## Only one of 'query' and 'query_script' can be specified!
    # query_script = "/path/to/sql/script.sql"

    ## Name of the measurement
    ## In case both measurement and 'measurement_col' are given, the latter takes precedence.
    # measurement = "sql"

    ## Column name containing the name of the measurement
    ## If given, this will take precedence over the 'measurement' setting. In case a query result
    ## does not contain the specified column, we fall-back to the 'measurement' setting.
    # measurement_column = ""

    ## Column name containing the time of the measurement
    ## If omitted, the time of the query will be used.
    # time_column = ""

    ## Format of the time contained in 'time_col'
    ## The time must be 'unix', 'unix_ms', 'unix_us', 'unix_ns', or a golang time format.
    ## See https://golang.ac.cn/pkg/time/#Time.Format for details.
    # time_format = "unix"

    ## Column names containing tags
    ## An empty include list will reject all columns and an empty exclude list will not exclude any column.
    ## I.e. by default no columns will be returned as tag and the tags are empty.
    # tag_columns_include = []
    # tag_columns_exclude = []

    ## Column names containing fields (explicit types)
    ## Convert the given columns to the corresponding type. Explicit type conversions take precedence over
    ## the automatic (driver-based) conversion below.
    ## NOTE: Columns should not be specified for multiple types or the resulting type is undefined.
    # field_columns_float = []
    # field_columns_int = []
    # field_columns_uint = []
    # field_columns_bool = []
    # field_columns_string = []

    ## Column names containing fields (automatic types)
    ## An empty include list is equivalent to '[*]' and all returned columns will be accepted. An empty
    ## exclude list will not exclude any column. I.e. by default all columns will be returned as fields.
    ## NOTE: We rely on the database driver to perform automatic datatype conversion.
    # field_columns_include = []
    # field_columns_exclude = []

[[inputs.sql]]
  driver = "mysql"
  dsn = "root:password@/nation"

  [[inputs.sql.query]]
    query="SELECT * FROM guests"
    measurement = "nation"
    tag_columns_include = ["name"]
    field_columns_exclude = ["name"]

nation,host=Hugin,name=John guest_id=1i 1611332164000000000
nation,host=Hugin,name=Jane guest_id=2i 1611332164000000000
nation,host=Hugin,name=Jean guest_id=3i 1611332164000000000
nation,host=Hugin,name=Storm guest_id=4i 1611332164000000000
nation,host=Hugin,name=Beast guest_id=5i 1611332164000000000