i have data with VPN connectivity. Format is quite simple:
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
sourcetype=vpn "OK" | eval Start = StartTimestamp | eval Stop = StopTimestamp | eval Start = round(Start/1000,0) | eval Stop = round(Stop/1000,0) | eval Startepoch = Start | eval duration = (Stop-Start) | convert ctime(Start) AS Start | convert ctime(Stop) AS Stop | concurrency duration=duration start=Startepoch 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...
00:00 2 Concurrent Sessions
01:00 1 Concurrent Session
thanks a lot
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)
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.
Hi, this displays only the hours where i have an event starting:
nothing else... it does not display
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.
|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.
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:
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