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