MySQL Table FIltering


I have an audit schema named events in MySql
I have created a audit profile named Events
The table name for the audit is AUDIT_EVENTS
I need an Audit Log Display in perspective filtered on ACTOR - (ex. 'usr-prov:BMS_USERS:/usr:JZunigaEspitia')
Display needs to allow the operator to set beginning and ending dates
However nothing seems to be getting logged to the AUDIT_EVENTS table

I don`t know how to create the date filters

check screenshots attached.

Could you help me to create it?

You're last screenshot is correct as far as the query string goes. Change the parameter data types to Datetime instead of String. Next when you bind the named query to your table, use date picker components, then use their values as the source for the query parameters.

Thank you but sorry what is a date picker components. Could you give me a sample?

It's actually the DateTime Picker component. It can be found in the Perspective Components panel (usually on the right side of the screen) in the Inputs section.

I highly recommend going through Inductive University for introduction training for free!
On the right side of the designer, inside the Perspective Components panel, has all the pre-built perspective components inside. You can drag and drop these components into your view.

You showed your table in a screenshot already. On it's data property, bind it to the date picker value. Click on the little fx to get your component path for the binding.

I got it and its very clear thank you. But something still went wrong.
That s te message I`ve got.

As you can see The sellection doesn`t works.

This is what I have in the page

Parameters should be like :date1 and :date2 with a leading colon.

MySQL is currently interpreting it as you are referencing two columns date1 and date2 which don't exist in the table hence the error Invalid column name: 'date1'

1 Like

When you use parameters in the query, you have to call them out with a colon. :date1 and :date2
@bkarabinchak.psi beat me to it :slight_smile:


I had my coffee this morning before coming on today :slight_smile:


Amazing Thank you guys the fault is gone...

Last question, is it possible to use same DateTime Picker object to date1 and date2?

You could but if your query ends up like BETWEEN '2023-06-08 08:55:00' AND '2023-06-08 08:55:00' where the dates are identical you're never going to get any records.

However if you have the user pick one date, and have a custom property that calculates the other date, and use those two - that would work.

1 Like

There is an open ticket to allow a date picker to select a range, but it could be a while before it is implemented.

1 Like

Hi guys!

Thank you very much for all your help and patience.
It's working perfectly.

1 Like