Counting the last element of each aggregation

Elastic Stack Kibana
1

Hello,

I have logs with the fields:
. IP
. Timestamp
. URI
. SessionId

Each log shows the time (timestamp) the URI was visited. The SessionId is a field I created by concatenating IP with Timestamp (without the minutes or seconds). That is, all the logs belonging to the same day and hour are grouped into the same SessionId.

For e.g.

IP Timestamp URI SessionId
1.2.3.4 December25th2017T08:30:00 google.com 1.2.3.4_25-12-2017_8
1.2.3.4 December25th2017T08:45:00 youtube.com 1.2.3.4_25-12-2017_8
1.2.3.4 December25th2017T09:30:00 facebook.com 1.2.3.4_25-12-2017_9
1.2.3.4 December25th2017T09:58:00 youtube.com 1.2.3.4_25-12-2017_9
4.3.2.1 December30th2017T15:20:00 yahoo.com 4.3.2.1_30-12-2017_15
4.3.2.1 December30th2017T15:30:00 elastic.com 4.3.2.1_30-12-2017_15
4.3.2.1 December30th2017T15:58:00 youtube.com 4.3.2.1_30-12-2017_15

Now, I need to know the top URI that were the last ones to be visited in the sessions.
That is, for the example above, "youtube.com" was the last URI visited in the following sessions:
. 1.2.3.4_25-12-2017_8
. 1.2.3.4_25-12-2017_9
. 4.3.2.1_30-12-2017_15

Therefore, I need a visualization/query that indicates something like:
URI Count
youtube.com 3

I've tried doing a couple split rows aggregations and filters but none of them gets me the result I want.

How can I achieve this in Kibana?

Thank you

2

Hi @Tomas_Law,

Try using a Terms aggregation, like this:

01 AM
Screen Shot 2018-01-17 at 11.29.01 AM.png4012×1504 226 KB

Let me know if that helps!

3

Hey Chris,

Doing that aggregation will count+1 everytime the URI "google.com" is accessed.

I only want to count it if it was the URI that was accessed last in a session.

Thanks for the reply.

4

So far, I've managed to obtain the following table where the last visited URI's are selected.

image
image.png1273×475 43.6 KB

What I need now is to group those last 2 URI's and compute the count for each of them.

Therefore, the desired result is something like:

/www-org.empleo.gob.es/es/garantiajuvenil/accesoJovenes.html 2
/www-org.empleo.gob.es/fogasa/descripcion_organigrama.html 1
/www-org.empleo.gob.es/fogasa/contacto.html 1

How can I group those URI's now?

5

I don't think we can get you all of the way there in Kibana, but we can get you pretty close :slight_smile:

  1. Under Metrics, select "Top Hit". For the "Field", select cs_uri.keyword. For the "Sort On", select timestamp.
  2. Under Buckets, select "Split Rows". For the aggregation, select "Terms" and for the "Field" select session_id.keyword.

This will give you a list of all of the last cs_uris for each session_id. Unfortunately, that's the best we can do (that I can figure), but you could export it as CSV, import to something like Excel, and manually compute the count.

1 Like
6

Hey Lukas,

The solution you proposed does get me the list of all of the last cs_uris for each session_id.

I've come up with a (better but not complete) solution where I manage to get the count split by tables but I can't order them.

The link to the post with the split table aggregation is:

Thanks for replying!

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.