Exclude field in sql server query from appearing in the legend

Hi, I have a T-sql server that returns 4 fields, grafana include all fields in the legend, how can indicate to grafana that I don’t need to display one of the fields in the legend (the url field in the following query).

SELECT
 [Timestamp] as time,
	(Download / (1024 * 1024)) as value,
	City as metric,
	url
FROM [BaselineDB].[dbo].[speedtest]
where url is not null
ORDER BY [Timestamp] ASC

Have you tried using the Organize Fields transformation? This will allow you to hide a field from view:

mattagrams, I am not sure this help in my situation, I need to hide/remove 1field from the legend, not all. Clicking the eye icon next to the field name will hide all the fields
As you can notice in the snapshot, I need to hide the url that appears in the legend

Might be misunderstanding but would not including the URL Field work?

SELECT
       [Timestamp]                as time
	  ,(Download / (1024 * 1024)) as value
	  ,City                       as metric
      --,url
FROM [BaselineDB].[dbo].[speedtest]
where url is not null
ORDER BY [Timestamp] ASC

Then having a Table Panel beside it with the same Query as below

SELECT
       [Timestamp]                as time
	  ,(Download / (1024 * 1024)) as value
	  ,City                       as metric
      ,url
FROM [BaselineDB].[dbo].[speedtest]
where url is not null
ORDER BY [Timestamp] DESC

To give you a graph for the download speed grouped by City on the Graph and a URL in the Table

1 Like

Thanks good idea. OK, let me ask you now about something that I have already asked in another thread but nobody responded.
Yes, I get a table, can I make the 3rd filed , the url item in each cell clickable? I would like to click the link so I can see the image

Think this could help hopefully?

1 Like

Yes, correct, it helped and now urls are clickable. However, where does the value of
${global_variable_name} come from?
2nd, when I did chose the url field, then added the data link, I had only 2 elements to populate not 3 as indicated in the thread

Think you’d just leave that Title field blank and that should be you done

Just realised that this could be used with a

https:// Grafana:3000 /fo/bar/${__value.text}

Left spaces not to have a link, but if you had a search for a field that was enterable via a fixed URL with the field added at the end, this could probably work

OK, got it, so many thanks for your help

1 Like

Grumpy, I had also opened a thread long time ago for which I did not get any response. My Grafana always displays data/graphs lagging 1 hour. My timezone is configured to be Paris/European central. For example, at 3 PM, the graph displays data until 2 PM so I dont see data between 2-3 PM until it is 4 PM

I had the same problem and helped others with a messy/clunky solution I’ve done

But since then you can set Default timezone of the page but it all stems from not using data type DateTimeOffset to tell Grafana to adjust away from UTC, Grafana always assume the data to be UTC

Grumpy, this is a good idea when we have 1 or couple of simple dashboards but it can be a little bit messy when having several dashboards with complicated T-SQL :slight_smile:
There should be a more robust solution. One thing I cant understand, Grafana allows configuring globally Timezone and at dashboard levels but it does not care about it, so what is the idea behind this? Not every server and client is under UTC. Also, it is indicated in the doc that Grafana uses the web browser local time which again not a true statement

1 Like

In fact I have just noticed that the Fedora server where grafan runs has its clock correct at 11AM but the speedtest client that writes to the text file every 5 minutes is lagging by 1 hour. I will check with the creator of speedtest-cli

Ok, check that but if not then you’ll have to do this, or debug SQL Server time, or Change the input datatype to DateTimeOffset (datetimeoffset (Transact-SQL) - SQL Server | Microsoft Docs)

DECLARE  @offset int        = datediff( HOUR, GETDATE(), GETUTCDATE() )
--SELECT   @offset, GETDATE(), GETUTCDATE() -- Debug values returned by server

SELECT   DATEADD( HOUR, @Offset, [Timestamp] ) as time  -- offsets the time to match the graph
	    ,( [Download] / (1024 * 1024) )        as value
	    ,[City]                                as metric
FROM     [BaselineDB].[dbo].[speedtest]

WHERE    [url]       IS NOT NULL
AND      [Timestamp] BETWEEN dateadd(hour, -@offset, $__timeFrom() ) 
                     AND     dateadd(hour, -@offset, $__timeTo()   ) -- Offsets the filter to match the graph
ORDER BY [Timestamp] ASC

I updated the python script to write local time instead of UTC, still grafana displays data 1 hour earlier. I will go through your recommendation and let you know

I update the T-SQL as suggested, it is now 10:31 still when I use query inspector, I see
timeto = 9:31 and not 10:31

graph is up to 9:31

whereas in SQL database I have 5 records after 9:31

sql_932

Hi Grumpy,
I replaced
SELECT DATEADD( HOUR, @Offset, [Timestamp] ) as time
By
SELECT $__timeEpoch([Timestamp] )
and graph was updated correctly
but really I don’t know why? what this $__timeEpoch do?

Probably fixes the issue through Unix Timestamps, bad to 1970-01-01 + your time difference + your timestamp