Displaying concurrent users over time

[Matthias_BY]

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

Search:
sourcetype=vpn "OK" | eval Start = StartTimestamp | eval Stop = StopTimestamp | eval Start = round(Start/1000,0) | eval Stop = round(Stop/1000,0) | eval Start_epoch = Start | eval duration = (Stop-Start) | convert ctime(Start) AS Start | convert ctime(Stop) AS Stop | concurrency duration=duration start=Start_epoch output=concurrent | table Start Stop concurrent duration

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

[rjthibod]

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

[weetabixsplunk]

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

[plaftaric]

Thanks. Useful SPL design patterns.

[alacercogitatus]

EDIT

Try this one:

sourcetype=vpn "OK"|eval earliest=StartTimestamp | eval latest=StopTimestamp| streamstats count as evt_id | convert ctime(earliest) as et timeformat="%m/%d/%Y:%H:%M:%S" | convert ctime(latest) as lt timeformat="%m/%d/%Y:%H:%M:%S" | map search="|gentimes start=$et$ end=$lt$ increment=5s |eval Present=1 |eval evt_id=$evt_id$| eval _time = starttime |fields - end* start* "|fields _time Present evt_id |bin _time span=5s | timechart span=30s dc(evt_id)

Original

sourcetype=vpn "OK" | eval Start = StartTimestamp | eval Stop = StopTimestamp | eval Start = round(Start/1000,0) | eval Stop = round(Stop/1000,0) | eval Start_epoch = Start | eval duration = (Stop-Start) | convert ctime(Start) AS Start | convert ctime(Stop) AS Stop | concurrency duration=date_hour start=Start_epoch output=concurrent | stats max(concurrent) as concurrent by date_hour

Try this and see if it works.

[Matthias_BY]

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

[alacercogitatus]

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.

[Matthias_BY]

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.

[alacercogitatus]

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

[Matthias_BY]

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