Hi,
I have a requirement below and want some advise how to do this.
-
Target log is kind of summarized access log which has fields like timestamp, click_count, url, some_parameter. Interval of timestamp is usually 10 seconds.
For example:
00:00:10, 10 , /a, p1
00:00:11, 5 , /a, p2
00:00:20, 3 , /a, p1
00:00:21, 6 , /a, p2
...
00:01:10, 4, /a, p1
00:01:10, 8, /a, p2
... -
I want to visualize (on Kibana) sum of total click count per minute, but if we have multiple record for the same "url" and "some_parameter" in the same minute, that value should be average.
In SQL, the aggregation can be like this:
SELECT -- The values to be plotted are sum(click_count) by URL
timestamp, sum(click_count), url
FROM (
SELECT -- calculate average 1 minute click_count by 'url' and 'some_parameter'
round(timestamp,'min') as timestamp , avg(click_count), url, some_parameter
FROM
summary_access_log
GROUP BY
round(timestamp,'min'), url, some_parameter
)
GROUP BY
url, timestamp
As far as I know, I can specify only one aggregation type for Y-axis, but what I want to do is kind of 2-phase aggregation, first do 'average' aggregation in one minute by url and some_parameter, and then do 'sum' aggregation by url.
Are there any way to do this on Kibana?
Regards,
Hironori Ogibayashi