Trouble Loading All Data When Variable is Empty in SQL Query

Grafana
1

Hi, I am trying to create a Grafana dashboard that filters data based on a user ID. The dashboard should display data for all users when no user ID is provided, and filter by user ID when one is specified. When I leave the user ID variable empty, the dashboard does not load any data instead of showing data for all users. However, it works fine when I enter a specific user ID.

I have tried adjusting the SQL query to handle null or empty values using various methods like checking for an empty string or null values directly in the query. The dashboard fails to load any data when the variable is left empty, despite expecting it to show all records.

here is my query to create the panel:

SELECT
  DATE_TRUNC('day', created_at) AS "time",
  COUNT(*) FILTER (WHERE status IN ('completed', 'running', 'termination requested', 'destroyed')) AS "Initiated Account",
  COUNT(*) FILTER (WHERE status IN ('completed', 'running')) AS "Successfully Created Account"
FROM users
WHERE
  $__timeFilter(created_at) 
  AND (
    '${userID:text}' = '' OR 
    user_id = '${userID:text}'
  )
GROUP BY 1
ORDER BY 1;

Does anyone have suggestions on how to modify the SQL query to handle an empty input properly?

2

what does the query inspector show when you select All?

Another thing you can you try. Make the All value default to -1
image

SELECT
  DATE_TRUNC('day', created_at) AS "time",
  COUNT(*) FILTER (WHERE status IN ('completed', 'running', 'termination requested', 'destroyed')) AS "Initiated Account",
  COUNT(*) FILTER (WHERE status IN ('completed', 'running')) AS "Successfully Created Account"
FROM users
WHERE
  $__timeFilter(created_at) 
  AND (
    '${userID:text}' = '-1' OR user_id = '${userID:text}'
  )
GROUP BY 1
ORDER BY 1;
3

@yosiasz in variable type what I need to select?

4

image
image1135×663 52.5 KB

what is variable type

5

While creating the variable in settings

image
image862×650 50.3 KB

What should be the Select Variable type

6

depends where you are bringing your data for your variables from.

7

I am brining the data from postgres table

8

When I select all it doesn’t load anything. And your method is also not working

9

it works on my pc :laughing: what do you see in Query Inspector when you select All

10

I got this

image
image1650×352 36.6 KB

11

@yosiasz This one I resolved with the following logic

AND (
    '${userID:text}' = '' OR
    user_id = CAST(NULLIF('${userID:text}', '') AS uuid)
  )

And now I have one more query if I want to filter for a single day or any specific date then how can we do that?

2 Likes
12

that looks redudant code

you check for empty string then again for null?

1 Like
13

Here is the documentation, if somebody needs it: