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?
what does the query inspector show when you select All?
Another thing you can you try. Make the All value default to -1
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;
@yosiasz in variable type what I need to select?
While creating the variable in settings
What should be the Select Variable type
depends where you are bringing your data for your variables from.
I am brining the data from postgres table
When I select all it doesn’t load anything. And your method is also not working
it works on my pc what do you see in Query Inspector when you select All
@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
that looks redudant code
you check for empty string then again for null?
1 Like
Here is the documentation, if somebody needs it: