topic Re: Displaying concurrent users over time in Splunk Search

Displaying concurrent users over time

Matthias_BY — Mon, 28 Sep 2020 14:56:36 GMT

Hello,

i have data with VPN connectivity. Format is quite simple:

1378764018782;1378764018781;OK

First is Start Time of the Session - second timestamp is Ending/Stop of a Session. Both in Epoch time.

I figured already out how to convert etc. all the info. so my current search gives me the results:

Start End Concurrent duration

09/10/2013 00:00:19 09/10/2013 00:00:57 1 38

09/10/2013 00:00:19 09/10/2013 00:00:20 2 1

09/10/2013 00:00:19 09/10/2013 00:00:19 3 0

09/10/2013 00:00:23 09/10/2013 00:06:59 2 396

Now i want to have a Report to fill this up to answer the question: how many people have been conccurrent at 12 etc. with the option to AVG per hour. i guess somehow with gentimes but i don't figured out how it is working...

Result:

00:00 2 Concurrent Sessions
01:00 1 Concurrent Session
02:00 1
03:00 1
...
06:00 1
07:00 0

thanks a lot
matthias

Re: Displaying concurrent users over time

alacercogitatus — Thu, 10 Oct 2013 13:20:03 GMT

EDIT

Try this one:

Original

Try this and see if it works.

Re: Displaying concurrent users over time

Matthias_BY — Thu, 10 Oct 2013 13:30:53 GMT

Hi, this displays only the hours where i have an event starting:

22:00 4

nothing else... it does not display

22:00 4
23:00 1
24:00 1
01:00 1
02:00 1
...

Re: Displaying concurrent users over time

alacercogitatus — Thu, 10 Oct 2013 13:35:39 GMT

You may need to tweak the stats command. Avg perhaps?
stats avg(concurrent) as concurrent by date_hour | fillnull value=1 concurrent

Re: Displaying concurrent users over time

Matthias_BY — Thu, 10 Oct 2013 13:42:34 GMT

still same result... date_hour is always the field when the session did start/end. but i need to stretch this accross the duration timeline. from start of the session until the end.

Re: Displaying concurrent users over time

alacercogitatus — Thu, 10 Oct 2013 13:58:29 GMT

try this: |concurrency ... | fillnull value=1 concurrent | stats max(concurrent) as concurrent by date_hour You may need to fiddle with it a bit, I don't think concurrent reports 1 as a value, so you just need to fill it in where needed.

Re: Displaying concurrent users over time

Matthias_BY — Thu, 10 Oct 2013 14:06:03 GMT

thanks a lot for your support so far - but it's still the same result - only one hour with all the count instead many with the evolving of the time:

Time Concurrent
22:00 4

Re: Displaying concurrent users over time

rjthibod — Mon, 03 Oct 2016 09:57:19 GMT

Here is my version of a query to help. I use a of version of the following in my app where I am dealing with concurrent users and sessions. I had a similar type of problem a while ago (https://answers.splunk.com/answers/246301/concurrent-users-per-time-bucket-from-transactions.html), and the following is the most reusable solution I could come up with that would also scale.

Note, before you get to the timechart command, the _time field will be in 1-minute intervals, so you should be able to inspect the data at a much higher granularity if you want. Also, the field duration2 shows the amount of time per 1-minute bucket.

sourcetype=vpn "OK"
| eval earliest=StartTimestamp 
| eval latest=StopTimestamp
| streamstats count as evt_id
| convert ctime(earliest) as earliest timeformat="%m/%d/%Y:%H:%M:%S" 
| convert ctime(latest) as latest timeformat="%m/%d/%Y:%H:%M:%S"
| eval duration = latest - earliest
| eval start_time_min = floor(earliest - (earliest % 60))
| eval end_time_min   = floor(latest - (latest % 60))
| eval time_bins = mvrange(start_time_min, end_time_min + 1, 60)
| mvexpand time_bins 
| eval duration2 = if(start_time_min == end_time_min, duration, if(start_time_min == time_bins, round(start_time_min + 60 - earliest, 3), if(end_time_min == time_bins, round(latest - end_time_min, 3), 60))) 
| rename time_bins as _time
| table _time duration2 evt_id
| eval _span = 60
| timechart span=1h dc(evt_id) as concurrent

Re: Displaying concurrent users over time

plaftaric — Fri, 06 Sep 2019 10:37:09 GMT

Thanks. Useful SPL design patterns.

Re: Displaying concurrent users over time

weetabixsplunk — Mon, 01 Mar 2021 21:04:25 GMT

I tried this but I keep getting a message saying "Field ' time_bins' does not exist in the data"