Events By User

[lore6673 (Lorenzo)]

Hi, I have a doc in ES with the number of events of Google Analytics and I want to create a visualization with the Average number of Events By Users. In the doc I have the field "email" and I would like to create a sort of script field:

Sum(events by Day)/count(email by day)

How can get this graph in Kibana?

Thanks
Lorenzo

[LeeDr (Lee Drengenberg)]

Scripted fields only work on data in each individual docs, so you can't do counts and sums /day.

But there's lots of ways in Kibana to represent that data.

I don't have data with user emails, so I'm going to simulate that with my metricbeat data and let's pretend that these metricset.name values are your emails. This screenshot is from clicking on that field name in the Discover tab;

If I just click that Visualize button from Discover it will create a Histogram of the count of the events (documents actually) for each one.

But if you want something by day, you could create a new visualization,
first select a Date Histogram
and then Split Series and do a Terms aggregation again on the same email field (I'm using metricset.name again).
Then set your timepicker in the top right corner (I set mine to last 7 days)
and set the interval in the Date Histogram to Daily instead of the default Auto

Roughly this same process could be used with other chart types of a data table visualization.

[lore6673 (Lorenzo)]

Ciao LeeDR,

thanks for the answer, but this is not I'd get from Kibana.

If I try to use your example, I need the [#docs]/[# metricset.name], in other words the avg number of docs for each metricset.name.

Lorenzo

[LeeDr (Lee Drengenberg)]

I think you have that inverted? If you want Average number of Events By Users. then you need the
count of events for each user over the total count of all events. Otherwise users with very few events will have the largest result since you're putting it in the denominator.

[lore6673 (Lorenzo)]

No, I think I have implemented what you kindly suggested.
I try to rephrase the request with one example.
I have these set of docs:

"User", "Day", "Total Events"
user_1 , day_1, 2
user_2, day_1, 4
user_3, day_1, 9
user_1, day_2, 4
user_2, day_2, 2

What I'd like to get is a bar chart with the following data:

day_1, 5
day_2, 3

Is it possibile to get this aggregation sum(total events) / count(user)?

Thanks so much
Lorenzo

[LeeDr (Lee Drengenberg)]

Here's I'm loading data like you described;

POST discuss/doc
{
  "user":"user_1",
  "day":"day_1",
  "total_events":2
}

POST discuss/doc
{
  "user":"user_2",
  "day":"day_1",
  "total_events":4
}
POST discuss/doc
{
  "user":"user_3",
  "day":"day_1",
  "total_events":9
}
POST discuss/doc
{
  "user":"user_1",
  "day":"day_2",
  "total_events":4
}
POST discuss/doc
{
  "user":"user_2",
  "day":"day_2",
  "total_events":2
}

It shows in Discover tab like this;

And I can create a new Data Table visualization that does a Terms aggregation on day.keyword and the Metric is the Sum of total_events;

Or I can make a bar chart that does a terms aggregation first on user.keyword, and then on day.keyword like this;

Or I can reverse the order of those 2 terms aggregations like this;

[lore6673 (Lorenzo)]

Thanks
Ok clear. But I can not visualize in the last chart you posted the value 5 and 3. This is the objective of my post.
Lorenzo

[LeeDr (Lee Drengenberg)]

Can you post some screenshots of what you have? And then explain what's not correct or missing from it.

Thanks,
Lee