Unable to aggregate on timestamp using HISTOGRAM

Plugins SQL
1

Hi Team,

I am trying to summarize my log data by aggregating on hourly level using the timestamp attribute. The below query was working in the elasticsearch distribution but does not work in open distro. I have searched the forums for alternative operators but unsuccessful. Can someone please help out here.
Thanks,
Tarun
Query :

curl -X POST "2.6.3.6/elasticsearch/_opendistro/_sql?format=csv" -H 'Content-Type: application/json' -d'{  "query": "SELECT HISTOGRAM(`@timestamp`, INTERVAL 1 HOUR) AS stime,`agent.hostname.keyword` AS pod,`url.keyword` AS durl,AVG(resp_time) AS avgresp,COUNT(*) ,AVG(upstream_time) AS avgupst FROM filebeat-2021.05.01 GROUP BY stime,pod,durl", "request_timeout":"400s",  "page_timeout":"400s","fetch_size":1000 }'
2

Welcome @tarund!

Can you help us understand what ‘does not work’ really means - e.g. an error message?

At first glance, HISTOGRAM looks a bit weird - I’m not sure Open Distro supports that type of syntax. I’ll find someone who can help you more definitively though.

3

What i meant is that the operator is not supported and would like an equivalent function in Open Distro.
I have tried the date_histogram function, but when i use it, I get an error - IndexNotFoundException, no such index. Whereas, if i remove the function the query works with that same index.
So, my requirement is how to aggregate on timestamp field. Is there a support for this in Open Distro ?

curl  -X POST "5.6.3.3/elasticsearch/_opendistro/_sql?format=csv" -H 'Content-Type: application/json' -d'{  "query": "SELECT date_histogram(`@timestamp`, INTERVAL 10 MINUTE) AS stime,resp_code,COUNT(*) AS count FROM `filebeat-2021.05.11` GROUP BY stime,resp_code" }'
ERROR-"details": "org.elasticsearch.index.IndexNotFoundException: no such index [`filebeat-7.12.1-2021.05.11`]\nFor more details, please send request for Json format to see the raw response from elasticsearch engine."
curl  -X POST "5.6.3.3/elasticsearch/_opendistro/_sql?format=csv" -H 'Content-Type: application/json' -d'{  "query": "SELECT date_histogram(field=`@timestamp`, 'interval'='1h') AS stime,resp_code,COUNT(*) AS count FROM `filebeat-2021.05.11` GROUP BY stime,resp_code" }'
ERROR - "reason": "Invalid SQL query",  "details": "ERROR. token : EQ, pos : 51","type": "ParserException"
curl  -X POST "5.6.3.3/elasticsearch/_opendistro/_sql?format=csv" -H 'Content-Type: application/json' -d'{  "query": "SELECT `@timestamp` AS stime,resp_code,COUNT(*) AS count FROM `filebeat-2021.05.11` GROUP BY stime,resp_code" }'
WORKS fine without aggregator on timestamp
4

Here is an example of date_histogram usage in a test file:

SELECT count(*) from online GROUP BY date_histogram('field'='insert_time','interval'='4d','alias'='days')

(link)

5

Thanks @searchymcsearchface for the example usage, But when i try this syntax i get parserException as mentioned above. Is it something to do with combination of quotes within curl ?

6

Here is an example of a cURL command I know to work (using some sample data):

curl -k -u admin:admin -XPOST "https://localhost:9200/_opendistro/_sql" -H 'Content-Type: application/json' -d'{  "query" : "SELECT count(*) from opensearch_dashboards_sample_data_logs GROUP BY date_histogram('\''field'\''='\''@timestamp'\'','\''interval'\''='\''1d'\'','\''alias'\''='\''days'\'')"}'

I find it super helpful to build my queries in Dev Tools then use the wrench icon and the “Copy as cURL” menu item.

Screen Shot 2021-05-14 at 8.29.23 AM
Screen Shot 2021-05-14 at 8.29.23 AM910×430 44.5 KB

Note that the “Copy as cURL” won’t have your auth or cert info, so I had to add the -k and -u switches as well as alter the URI.

7

Thanks @searchymcsearchface, finally I was able to get through date_histogram. But I also found out that the key field(timestamp in this case) does not come in the output for csv format, there is an issue opened for this OpenSearch function support · Issue #44 · opensearch-project/sql · GitHub
So I will have to wait until then.