topic Count an event multiple times (once per day) spanning a time range within the event in Dashboards & Visualizations

Count an event multiple times (once per day) spanning a time range within the event

mccurity — Fri, 25 Sep 2020 20:11:40 GMT

I'm trying to use timechart (which may be the wrong approach) to count events for each day that were "active" over a period of time. For example the data would be:

user	session	first_seen	last_seen
user1	137271	2020-09-13T00:39:40.079Z	2020-09-24T00:56:30.941Z
user1	137264	2020-09-13T13:17:10.052Z	2020-09-25T13:19:37.342Z
user1	137272	2020-09-13T13:48:24.513Z	2020-09-25T13:27:27.663Z
user2	137272	2020-09-16T02:45:28.436Z	2020-09-24T13:21:27.215Z
user2	137267	2020-09-18T13:03:01.847Z	2020-09-25T13:18:05.927Z
user3	137272	2020-09-13T13:04:52.235Z	2020-09-25T13:07:02.422Z

Resulting in (for use in some ort of timechart like graph, or maybe even a bar chart):

Date (x axis)	Count (y axis)
2020-09-13	4
2020-09-14	4
2020-09-15	4
2020-09-16	5
2020-09-17	5
....	...
2020-09-24	6
2020-09-25	4

One though that came to mind was creating then expanding a multivalue field with a value for each day in between the first and last dates. Thought I'm not sure how to even accomplish that if doable.

I've also thought a couple time basing something on a calculated duration but may be challenging given the varying first and last times.

Or maybe there's a precanned app out there I'm not finding?

Re: Count an event multiple times (once per day) spanning a time range within the event

ITWhisperer — Fri, 25 Sep 2020 23:27:12 GMT

OK this is a bit complicated, and possibly not very efficient, but it seems to give the results you want

Re: Count an event multiple times (once per day) spanning a time range within the event

mccurity — Wed, 14 Oct 2020 17:23:32 GMT

Thanks. I did run into issues with having too much data; a lot of users with a lot of sessions each running for months resulting in too many results.

Re: Count an event multiple times (once per day) spanning a time range within the event

ITWhisperer — Wed, 14 Oct 2020 17:43:35 GMT

You could try creating a summary index, say once a week, and then do you main query using this index. If a session is active, will there be at least one event every day? It might make life easier if there is.