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

[mccurity]

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?

[mccurity]

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.

[ITWhisperer]

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.

[ITWhisperer · Accepted solution]

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

-- your search
| eval firstday=strptime(first_seen,"%Y-%m-%d")
| eval lastday=strptime(last_seen,"%Y-%m-%d")
| eval interveningdays=round((lastday-firstday)/(60*60*24))+1
| streamstats count as row 
| streamstats sum(interveningdays) as activedays
| sort - activedays
| append [|makeresults count = 1|eval activedays=1| fields - _time]
| sort activedays
| makecontinuous activedays
| sort - activedays
| streamstats min(row) as row
| streamstats values(user) as user values(session) as session values(firstday) as firstday by row
| sort activedays
| streamstats count as userday by row
| eval _time=firstday+((userday - 1)*(60*60*24))
| stats count by _time