Pandora FMS Community

Report / SQL element / Date of selection

Christophe · 27 jul 2022, 10:10 · 8 mensajes · 119 visitas

  1. Inicio del debate

    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;

  2. 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.

  3. 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

    Filter-SQL-Report.jpg12 KB · JPG
    Filter-SQL-Report.jpg
  4. 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.

  5. 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

  6. Hello,

    Support team is not able to perform personalized SQL Queries.

    What we can provide is the name of the tables that you need to get data from.

    Here are the tables you need:

    tagente

    tagente_datos

    Kind regards,

    Sergio B.

  7. Hello,

    Happy to see that what I'm asking seems to be added in the last release 765 RRR !

    Pandora_Macro.jpg160 KB · JPG
    Pandora_Macro.jpg
  8. Hello Christophe,

    We hope that with this new marcros you are able to perform the query you needed in a much more simplier way.

    Kind regards,

    Sergio B.

Entra en tu cuenta para publicar.