I have tried to use following SQL in kibana internal index:
SELECT dashboard.title FROM ".kibana*" WHERE updated_at > TODAY() - INTERVAL CAST(MONTH_OF_YEAR(TODAY()) AS interval_month) MONTHS
Unfortunately I get an error:
[essql] > Couldn't parse Elasticsearch SQL query. You may need to add double quotes to names containing special characters. Check your query and try again. Error: [parsing_exception] line 1:79: Invalid [INTERVAL MONTH] value [CAST(MONTH_OF_YEAR(TODAY()) AS interval_month)]: expected digit (at [0]) but found [C]
Tired as well to user CONVERT function and convert to integer, but this does not work either.
I'm a bit unclear on exactly what you're trying to query for, but you may have more success using the DATE_TRUNC function for this query. I believe you're trying to find all dashboards updated since the beginning of the year? If so this query should work:
SELECT dashboard.title FROM ".kibana*" WHERE updated_at > DATE_TRUNC('year', TODAY())
DATE_TRUNC will "zero-out" the parts of the date up to the specified unit, in this case the year. So if TODAY() is 2020-05-21T12:45:41, DATE_TRUNC('year', TODAY()) would equal 2020-01-01T00:00:00.
If that's not what you're trying to get, let me know what you're trying to query for and I can help more
Thank you! Original idea was to get monthly/weekly reports. So I need to use TODAY() or any other function to get 1 of each month and as well current week. I guess I shall be able to use DATE_TRUNC fucntion like this? DATE_TRUNC('month', TODAY()), maybe DATE_TRUNC('week', TODAY())?
Ok got this message, do I need to install or configure it somehow?:
[essql] > Unexpected error from Elasticsearch: [verification_exception] Found 1 problem(s) line 1:69: Unknown function [DATE_TRUNC]
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.