Let user re-use a query and change the WHERE clause as required

Grafana Dashboards
1

Hi,
I’m still on Grafana 9.5.2, waiting on my colleague to update the Docker container. Anyway, I hope my requirement is that ‘basic’ that the version doesn’t matter. If it is even possible, what I have in mind.

I’m bit of a noob in Grafana.
I have a quite simple query to get SUM values (INT), a date and a Site (TEXT) from a MSSQL database. The query in general is working.

SELECT $__timeGroupAlias(Date,$__interval), SUM(Capacity) FROM FileServices.dbo.HomeDrive WHERE $__timeFilter(Date) AND Site = 'ABC' GROUP BY Date ORDER BY Date ASC

Is it possible in any way that a user can adjust the value for ‘Site’ from the Dashboard view? Like he does with the time picker. That way he could get the values filtered based on the site he is entering. For site ABC or CDE or FOO or BAR - you’ll get the point.

Additionally: Is there any way to (temporary) duplicate that query during runtime (from the Dashboard view!) and that way have two, three, four queries and change the WHERE value for each instance?

Unfortunately it is no option to have the user access the edit view and simply duplicate the query there.

2

can you use a dashboard variable for this?

2 Likes
3

Welcome

Create a variable calles sites and query a list of site names

Then in query use whatever user selected

where site in ('${sites:doublequote}')

Kind of thing

2 Likes
4

Thank you! The first part is working, I actually never worked with variables in Grafana before. So I have my list of sites now in the Dashboard Settings-Variables section. Good.

From your answer and the docu I thought I need to implement it this way:
SELECT $__timeGroupAlias(Date,$__interval), SUM(Capacity) FROM FileServices.dbo.HomeDrive WHERE $__timeFilter(Date) AND Site in ('${sites:doublequote}') GROUP BY Date ORDER BY Date ASC

But now the Edit view as well as the “normal” view is showing “No Data”. Where am I supposed to find the Sites-selection? Or what do I miss in general to make the Site-selection now?

1 Like
5

Ok, after deleting my browser cache I can see the selection field now. Unfortunately still showing “No data”…
When I simply remove the AND Site in [...] my data is visible.

6

can you share what your query inspector looks like?

1 Like
7

Thanks for your reply!

You mean that?

{
  "request": {
    "url": "api/ds/query",
    "method": "POST",
    "data": {
      "queries": [
        {
          "refId": "FOO",
          "datasource": {
            "type": "mssql",
            "uid": "e4d933ab-5a8d-487f-8d34-d54e43dc53ca"
          },
          "rawSql": "SELECT $__timeGroupAlias(Date,1d), SUM(Capacity) FROM FileServices.dbo.HomeDrive WHERE $__timeFilter(Date) AND Site in ('\"ABC\",\"CDE\",\"FOO\",\"BAR"') GROUP BY Date ORDER BY Date ASC",
          "format": "time_series",
          "datasourceId": 2,
          "intervalMs": 86400000,
          "maxDataPoints": 1280
        }
      ],
      "range": {
        "from": "2023-12-20T07:29:41.205Z",
        "to": "2024-01-19T07:29:41.205Z",
        "raw": {
          "from": "now-30d",
          "to": "now"
        }
      },
      "from": "1703057381205",
      "to": "1705649381205"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "HDH": {
        "status": 200,
        "frames": [],
        "refId": "FOO"
      }
    }
  }
}
8

Any idea why I get “no data” as soon as the selectable variable is in?