Sql.yaml file format to enable load data

Grafana Dashboards
1

Hi, I have created the below yaml file. The sql brings back three columns ORDER_DATE, Label and STARTTIME (seconds as an integer), yet when it pulls the data through on Grafana it brings it row in as a record. It also seem to populate a date using the time the data is pulled through. Does anyone know what adjustment I need to make please?
collector_name: mssql_standard

metrics:

  • metric_name: mssql_insight_cdi_mssql_nt_file_delivery_timesII
    type: gauge
    help: ‘NT file delivery timesII.’
    value_label: ‘operation’
    key_labels:
    • ORDER_DATE
    • Label
      values: [STARTTIME]
      query_ref: cdi_mssql_nt_file_delivery_timesII

queries:

Database reads and writes

  • query_name: cdi_mssql_nt_file_delivery_timesII
    query: |
    Select t.Date as “ORDER_DATE”, t.FileName + ‘: FTP Arrived’ as “Label”, CASE WHEN t.STARTTIME >= 82800 then 86400 - t.STARTTIME else t.STARTTIME END as STARTTIME from (select ae.FileName, format(cast(aeh.EventTS as datetime),‘yyyy-MM-dd’) as Date, min((datepart(hour,aeh.EventTS) * 3600)+(datepart(minute,aeh.EventTS)* 60)+(datepart(second,aeh.EventTS))) as “STARTTIME” from [prdlogging].[dbo].[T_AsyncEventHistory] aeh (nolock) inner join (Select ae.EventID, LEFT( RIGHT(ae.Description, CHARINDEX(‘', REVERSE(ae.Description),CHARINDEX(’', REVERSE(ae.Description))+1) - 1), charindex(‘.’,RIGHT(ae.Description, CHARINDEX(‘', REVERSE(ae.Description),CHARINDEX(’', REVERSE(ae.Description))+1) - 1))-1) as FileName, ae.NotificationToken from [prdlogging].[dbo].[T_AsyncEvent] ae (nolock) inner join [prdlogging].[dbo].[T_AsyncEventType] aet (nolock) on ae.EventTypeID = aet.EventTypeID inner join [prdlogging].[dbo].[T_RouterSourceFiles] t (nolock) on RIGHT(LEFT(ae.Description, LEN(ae.Description)- CHARINDEX(‘', REVERSE(ae.Description))),LEn(LEFT(ae.Description, LEN(ae.Description)- CHARINDEX(’', REVERSE(ae.Description))))-1) = t.SourceFolder where aet.SystemID = 1006 AND charindex(‘.’,ae.Description) > 0 AND LEFT(t.SourceFileID,3) in (‘C81’)) ae on aeh.EventID = ae.EventID WHERE aeh.EventTS > ‘20221231’ group by ae.FileName, format(cast(aeh.EventTS as datetime),‘yyyy-MM-dd’) ) t where t.FileName not like ‘%LATE’ AND t.FileName not like ‘%Yesterday’ AND t.FileName not like '%Delta’AND t.FileName not like ‘%Daily’ AND t.FileName not like ‘%Intraday%’