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
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
I tried this but I keep getting a message saying "Field ' time_bins' does not exist in the data"
Thanks. Useful SPL design patterns.
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.
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
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.
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.
You may need to tweak the stats command. Avg perhaps?
stats avg(concurrent) as concurrent by date_hour | fillnull value=1 concurrent
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
...