[inputs.exec] Error in plugin: metric parse error

1

Hi guys,
I need your help.

I Have 2 Problems:

1. My Main Problem:
I want to monitor my SQL Server backups. For this purpose I have written a Powershell script. The Powershell script gets the information when the backups were created and how long the backups took.

Telegraf does not seem to get along with the date field “start” and “end”. I get the error message:

2020-11-18T10:42:31Z E! [inputs.exec] Error in plugin: metric parse error: expected field at 1:64: “Backup,backup_type=Log,db_server=HOSTNAME\\INSTANCENAME start=11/18/2020 01:00:54,end=11/18/2020 01:00:54,BackupSize=746496,db_child="DB_NAME"”

The date is structured as follows:
2020-11-18 01:00:00

do you have an idea what I am doing wrong?

Enclosed is the Powershell script that fetches the data from the SQL Server:

$Datenquelle = "HOSTNAME\INSTANCENAME"
$Datenbank = "master"

$connectionString = "Server=$Datenquelle; " + "Integrated Security=SSPI; " + "Database=$Datenbank"

$Verbindung = New-Object System.Data.SqlClient.SqlConnection

$Verbindung.ConnectionString = $connectionString

$Verbindung.Open()

$Query = "
USE MSDB
 
SELECT
 bs.server_name AS db_server, -- Server name
 bs.database_name AS db_child, -- Database name
 CASE bs.compatibility_level 
   WHEN 110 THEN 'SQL Server 2012'
   WHEN 120 THEN 'SQL Server 2014'
   WHEN 130 THEN 'SQL Server 2016'
   WHEN 140 THEN 'SQL Server 2017'
   WHEN 150 THEN 'SQL Server 2019'
 END AS Compability , -- Return backup compatibility level
 CASE bs.type 
   WHEN 'D' THEN 'Full'
   WHEN 'I' THEN 'Differential'
   WHEN 'L' THEN 'Log'
   WHEN 'F' THEN 'File or filegroup'
   WHEN 'G' THEN 'Differential file'
   WHEN 'P' THEN 'Partial'
   WHEN 'Q' THEN 'Differential partial'
 END AS backup_type, -- Type of database backup
 bs.backup_start_date AS 'start', -- Backup start Datum
 bs.backup_finish_date AS 'end',  -- Backup abgeschlossen Datum
 bs.backup_size AS  [BackupSize]  -- Normal backup size (In bytes)
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
 ON (bs.media_set_id=bmf.media_set_id)
 WHERE bs.backup_finish_date >= DATEADD(MI, -1440, GetDate())
ORDER BY bs.backup_start_date DESC
"
$Befehl = $Verbindung.CreateCommand()

$Befehl.CommandText = $Query

$Resultat = $Befehl.ExecuteReader()

$Backups = new-object System.Data.DataTable

$Backups.Load($Resultat)

$Verbindung.Close()

$Measurement = "Backup"
$TagList = "db_server","backup_type"
$MetricList = "db_child","BackupSize","start","end"
$Backups | ConvertTo-Metric -Measure $Measurement -MetricProperty $MetricList -TagProperty $TagList | ConvertTo-InfluxLineString -ExcludeEmptyMetric -WarningAction SilentlyContinue

2. My Second (Design Problem):
My design problem: Telegraf sets the SQL server name, which is structured as follows:
HOSTNAME\SQL_INSTANCENAME
makes 4x a backslash. Why?

2

Hello @dcst55,
Right now you don’t have a timestamp in you line protocol, so it will attach a timestamp at the point of write.
If you want to include a timestamp in your line protocol it must look like:

myMeasurement,tag1=value1,tag2=value2 fieldKey="fieldValue" 1556813561098000000

However, you can include those timestamps as tags in a string as well and use toTime() to convert them to timestamps in Flux.

However, please note that again influxDB assumes all numeric input values are nanosecond epoch timestamps.