Sql Query receiver metrics ts_column not working

[devshashankb]

Hi,
for the receiver - https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver

I am trying to get metrics from the custom query, was able to get the metrics but unable to use ts_column as time stamp. i had to convert my timestamp in table to epoch, otherwise i get a data format exception when timestamp is passed.when i use the regular timestamp value

value was "2023-06-07T00:00:00Z": strconv.ParseInt: parsing "2023-06-07T00:00:00Z": invalid syntax; row 47: failed to parse uint64 for "time"
2023-08-01 21:17:21.6602174 +0000 UTC
strconv.ParseInt: parsing "2023-08-14T23:39:00Z": invalid syntax; row 122: failed to parse uint64 for "epoch", value was "2023-08-14T23:40:00Z"

I am converting to epoch with sql query as shown in the config file. but still i don't see the metric with timestamp value from the table. Here's the config file

sqlquery:
driver: sqlserver
datasource: ""
queries:
- sql: "select count_test,CAST(DATEDIFF(s,'1970-01-01 00:00:00',time) as varchar )as epoch from table name"
metrics:
- metric_name: app.count
value_column: "count_test"
attribute_columns: [ "count_test" ]
ts_column: "epoch"

here's the metric. i was expecting epoch value to be replaced with Timestamp. seems it's being appended Please help in resolving this issue
Metric #127
Descriptor:
-> Name: app.count
-> Description:
-> Unit:
-> DataType: Gauge
NumberDataPoints #0
Data point attributes:
-> count_test: Str(2)
StartTimestamp: 1970-01-01 00:00:00 +0000 UTC
Timestamp: 1970-01-01 00:00:01.68964272 +0000 UTC

Can you provide a sample example on how to pass timestamp metric from the query?

[andrzej-stencel]

You are right. The value of ts_column is currently limited to only accepting a decimal number, and you are correct to convert the original value of the time column (I assume it is of DATETIME type?) with DATEDIFF(s,'1970-01-01 00:00:00',time). What is missing is that the value of ts_column is interpreted as nanoseconds, and your expression returns seconds. The value must be multiplied by 10^9. Here's what has worked for me:

receivers:
  sqlquery:
    driver: sqlserver
      datasource: "sqlserver://SA:YourStrong@Passw0rd@localhost:1433?database=sqlquery"
        queries:
        - sql: "select count_test, 1000000000*CAST(DATEDIFF(s,'1970-01-01 00:00:00',time) as bigint) as epoch from table name"
          metrics:
          - metric_name: app.count
            value_column: "count_test"
            attribute_columns: [ "count_test" ]
            ts_column: "epoch"

Note that you need to cast the return value from DATEDIFF to a BIGINT for the multiplication by 10^9 to work.

[devshashankb]

Awesome!! It works!! Thank you!

[jayat66]

@astencel-sumo @devshashankb
I encountered a similar problem where I cannot match the time series on the column from the SQL query.
Here is my receiver configuration.
The Time series is showing the current time(Collector time) instead of the DiscoveryTime.
I tried both ts_column & start_ts_column based on the description here https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver

Any help is greatly appreciated!

sqlquery:
    driver: sqlserver
    datasource: "sqlserver://SA:YourStrong@Passw0rd@localhost:1433?database=sqlquery"
    queries:
        - sql: |-
          select
             SPID,
             DatabaseName,
             HostName,
             ProgramName,
             LoginName,
             DiscoveryTime,
             DiscoveryEndTime,
             1000000000*CAST(DATEDIFF(s,'1970-01-01 00:00:00',DiscoveryTime) as bigint) as epoch,
             count(*) as count_blocks
          FROM Performance.dbo.Blocker
          GROUP BY SPID,DatabaseName,HostName,ProgramName,LoginName,DiscoveryTime,DiscoveryEndTime
          ORDER BY DiscoveryEndTime desc
        metrics:
          - metric_name: db_blocking_query_count
            value_column: "count_blocks"
            attribute_columns: ["SPID", "DatabaseName", "HostName", "ProgramName", "LoginName", "DiscoveryTime", "DiscoveryEndTime", "epoch"]
            ts_column: "epoch"

[andrzej-stencel]

@devshashankb sorry for the late reply. I tested your config and it works for me ™️ 😉

Here's the setup I used - a bit simplified from yours, but it should not affect the result.

Here's the data I put in SQL Server:

create table Blocker (ProgramName nvarchar(100), DiscoveryTime datetime)
go
insert into Blocker values ("program1", "2024-01-01 01:01:01");
insert into Blocker values ("program2", "2024-02-02 02:02:02");
go
select * from Blocker
go
ProgramName                                                                          DiscoveryTime          
------------------------------------------------------------------------------------ -----------------------
program1                                                                             2024-01-01 01:01:01.000
program2                                                                             2024-02-02 02:02:02.000

Here's the Otelcol config:

exporters:
  debug:
    verbosity: detailed
receivers:
  sqlquery:
    driver: sqlserver
    datasource: "sqlserver://SA:YourStrong@Passw0rd@localhost:1433?database=master"
    queries:
        - sql: |-
            select
              ProgramName,
              DiscoveryTime,
              1000000000*CAST(DATEDIFF(s,'1970-01-01 00:00:00',DiscoveryTime) as bigint) as epoch,
              count(*) as count_blocks
            FROM master.dbo.Blocker
            GROUP BY ProgramName,DiscoveryTime
            ORDER BY DiscoveryTime desc
          metrics:
          - metric_name: db_blocking_query_count
            value_column: "count_blocks"
            attribute_columns: ["ProgramName", "DiscoveryTime", "epoch"]
            ts_column: "epoch"
service:
  pipelines:
    metrics:
      exporters:
        - debug
      receivers:
        - sqlquery

And here's the output from the Debug exporter:

2024-03-21T20:36:21.797+0100    info    MetricsExporter {"kind": "exporter", "data_type": "metrics", "name": "debug", "resource metrics": 1, "metrics": 2, "data points": 2}
2024-03-21T20:36:21.797+0100    info    ResourceMetrics #0
Resource SchemaURL: 
ScopeMetrics #0
ScopeMetrics SchemaURL: 
InstrumentationScope  
Metric #0
Descriptor:
     -> Name: db_blocking_query_count
     -> Description: 
     -> Unit: 
     -> DataType: Gauge
NumberDataPoints #0
Data point attributes:
     -> ProgramName: Str(program2)
     -> DiscoveryTime: Str(2024-02-02T02:02:02Z)
     -> epoch: Str(1706839322000000000)
StartTimestamp: 1970-01-01 00:00:00 +0000 UTC
Timestamp: 2024-02-02 02:02:02 +0000 UTC
Value: 1
Metric #1
Descriptor:
     -> Name: db_blocking_query_count
     -> Description: 
     -> Unit: 
     -> DataType: Gauge
NumberDataPoints #0
Data point attributes:
     -> ProgramName: Str(program1)
     -> DiscoveryTime: Str(2024-01-01T01:01:01Z)
     -> epoch: Str(1704070861000000000)
StartTimestamp: 1970-01-01 00:00:00 +0000 UTC
Timestamp: 2024-01-01 01:01:01 +0000 UTC
Value: 1
        {"kind": "exporter", "data_type": "metrics", "name": "debug"}

As you can see in the output, the timestamps for both data points are correctly set to 2024-01-01 and 2024-02-02:

     -> ProgramName: Str(program2)
Timestamp: 2024-02-02 02:02:02 +0000 UTC
     -> ProgramName: Str(program1)
Timestamp: 2024-01-01 01:01:01 +0000 UTC

Is this not the result you were expecting?