Render query result with 1st column as datetime as is

[brunogonzalez]

Hi,

I'm trying to set up custom queries with Graphana Alloy and SQL Exporter. I've managed to do it except for one thing: my result set always has the first column as a datetime at the time the event I monitored happened, then all the other columns are data. Example below with only one data column and the date time of the event:

This database is where I store things

When I push the same query as SSMS in the config file:
` - metric_name: sessions
type: gauge
help: 'Sessions'
values: [Sessions]
query: |
SELECT [DateTime UTC] as datetimeutc
,[Sessions] as Sessions
FROM [MYDB].[dbo].MYTABLE]

It is processed but in Graphana dashboard in a time series I see that he chose to take the prometheus scape date or whatever instead of the column date.

I'm not sure how to tell it to just render the query content. I'm originally using Graphana OSS with local connection to the DB server and I get the behaviour that I want:

I've tried to change things in the config file like

key_labels: - datetimeutc
or
value_label: Sessions values: [datetimeutc, Sessions]
but I can't seem to figure it out.

Any hints would appreciated.

Thank You

[burningalchemist]

Hey @brunogonzalez, let me try to reproduce it, and I'll get back to you.

Quick question - are you using Grafana Alloy and sql_exporter as separate components or built-in functionality (Grafana Alloy uses some code from sql_exporter so it's partially embedded, but only for Microsoft SQL)?

Also, if you could check the raw output from the /metrics endpoint? Is the output somewhat suspicious?

I have a feeling it's related to Grafana rendering the results, but happy to look into that. 👍

Cheers!

[burningalchemist]

Some thoughts:

• You might want to use transformations in Grafana as datetimeutc is just a label, so it's not processed as a timestamp in Grafana (and maybe that's the reason of a broken chart);

• You also might want to try timestamp_value parameter as in the example:

# Timestamp value (optional). Should point at the existing column containing valid timestamps to return a metric with an explicit timestamp.
timestamp_value: CreatedAt

This feature is available in sql_exporter since v0.14, but if you're using the embedded exporter from Alloy, I don't know if they updated the config file in Grafana Alloy (they never reached out to me, so I don't know the current status of this integration).

This parameter should reuse the datetimeutc column as an actual timestamp per value (sessions) instead of a scrape timestamp (see Prometheus metric format).

In the worst case, you can deploy sql_exporter as a separate exporter (it's small), and scrape the results from Alloy.

Give it a shot, and let me know. 🙂

[brunogonzalez]

Hi @burningalchemist
I'm using the sql_exporter built in Alloy indeed.
I didn't see anything suspicious in the /metrics. I can paste it if you want as I've cleaned it up to remove sensitive information.

I've thought about the timestamp_value parameter. I'll retry because it was crashing Alloy but I was playing a lot with multiple values so 😊
If Alloy crashes, then i'll install things separately as recommended. I'm in touch with Graphana, so I'll try to slip in a word about the embedded version and ways to know about it.

I'll check on Graphana's side as well for the transformation, thanks for the hints. I'll keep you posted.

[burningalchemist]

If it crashes - then I guess they might be using the old version which doesn't have this feature/parameter implemented. But anyways.

Also, if you could let me know what's your desired result, I can figure out what might be needed to achieve the goal. 🙂

Yeah, let me know. I'm happy to help, with the standalone app it's easier. 👍