Splunk Search

Displaying concurrent users over time

Matthias_BY
Communicator

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:

alt text

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

0 Karma

rjthibod
Champion

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
Explorer

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

0 Karma

plaftaric
Explorer

Thanks. Useful SPL design patterns.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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.

0 Karma

Matthias_BY
Communicator

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

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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.

0 Karma

Matthias_BY
Communicator

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.

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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

0 Karma

Matthias_BY
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...