Managing concurrent sessions in FileHold

Concurrent sessions are one of the dimensions of licensing in FileHold. You can purchase as few or as many as you may need for the intensity of usage for your organization. Large sites often need only concurrent sessions for 10% of their users. Smaller sites might need concurrent sessions for 25% of their users. FileHold will notify you if the number of concurrent sessions has been exceed in addition to maintaining a log of every time this happens. Unfortunately that only informs an administrator that a user was unable to access the system when they wanted it. For planning purposes it would be better to monitor usage over time and look for a model that could predict future requirements in advance of users getting a "too many sessions" message.

Fortunately FileHold provides detailed tracking of users' connections to the system in the user activity log. This information can be easily export to a CSV file for further analysis in a tool like Excel. A little bit of advanced Excel knowledge is required, but the histogram and charting tools can be very effective in understanding the usage of the system.

The following chart shows the FileHold concurrent session usage over the course of one week with the maximum value of concurrent sessions in each bar. Clearly Wednesday was when the most sessions, seven, were in use. Saturday was the least with zero concurrent sessions being used.

Image

This chart does not show the whole story though as these are only the peak values on those days. We can break the day down into smaller components to understand how frequently this peak was reached. If we take a look at Wednesday in 20 minute increments we get the following graph.

Image

At the day level, the story is quite different. We actually only reached 7 concurrent sessions at approximately 10:20 and at no other time. Usage drops down to 3 during lunch and the sustained peak is 6.

This sort of analysis could be performed over weeks, months, or years as needed. The trick is how to go from the user activity log which provides the log in and log out times of all users to something that can be put into one of these nice charts. The charts are simply histograms with a count of the concurrent session usage during a period of time. We need to translate from the start and end time of the activity log to a count of sessions at a specific time. As I did not find anything in Excel that would do this for me automatically I have prepared a little Visual Basic for Application (VBA) macro that will do the job.

The macro has three parameters: MinutesPerBin, StartDate, and EndDate. To keep things simple in the macro these parameters are simply defined ranges in the workbook, each with a single cell. The minutes per bin defines how large the period of time will be where concurrent sessions are counted. This will be the granularity of your histogram. In my day example above the bin size was 20 minutes. Start and end date should match the data you extracted from the user activity log.

The macro assumes the presence of two sheets in the workbook: Log and Data. The Log sheet must contain an import of the CSV file created by the user activity log export. The Data sheet should be blank. After the macro has run the Data sheet will contain a record for every connected user in every time interval. There are two parts to the macro processing. The first part will reformat the important information from the log. The data in column two is the main connection data that will be input data for the histogram tool. The second part will create the data representing each bin in the time period and place it in the fifth column of the Data sheet. You need the list of bins to ensure that time periods with zero sessions are depicted properly in the histogram.

You will be able to use the Excel data analysis histogram tool using the data in column two for the input range and the data in column five for the bin range. The output of the histogram tool will be what you use to create the charts.

Update: Excel 2016 has simplified creating histograms, so you no longer need the bins created you only need the number of users that have a concurrent session in the given interval.

Option Explicit
Sub GenerateHistogramData()
    Const UsernameCol = 2
    Const LoginTimeCol = 7
    Const LogoutTimeCol = 8
    Const LogSheet = "Log"
    Const DataSheet = "Data"
    Const MinutesPerDay = 60 * 24
    
    Dim MinutesPerBin As Integer
    Dim Row As Long
    Dim OutputRow As Long
    Dim NumberOfBins As Integer
    Dim LogoutTime As Double
    Dim LoginTime As Double
    Dim Bin As Double
    Dim StartDateTime As Double
    Dim EndDateTime As Double
                
    MinutesPerBin = Range("MinutesPerBin").Value
    StartDateTime = Range("StartDate").Value
    EndDateTime = Range("EndDate").Value
        
    Row = 2
    OutputRow = 1
    Sheets(DataSheet).Cells.ClearContents
    Do While (Worksheets(LogSheet).Cells(Row, UsernameCol) <> "")
        LogoutTime = Worksheets(LogSheet).Cells(Row, LogoutTimeCol)
        LoginTime = Worksheets(LogSheet).Cells(Row, LoginTimeCol)
        If (LogoutTime > 0) Then
            
            NumberOfBins = (LogoutTime - LoginTime) * MinutesPerDay / MinutesPerBin
            
            Bin = Int(LoginTime * (MinutesPerDay / MinutesPerBin)) / (MinutesPerDay / MinutesPerBin)
            Do While NumberOfBins > 0
                Worksheets(DataSheet).Cells(OutputRow, 1).Value = Worksheets(LogSheet).Cells(Row, UsernameCol) + " " + Format(Bin, "yyyy-mm-dd hh:MM:ss")
                Worksheets(DataSheet).Cells(OutputRow, 2).Value = Bin
                Worksheets(DataSheet).Cells(OutputRow, 3).Value = Format(LoginTime, "yyyy-mm-dd hh:MM:ss")
                Worksheets(DataSheet).Cells(OutputRow, 4).Value = Format(LogoutTime, "yyyy-mm-dd hh:MM:ss")
                
                Bin = Bin + MinutesPerBin / MinutesPerDay
                OutputRow = OutputRow + 1
                NumberOfBins = NumberOfBins - 1
            Loop
        End If
        Row = Row + 1
    Loop
    
    NumberOfBins = (EndDateTime - StartDateTime) * MinutesPerDay / MinutesPerBin
    Bin = Int(StartDateTime * (MinutesPerDay / MinutesPerBin)) / (MinutesPerDay / MinutesPerBin)
    OutputRow = 1
    Do While NumberOfBins > 0
        Worksheets(DataSheet).Cells(OutputRow, 5).Value = Bin
        Bin = Bin + MinutesPerBin / MinutesPerDay
        NumberOfBins = NumberOfBins - 1
        OutputRow = OutputRow + 1
    Loop
    
End Sub