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