DEVHIDE
Login Or Sign up

PowerShell Script to get max simultaneous calls for each day

373 Views Asked by At

I am trying to analyze call logs from our 3CX PBX in order to evaluate our peak calling hours. I would like to see for each day in said logs what was the max. simultaneous calls we had at one point during the day.

The call logs are stored in a CSV file and look like this. Each row represents a call from start to finish.

CallDate CallStart CallEnd
31.05.2021 11:09:37 11:10:29
31.05.2021 11:09:37 11:19:25
31.05.2021 11:09:40 11:11:41
31.05.2021 11:11:32 11:16:52
31.05.2021 11:12:06 11:14:15
31.05.2021 11:13:08 11:13:31
31.05.2021 11:13:42 11:17:02
31.05.2021 11:13:43 11:17:01
31.05.2021 11:15:18 11:16:07
31.05.2021 11:16:46 11:22:04
31.05.2021 11:16:58 11:27:18
31.05.2021 11:17:43 11:17:53
31.05.2021 11:18:53 11:19:22
31.05.2021 11:19:24 11:19:30
31.05.2021 11:19:24 11:21:15
31.05.2021 11:19:31 11:23:08
31.05.2021 11:20:21 11:20:35
31.05.2021 11:20:55 11:25:18
31.05.2021 11:21:07 11:21:56
31.05.2021 11:21:25 11:21:36
31.05.2021 11:21:39 11:28:50
31.05.2021 11:21:41 11:21:52
31.05.2021 11:22:22 11:22:43
31.05.2021 11:23:25 11:24:34
31.05.2021 11:24:10 11:29:53
31.05.2021 11:25:30 11:27:18
31.05.2021 11:27:57 11:28:32
31.05.2021 11:30:36 11:32:02
31.05.2021 11:31:44 11:32:09
31.05.2021 11:32:39 11:37:48
31.05.2021 11:34:19 11:50:04
31.05.2021 11:34:42 11:35:05
31.05.2021 11:35:35 11:39:09
31.05.2021 11:35:44 11:49:22
31.05.2021 11:36:41 11:37:00
31.05.2021 11:37:34 11:37:58
31.05.2021 11:37:57 11:38:33
31.05.2021 11:39:45 11:40:15
31.05.2021 11:39:56 11:41:20
31.05.2021 11:40:21 11:40:38
31.05.2021 11:41:17 11:42:07
31.05.2021 11:42:16 11:44:38
31.05.2021 11:42:52 11:46:54
31.05.2021 11:43:03 11:43:16
31.05.2021 11:43:35 11:44:17
31.05.2021 11:44:25 11:44:59
31.05.2021 11:44:59 11:48:00
31.05.2021 11:45:36 11:52:58
31.05.2021 11:45:48 11:46:11
31.05.2021 11:46:03 11:54:17
31.05.2021 11:46:11 11:46:42
31.05.2021 11:47:58 11:48:31
31.05.2021 11:50:19 11:50:26
31.05.2021 11:50:33 11:50:58
31.05.2021 11:50:36 11:50:43
31.05.2021 11:50:54 11:51:12
31.05.2021 11:51:19 11:51:28
31.05.2021 11:52:22 11:55:19
31.05.2021 11:53:43 11:55:24
31.05.2021 11:54:44 11:55:12
31.05.2021 11:55:45 11:55:55
31.05.2021 11:56:04 11:56:19
31.05.2021 11:56:26 11:56:28
31.05.2021 12:17:06 12:17:39
31.05.2021 12:17:53 12:18:39
31.05.2021 12:19:25 12:23:52
31.05.2021 12:20:02 12:23:02
31.05.2021 12:21:29 12:26:54
31.05.2021 12:33:27 12:39:38
31.05.2021 12:33:43 12:35:36
31.05.2021 12:39:46 12:40:52
31.05.2021 12:54:23 12:55:54
31.05.2021 12:55:17 12:55:39
31.05.2021 13:03:11 13:06:30
31.05.2021 13:04:47 13:13:33
31.05.2021 13:15:02 13:16:51
31.05.2021 13:16:14 13:16:49
31.05.2021 13:17:54 13:20:03
31.05.2021 13:27:53 13:29:41
31.05.2021 13:29:22 13:29:44
31.05.2021 13:29:56 13:38:59
31.05.2021 13:29:58 13:30:14
31.05.2021 13:30:26 13:34:24
31.05.2021 13:31:45 13:36:27
31.05.2021 13:32:04 13:33:03
31.05.2021 13:32:40 13:35:32
31.05.2021 13:34:54 13:35:24
31.05.2021 13:36:12 13:38:03
31.05.2021 13:38:24 13:39:05
31.05.2021 13:39:35 13:51:50
31.05.2021 13:42:12 13:56:16
31.05.2021 13:47:56 13:49:15
31.05.2021 13:49:55 13:55:08
31.05.2021 13:51:02 13:56:16
31.05.2021 13:55:17 13:55:27
31.05.2021 13:56:17 14:02:13
31.05.2021 14:00:35 14:01:27
31.05.2021 14:01:11 14:01:57
31.05.2021 14:02:13 14:03:54
31.05.2021 14:02:42 14:02:51

I think I'm quite close but I noticed a flaw in my logic. Currently, I compare if the CallStart of the next call is earlier than the CallEnd of the current call and if yes, that counts as a simultaneous call and I enter a while-loop that looks for more started calls before the end of the current call and add those too.

However, in this while-loop, I do not account for the possibility that one call might have ended while I am searching for more calls started earlier than the end date of the current call in my foreach loop which would account for one simultaneous call less.

Sorry if it is confusing. I hope you can understand what I am after.

Here is my script so far:

$CallDays = Import-Csv -Path C:\temp\calls.csv -Delimiter ";" | Group-Object {$_.CallDate}

$RawData = Import-Csv -Path C:\temp\calls.csv -Delimiter ";"

ForEach($CallDay in $CallDays)
{
    $ActualCallDate = $CallDay.Name

    if($ActualCallDate -eq "31.05.2021")
    {
        $AllCallsFromThatDay = $RawData | Where-Object CallDate -eq $ActualCallDate | Sort-Object -Property CallStart    

        [Object]$AllSimultaneousCalls = New-Object System.Collections.ArrayList

        $SimultaneousCalls = 0

        ForEach($Call in $AllCallsFromThatDay)
        {
            if($SimultaneousCalls - 1 -lt 0)
            {
                $SimultaneousCalls = 0
            }

            else
            {
                $SimultaneousCalls--
            }

            $CurrentRow = $AllCallsFromThatDay.IndexOf($Call)

            $NextRow = $CurrentRow + 1

            if($AllCallsFromThatDay[$CurrentRow] -ne $NULL)
            {
                $CurrentCallEnd = Get-Date ($ActualCallDate + " " + $Call.CallEnd)

                $NextCallStart = Get-Date ($ActualCallDate + " " + ($AllCallsFromThatDay[$NextRow]).CallStart)

                while($CurrentCallEnd -gt $NextCallStart)
                {
                    $SimultaneousCalls++

                    $NextRow++

                    if($AllCallsFromThatDay[$NextRow] -ne $NULL)
                    {
                        $NextCallStart = Get-Date ($ActualCallDate + " " + ($AllCallsFromThatDay[$NextRow]).CallStart)
                    }

                    else
                    {
                        Break
                    }
                }        

                $AllSimultaneousCalls.Add($SimultaneousCalls) | Out-Null

                $SimultaneousCalls
            }
        }

        Write-Host (($AllSimultaneousCalls | Measure-Object -Maximum).Maximum)

    }
}

The output currently looks like this:

2
14
14
19
21
20
23
25
24
35
49
48
47
47
50
56
55
61
63
62
67
66
65
65
66
65
64
64
63
68
88
87
90
107
106
106
105
105
106
105
104
107
114
113
112
111
115
124
124
132
131
130
129
130
129
128
127
128
128
127
126
125
124
123
122
123
123
122
122
121
120
120
119
119
118
118
117
116
116
115
122
121
123
126
126
126
125
124
123
126
129
128
128
128
127
128
128
127
127
127
132

Thank you for any help!

powershell 3cx
Original Q&A
2

There are 2 best solutions below

2
On

I guess the easiest way is to create a new column and count the simultaneous calls in there.
Something like:

# Install-Script Read-HtmlTable
# $Source = Read-HtmlTable -Table 0 'https://stackoverflow.com/questions/68739752/powershell-script-to-get-max-simultaneous-calls-for-each-day'
$Calls = $Source | ForEach-Object {
    [pscustomobject]@{
        Start = [datetime]::ParseExact("$($_.CallDate) $($_.CallStart)", 'dd.MM.yyyy HH:mm:ss', $Null)
        End   = [datetime]::ParseExact("$($_.CallDate) $($_.CallEnd)", 'dd.MM.yyyy HH:mm:ss', $Null)
    }
}
$TimeTable = $Calls | ForEach-Object {
        [pscustomobject]@{ Time = $_.Start; Volume = 0 }
        [pscustomobject]@{ Time = $_.End;   Volume = 0 }
} |Sort-Object -Unique Time
foreach ($At in $TimeTable) {
    foreach ($Call in $Calls) {
        if ($At.Time -ge $Call.Start -and $At.Time -lt $Call.End) {
            $At.Volume = $At.Volume + 1
        }
    }
}
$TimeTable

Which returns:

Time                  Volume
----                  ------
5/31/2021 11:09:37 AM      4
5/31/2021 11:09:40 AM      6
5/31/2021 11:10:29 AM      4
5/31/2021 11:11:32 AM      5
5/31/2021 11:11:41 AM      3
5/31/2021 11:12:06 AM      4
...

enter image description here

0
On

Since the entries overlap partially, it would be beneficial to create custom ranges from the input.

Just looking at the first three entries:

CallDate CallStart CallEnd
31.05.2021 11:09:37 11:10:29
31.05.2021 11:09:37 11:19:25
31.05.2021 11:09:40 11:11:41

We see that from 11:09:37 to 11:09:39 (= an end date, which doesn't exist in the input), we have 2 simultaneous calls. Between 11:09:40 and 11:10:29 we have 3 calls.

Following script takes that into account and creates custom ranges to reflect the peak times more precise.

# "using namespace *" is just to simplify and shorten .NET class usages in the script.
# you can also use the full class name for each class like "[System.Collections.Generic.Hashset]"
using namespace System.Collections.Generic
using namespace System.Management.Automation
using namespace System.Globalization

$data            = Import-Csv -LiteralPath (Join-Path $PSScriptRoot 'data.csv') -Delimiter ',' -Encoding utf8
$inputDateFormat = 'dd.MM.yyyy HH:mm:ss'

# predefine timespan for 1 second
$oneSecondTimespan = [timespan]::FromSeconds(1)

# dataLine is just for error reporting
$dataLine = 1

# create an array with DateTime objects (parsed)
[psobject[]]$entries = foreach ($entry in $data)
{
    $dataLine++

    $startDateString = '{0} {1}' -f $entry.CallDate, $entry.CallStart
    $endDateString   = '{0} {1}' -f $entry.CallDate, $entry.CallEnd

    try
    {
        $startDate = [datetime]::ParseExact($startDateString, $inputDateFormat, [DateTimeFormatInfo]::InvariantInfo)
        $endDate   = [datetime]::ParseExact($endDateString, $inputDateFormat, [DateTimeFormatInfo]::InvariantInfo)

        [pscustomobject]@{
            StartDate = $startDate
            EndDate   = $endDate
        }
    }
    catch
    {
        # add the line to ErrorDetails. Will be shown on output.
        $errorDetails = '[Line {0}] {1}' -f $dataLine, $_.tostring()
        $_.ErrorDetails = [ErrorDetails]::new($errorDetails)

        # re-throw exception
        Write-Error -ErrorRecord $_
    }
}


# get all start dates while also taking the enddates into account
[HashSet[datetime]]$startDatesSet = foreach ($entry in $entries)
{
    $entry.StartDate
    $entry.EndDate + $oneSecondTimespan
}


# sort
[datetime[]]$startDates = $startDatesSet | Sort-Object


# build timespan ranges
[psobject[]]$ranges = for ($i = 0; $i -lt ($startDates.Count -1); $i++)
{
    [PSCustomObject]@{
        FromDate = $startDates[$i]
        ToDate   = $startDates[$i + 1] - $oneSecondTimespan
        Calls    = 0
    }
}


# count simultanous calls
foreach ($range in $ranges)
{
    foreach ($entry in $entries)
    {
        if ($entry.StartDate -le $range.FromDate -and $entry.EndDate -ge $range.ToDate)
        {
            $range.Calls++
        }
    }
}


$ranges | Sort-Object -Property 'Calls' -Descending

Result:

FromDate            ToDate              Calls
--------            ------              -----
2021-05-31 11:46:11 2021-05-31 11:46:11     8
2021-05-31 11:46:03 2021-05-31 11:46:10     7
2021-05-31 11:21:41 2021-05-31 11:21:52     7
2021-05-31 11:46:12 2021-05-31 11:46:42     7
2021-05-31 11:21:25 2021-05-31 11:21:36     6
2021-05-31 11:21:39 2021-05-31 11:21:40     6
2021-05-31 11:45:48 2021-05-31 11:46:02     6
2021-05-31 11:21:53 2021-05-31 11:21:56     6
2021-05-31 11:46:43 2021-05-31 11:46:54     6
2021-05-31 11:21:07 2021-05-31 11:21:15     6
2021-05-31 11:47:58 2021-05-31 11:48:00     6
2021-05-31 11:37:34 2021-05-31 11:37:48     5
2021-05-31 11:21:16 2021-05-31 11:21:24     5
2021-05-31 11:21:37 2021-05-31 11:21:38     5
2021-05-31 11:21:57 2021-05-31 11:22:04     5
2021-05-31 11:22:22 2021-05-31 11:22:43     5
2021-05-31 11:46:55 2021-05-31 11:47:57     5
2021-05-31 11:48:01 2021-05-31 11:48:31     5
2021-05-31 11:36:41 2021-05-31 11:37:00     5
2021-05-31 11:37:57 2021-05-31 11:37:58     5
2021-05-31 11:45:36 2021-05-31 11:45:47     5
2021-05-31 11:44:59 2021-05-31 11:44:59     5
2021-05-31 11:44:25 2021-05-31 11:44:38     5
2021-05-31 11:43:03 2021-05-31 11:43:16     5
2021-05-31 11:24:10 2021-05-31 11:24:34     5
2021-05-31 11:20:55 2021-05-31 11:21:06     5
2021-05-31 11:43:35 2021-05-31 11:44:17     5
2021-05-31 11:20:21 2021-05-31 11:20:35     5
2021-05-31 11:16:46 2021-05-31 11:16:52     5
2021-05-31 11:19:24 2021-05-31 11:19:25     5
2021-05-31 11:15:18 2021-05-31 11:16:07     5
2021-05-31 11:13:43 2021-05-31 11:14:15     5
2021-05-31 13:32:40 2021-05-31 13:33:03     5
2021-05-31 11:16:58 2021-05-31 11:17:01     5
2021-05-31 11:16:08 2021-05-31 11:16:45     4
2021-05-31 11:14:16 2021-05-31 11:15:17     4
2021-05-31 11:35:44 2021-05-31 11:36:40     4
2021-05-31 11:37:01 2021-05-31 11:37:33     4
2021-05-31 11:13:42 2021-05-31 11:13:42     4
2021-05-31 11:37:49 2021-05-31 11:37:56     4
2021-05-31 11:37:59 2021-05-31 11:38:33     4
2021-05-31 11:16:53 2021-05-31 11:16:57     4
2021-05-31 11:13:08 2021-05-31 11:13:31     4
2021-05-31 11:39:56 2021-05-31 11:40:15     4
2021-05-31 11:45:00 2021-05-31 11:45:35     4
2021-05-31 11:40:21 2021-05-31 11:40:38     4
2021-05-31 11:41:17 2021-05-31 11:41:20     4
2021-05-31 11:44:39 2021-05-31 11:44:58     4
2021-05-31 13:32:04 2021-05-31 13:32:39     4
2021-05-31 11:44:18 2021-05-31 11:44:24     4
2021-05-31 11:42:52 2021-05-31 11:43:02     4
2021-05-31 13:33:04 2021-05-31 13:34:24     4
2021-05-31 11:20:36 2021-05-31 11:20:54     4
2021-05-31 11:48:32 2021-05-31 11:49:22     4
2021-05-31 11:23:25 2021-05-31 11:24:09     4
2021-05-31 11:22:05 2021-05-31 11:22:21     4
2021-05-31 11:17:02 2021-05-31 11:17:02     4
2021-05-31 13:51:02 2021-05-31 13:51:50     4
2021-05-31 11:22:44 2021-05-31 11:23:08     4
2021-05-31 13:34:54 2021-05-31 13:35:24     4
2021-05-31 11:43:17 2021-05-31 11:43:34     4
2021-05-31 11:24:35 2021-05-31 11:25:18     4
2021-05-31 11:18:53 2021-05-31 11:19:22     4
2021-05-31 11:25:30 2021-05-31 11:27:18     4
2021-05-31 11:50:54 2021-05-31 11:50:58     4
2021-05-31 11:50:36 2021-05-31 11:50:43     4
2021-05-31 11:19:26 2021-05-31 11:19:30     4
2021-05-31 11:19:31 2021-05-31 11:20:20     4
2021-05-31 11:17:43 2021-05-31 11:17:53     4
2021-05-31 11:51:19 2021-05-31 11:51:28     3
2021-05-31 13:31:45 2021-05-31 13:32:03     3
2021-05-31 13:34:25 2021-05-31 13:34:53     3
2021-05-31 11:53:43 2021-05-31 11:54:17     3
2021-05-31 11:52:22 2021-05-31 11:52:58     3
2021-05-31 11:50:59 2021-05-31 11:51:12     3
2021-05-31 11:50:44 2021-05-31 11:50:53     3
2021-05-31 11:50:33 2021-05-31 11:50:35     3
2021-05-31 13:35:25 2021-05-31 13:35:32     3
2021-05-31 11:50:19 2021-05-31 11:50:26     3
2021-05-31 11:54:44 2021-05-31 11:55:12     3
2021-05-31 13:36:12 2021-05-31 13:36:27     3
2021-05-31 11:49:23 2021-05-31 11:50:04     3
2021-05-31 14:01:11 2021-05-31 14:01:27     3
2021-05-31 11:12:06 2021-05-31 11:13:07     3
2021-05-31 11:13:32 2021-05-31 11:13:41     3
2021-05-31 11:17:03 2021-05-31 11:17:42     3
2021-05-31 11:17:54 2021-05-31 11:18:52     3
2021-05-31 11:19:23 2021-05-31 11:19:23     3
2021-05-31 11:23:09 2021-05-31 11:23:24     3
2021-05-31 11:25:19 2021-05-31 11:25:29     3
2021-05-31 11:42:16 2021-05-31 11:42:51     3
2021-05-31 11:27:57 2021-05-31 11:28:32     3
2021-05-31 13:55:17 2021-05-31 13:55:27     3
2021-05-31 11:11:32 2021-05-31 11:11:41     3
2021-05-31 13:51:51 2021-05-31 13:55:08     3
2021-05-31 11:09:40 2021-05-31 11:10:29     3
2021-05-31 12:21:29 2021-05-31 12:23:02     3
2021-05-31 13:47:56 2021-05-31 13:49:15     3
2021-05-31 11:34:42 2021-05-31 11:35:05     3
2021-05-31 11:41:21 2021-05-31 11:42:07     3
2021-05-31 11:40:39 2021-05-31 11:41:16     3
2021-05-31 11:40:16 2021-05-31 11:40:20     3
2021-05-31 11:35:35 2021-05-31 11:35:43     3
2021-05-31 13:49:55 2021-05-31 13:51:01     3
2021-05-31 11:38:34 2021-05-31 11:39:09     3
2021-05-31 11:39:45 2021-05-31 11:39:55     3
2021-05-31 13:30:26 2021-05-31 13:31:44     2
2021-05-31 12:55:17 2021-05-31 12:55:39     2
2021-05-31 12:33:43 2021-05-31 12:35:36     2
2021-05-31 13:29:58 2021-05-31 13:30:14     2
2021-05-31 13:29:22 2021-05-31 13:29:41     2
2021-05-31 13:16:14 2021-05-31 13:16:49     2
2021-05-31 12:23:03 2021-05-31 12:23:52     2
2021-05-31 13:04:47 2021-05-31 13:06:30     2
2021-05-31 13:36:28 2021-05-31 13:38:03     2
2021-05-31 14:01:28 2021-05-31 14:01:57     2
2021-05-31 14:00:35 2021-05-31 14:01:10     2
2021-05-31 13:55:28 2021-05-31 13:56:16     2
2021-05-31 13:55:09 2021-05-31 13:55:16     2
2021-05-31 13:35:33 2021-05-31 13:36:11     2
2021-05-31 13:49:16 2021-05-31 13:49:54     2
2021-05-31 13:42:12 2021-05-31 13:47:55     2
2021-05-31 13:38:24 2021-05-31 13:38:59     2
2021-05-31 14:02:13 2021-05-31 14:02:13     2
2021-05-31 12:20:02 2021-05-31 12:21:28     2
2021-05-31 11:09:37 2021-05-31 11:09:39     2
2021-05-31 11:51:29 2021-05-31 11:52:21     2
2021-05-31 14:02:42 2021-05-31 14:02:51     2
2021-05-31 11:50:05 2021-05-31 11:50:18     2
2021-05-31 11:35:06 2021-05-31 11:35:34     2
2021-05-31 11:34:19 2021-05-31 11:34:41     2
2021-05-31 11:50:27 2021-05-31 11:50:32     2
2021-05-31 11:51:13 2021-05-31 11:51:18     2
2021-05-31 13:27:53 2021-05-31 13:29:21     1
2021-05-31 13:29:42 2021-05-31 13:29:44     1
2021-05-31 12:17:53 2021-05-31 12:18:39     1
2021-05-31 11:56:04 2021-05-31 11:56:19     1
2021-05-31 12:39:39 2021-05-31 12:39:45     0
2021-05-31 11:56:29 2021-05-31 12:17:05     0
2021-05-31 12:17:40 2021-05-31 12:17:52     0
2021-05-31 11:29:54 2021-05-31 11:30:35     0
2021-05-31 12:26:55 2021-05-31 12:33:26     0
2021-05-31 13:29:45 2021-05-31 13:29:55     0
2021-05-31 11:32:10 2021-05-31 11:32:38     0
2021-05-31 12:55:55 2021-05-31 13:03:10     0
2021-05-31 13:39:06 2021-05-31 13:39:34     0
2021-05-31 11:56:20 2021-05-31 11:56:25     0
2021-05-31 13:13:34 2021-05-31 13:15:01     0
2021-05-31 13:16:52 2021-05-31 13:17:53     0
2021-05-31 13:20:04 2021-05-31 13:27:52     0
2021-05-31 11:55:56 2021-05-31 11:56:03     0
2021-05-31 12:18:40 2021-05-31 12:19:24     0
2021-05-31 12:40:53 2021-05-31 12:54:22     0
2021-05-31 11:55:25 2021-05-31 11:55:44     0

Related Questions in POWERSHELL

Related Questions in 3CX

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Popular Questions

Copyright © 2021 Jogjafile Inc.