-
Report / SQL element / Date of selection
Hello,
I’ve created a report using a SQL element. I want to know how I can integrate the “date filter” in the Report view in this request ?
Is-there a special Tag to put in the SQL request to be able to use the date filter ?
In the following request, I want to change the line starting by “and FROM_UNIXTIME…” to use a “BETWEEN” using the filter dates.
eg :
select a.alias agent_name, FROM_UNIXTIME(min(c.utimestamp)) DateMin_Compil,FROM_UNIXTIME(max(c.utimestamp)) DateMax_Compil, count(*) occurence
from tagente_datos c, tagente_modulo b, tagente a
where
b.id_agente_modulo = c.id_agente_modulo
and a.id_agente = b.id_agente
and (b.nombre like “Resultat compil%”)
and a.nombre like “Compil.%”
and FROM_UNIXTIME(c.utimestamp) >= DATE_SUB(CURRENT_DATE(),INTERVAL 7 DAY)
and datos <> 2
group by c.id_agente_modulo, b.nombre,a.alias
order by occurence desc, alias;
-
7 Replies
-
Hi,
The only thing we have restricted in Reports is the use of the asterisk (*).
From there you can actually execute any MySQL query you want as long as its syntaxis is correct.
You can use the DB Interface in the console at the “Admin Tools” section where you can try the query and see if it executes and gives you the result needed.
Then you can simply configure that query in your MySQL report and get the results needed.
Kind regards,
Sergio B.
-
Thanks for your reply? I’ve alreayd done that, and it’s working fine.
My question is how I can use the date filter – as SLA component use – in a SQL query ?
Is there any “tags/keywords” to do thing like that :
select id, date from t where date between (%filter_start%, %filter_end%);
My goal is to use the filter to not have to include it hardly in the query, as I’m making a SQL request for a third part, that doesn’t know how to do SQL at all.
Thanks for answer.
Christophe
-
-
Hello,
Pandora FMS inserts data through timestamp. In order to filter by date in the DB you will need to convert the date into timestamp.
Here’s a website that explains the procedure:
https://www.tutorialspoint.com/how-to-convert-mysql-datetime-to-unix-timestamp#
With this, you will be able to filter by timestap in your query.
Kind regards,
Sergio B.
-
Hello Sergio,
There is no problem of conversion. I just want to use the Dates filter (Begin and end) of the interface – as in the previous screen capture – in a SQL Query reports. The goal is to get data of a module only “inside” this interface date selection.
Can you give me example, please of that ?
Regards
Christophe
-
-
Hello,
Happy to see that what I’m asking seems to be added in the last release 765 RRR !
-
