Timezone Not Inlcuded in Backup in SQL Server input

[Grosoiu]

Relevant telegraf.conf

[[inputs.system]]

[[inputs.mem]]

[[inputs.cpu]]

[[inputs.diskio]]

[[inputs.win_perf_counters]]
  [[inputs.win_perf_counters.object]]
    ObjectName = "Network Interface"
    Instances = ["*"]
    Counters = [
      "Bytes Received/sec",
      "Bytes Sent/sec",
      "Packets Received/sec",
      "Packets Sent/sec",
      "Packets Received Errors",
      "Packets Outbound Errors",
    ]
    Measurement = "win_net"

[[inputs.disk]]
  ## Percentage of the Disk in VM.
  ignore_fs = ["tmpfs", "devtmpfs", "devfs", "iso9660", "overlay", "aufs", "squashfs"]

[[inputs.win_services]]
  ## Names of the services to monitor. Leave empty to monitor all the available services on the host. Globs accepted. Case sensitive.
  service_names = [
    "BES*", # bigfix client
    "CSF*",
        ]
  excluded_service_names = ['WinRM'] # optional, list of service names to exclude

[[inputs.win_perf_counters]]
  [[inputs.win_perf_counters.object]]
    ObjectName = "Process"
    Counters = [
      "% Processor Time",
      "Handle Count",
      "Private Bytes",
      "Thread Count",
      "Virtual Bytes",
      "Working Set",
    ]
    Instances = ["BES*"]
    Measurement = "win_proc"

[[inputs.win_perf_counters]]
  [[inputs.win_perf_counters.object]]
    ObjectName = "Process"
    Counters = [
      "% Processor Time",
      "Handle Count",
      "Private Bytes",
      "Thread Count",
      "Virtual Bytes",
      "Working Set",
    ]
    Instances = ["CSF*"]
    Measurement = "win_proc"

[[inputs.sql]]
  ## This input is for running custom queries against the server at a certain interval.
  driver = "mssql"

    interval = "1h"
    dsn = "odbc:server=w01u13801064001;Port=1433;database=master;Integrated Security=SSPI"
    disconnected_servers_behavior = "ignore"

  [[inputs.sql.query]]

    query="CREATE TABLE #ErrorLog(LogDate DATETIME,ProcessInfo NVARCHAR(128),Text NVARCHAR(MAX));INSERT INTO #ErrorLog EXEC xp_readerrorlog 0, 1, N'Error';DECLARE @RowCount INT;SELECT @RowCount = COUNT(*) FROM #ErrorLog;DECLARE @ServerName NVARCHAR(128);SET @ServerName = @@servername;SELECT @RowCount AS errorcount, @ServerName AS servername;DROP TABLE #ErrorLog;"
    measurement = "SQL_ErrorLog"
    tag_columns_include = ["servername","errorcount"]

[[inputs.sql]]
  ## This input is for running custom queries against the server.
  driver = "mssql"

    dsn = "odbc:server=w01u13801064001;Port=1433;database=master;Integrated Security=SSPI"
    disconnected_servers_behavior = "ignore"

  [[inputs.sql.query]]

    query="select REPLACE(@@servername,'\\',':') as servername, ER.session_id, ER.status as status, ER.start_time as logintime, ES.login_name as loginname, ES.host_name as hostname,ES.program_name as programname, DB_NAME(ER.database_id)as databasename, ER.cpu_time as CPUtime, ES.memory_usage as memory, ER.reads as IOReads, ER.writes as IOWrites, ER.wait_type as waittype, ER.wait_time as waitime, ER.wait_resource, EC.protocol_type, ER.blocking_session_id as blk_by, ER.command as CMD, ER.open_transaction_count, ER.statement_sql_handle,CAST(ER.sql_handle as varchar(max)) as sqlhandle from sys.dm_exec_sessions ES left join sys.dm_exec_connections EC on ES.session_id = EC.session_id left join sys.dm_exec_requests ER on ES.session_id = ER.session_id where ER.session_id is not NULL"
    measurement = "mssql_sessions"
    tag_columns_include = ["session_id","servername","status","loginname","hostname","programname","databasename","waittype","protocol_type","CMD","sql_handle"]

  [[inputs.sql.query]]

    query="IF OBJECT_ID('tempdb..#spacetable') IS NOT NULL DROP TABLE tempdb..#spacetable; CREATE TABLE #spacetable(database_name VARCHAR(50),percent_data_used FLOAT);INSERT INTO #spacetable EXECUTE master.sys.sp_MSforeachdb 'USE [?];SELECT DB_NAME() AS ''DATABASE NAME'',CASE WHEN SUM(size*8/1024) = 0 THEN ''divide by zero'' ELSE SUBSTRING(CAST((SUM(FILEPROPERTY(name, ''SpaceUsed''))*1.0*100/SUM(size)) AS CHAR(50)), 1, 6) END AS ''percent fill'' FROM sys.master_files WHERE database_id = DB_ID(DB_NAME()) AND type = 0 GROUP BY type_desc';SELECT REPLACE(@@servername,'\\',':') as servername, database_name, percent_data_used FROM #spacetable ORDER BY database_name; DROP TABLE #spacetable"
    measurement = "SQLDB_Percentage"
    tag_columns_include = ["servername","database_name"]

  [[inputs.sql.query]]

    query="EXEC msdb.dbo.sp_help_job"
    measurement = "agent_xp"
    tag_columns_include = ["originating_server","name","category"]

[[inputs.win_services]]

#Add the name of the services that need to be monitored on the VM
  service_names = [
      "MSSQLSERVER",
      "ClusSvc",
      "SQLServerAgent"
  ]
  [inputs.win_services.tags]
    server_type_name = "AG Failover MSSQL Server"
    listener_name = "w01u13801064lsr"

[[inputs.sqlserver]]

#VERY IM1433ANT, if a server has the character '\' in the servername, you'll have to put '\\' instead because TOML ignores one of them.
  servers = [

      "Server=w01u13801064001;Port=1433;database=master;Integrated Security=SSPI;log=1;",
  ]

  database_type = "SQLServer"

  include_query = []
  ## Remove this on AG configured servers.
  exclude_query = ["SQLServerAvailabilityReplicaStates", "SQLServerDatabaseReplicaStates"]

[inputs.sqlserver.tags]
  listener_name = "w01u13801064lsr"

[global_tags]
  LOB = "Payments"
  PRODUCT = "FGPP Parc"
  Service_name = "w01u13801064001 SQL Server Database"
  ENV = "UAT"
  Data_center = "Azure East US"
  Assignment_Group = "AppOps DB SQL"
  Assignment_Group_ID = "99c276151b2f1494987474c51a4bcb1c"

[agent]
  interval = "60s"
  collection_jitter = "0s"

[[outputs.http]]

  url = "https://mimir-dev.clm.provides.io:443/api/v1/push"

  data_format = "prometheusremotewrite"

  insecure_skip_verify = true

  [outputs.http.headers]
     Content-Type = "application/x-protobuf"
     Content-Encoding = "snappy"
     X-Prometheus-Remote-Write-Version = "0.1.0"
     X-Scope-OrgID = "telegraf"

Logs from Telegraf

There are no errors present in the debug mode, as the agent does not consider it a bug.

System info

Telegraf 1.30.1

Docker

No response

Steps to reproduce

1.Run the telegraf inputs.sqlserver on an SQL Server that has a timezone different than utc

2. Check that the value sent as EPOCH is wrong, as it doesn t have the timezone delay added/removed.

Expected behavior

It should take the timezone delay into account. The command that you are using in the sql server for getting the time of the last backup does not take timezone into account, so if it says 6PM, it doesn t know if it s 6PM UTC or 6PM mountain time.

Actual behavior

IT should substract the Timezone delay.

Additional info

We have found a solution, I will create a pull request shortly.

[powersj]

next steps: get reivews on 15154, thanks for the PR!

[powersj]

Fixed with #15154