Solved

Query Development:  Overlapping with Call Log Data

Posted on 2014-12-19
13
171 Views
Last Modified: 2015-01-15
Greetings, Experts!

I have an Access DB that records SMDR call log data from our company’s Avaya system.  (This Access DB is used with a commercial product used to collect the data.)

With that said, the brass has handed me a task that I cannot seem to grasp.  Some folks, internal and external, are complaining that there are many times that they cannot call out or call in.  This naturally gets the attention of the brass.
The Avaya system does not record busy signals, nor data if an internal user fails to get a line out.  The only thing that I’ve got to go on is the data for successful phone calls (in & outbound).

ID   |      Call_Time       |   Duration (s)   |   Call_Type   |   Extension...
1          20141219  11:20:44 AM             127                 Inbound                207
2          20141219  11:21:07 AM             603                 Inbound                208
3          20141219  11:31:12 AM              47                  Inbound                213


To be able to make a decision about how many extra new phone lines to order, it would be nice to know when there is an “overlapping” of more than one line at any time.  

Between [20141219  11:20:44 AM] and [20141219  11:22:20 AM] 4 lines were in use.
Between [20141219  11:21:07 AM] and [20141219  11:24364 AM] 5 lines were in use.
Between [20141219  11:38:41 AM] and [20141219  11:43:17 AM] 4 lines were in use.


Not sure if this is the “best” way of displaying the data or not, as this is my first stab at genning up a solution for the brass.
Experts, what would be an Access Query that would generate this kind of result?

Thank you, thank you, thank you!

Todd
0
Comment
Question by:todjklki
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
I don't have an Access SQL answer for you, but I have written a SQL Server article called T-SQL: Identify bad dates in a time series, where one of the definitions of 'bad dates' in the big honkin' CTE expression at the bottom of the article is overlapping dates.

Good luck.
0
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
Can you post a sample of the SMDR records please. We don't need the sensitive bits (phone and extension numbers) and user names could be anonymised.
I think the SMDR record includes both a start and end time? Not sure about the product you're using. I run a SMDR service that collects the raw format data from our Avaya, but I'm not at work at the moment.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Access Query
I am sure that there'll be folks who will want to stab at that.
I wouldn't hazard a guess about whether you could or not.

I'd code to transform the data with VBA.
What you really want is a table with one record for each second on working time between the start and end of the analysis.
Each record (each second) has a field for each extension.
Each field contains a 1 or a 0 depending on busy or not
You can then sum all the fields in each record for a 'total lines busy' value
You can then kick it out to Excel and graph time against 'total lines busy' for the PHBs.

You'd build the table and code to fill it all with zeroes.
Then for each record like
20141219  11:20:44 AM             127                 Inbound                207
You'd find 2014-12-19  11:20:44 AM  and run through a loop 127 times changing field 207 from zero to 1 and moving to the next record

Could you do that in an Access query?
Hard to say.
Interval data is a bitch.
0
 

Author Comment

by:todjklki
Comment Utility
Thanks for the replies!

An Excel file is attached, Simon, with sample data.  Unfortunately, there is no end time in the source table.  But, this is nothing that can't be handled.

Gotcha on VBA, Nick.  Not exactly sure how to approach that, as I'm a bit rusty.  I think that I understand what you're driving at.
SMDR.xlsx
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
How many extensions, with what numbers?
I've built the first bit, guessing at extensions 210 thru 217

What you really want is a table with one record for each second on working time between the start and end of the analysis.
You'd build the table and code to fill it all with zeroes.
0
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
Some fairly unstructured thoughts on this:

1. Do we need to strip out internal calls from the SMDR records - only calls that involve external trunks that need counting, or report on them separately
e.g. (not based on your sample data)
"at 2014-12-14 10:10:13 there were 4 calls using external trunks and 5 internal calls."
"at 2014-12-14 10:10:45 there were 3 calls using external trunks and 2 internal calls."

2. Depending on which Avaya system you have, you might have System Status Monitor or something else that can report peak and current utilisation levels (trunks, voicemail channels etc) and exception counts. I only know the IP Office product, but that definitely displays stats on failed calls  with text like 'no trunk available'.

3. As you're using an external tool to get the SMDR, it may well also pick up the exception log entries?

4. Nick's approach looks workable. I'm guessing you have a far larger number of extensions though? Our IP office handles up to 1000 extensions I think and conference calls for large number of parties.

5. Apart from adding lines, it's worth looking at items like auto-attendant menu systems and voicemail setup, because they use trunks while active. Is the programming of those items sound and hardened against accidental or malicious misuse - e.g. auto-attendant system that can be made to loop endlessly, over-long messages.

6. Are you maxed out on PRI or BRI ISDN or can you easily add VoIP trunks?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
Here's a very low-tech Excel solution.
Excel analysis of SMDR file, showing number of external and internal concurrent calls.columns shaded green in the screenshot - I've done text to columns to convert to date/time.
columns shaded orange in the screenshot - columns where I've added formulae.
To do this:
1. Open in Excel
2. Sort by Col B (Call time)
3. Use  Data/text to columns, one column at a time, on col B and then D, using default 'General' to convert string values to date/time.
4. insert formula in col AI, row 2 (=B2+D2) to get end time for each call
5. insert formula in col AJ, row 2 to get the number of subsequent calls that have started before this one finished.
=COUNTIFS(B3:B27,"<" &AI2,G3:G27,"<>" & "INT") & " external and " & COUNTIFS(B3:B27,"<" &AI2,G3:G27,"=" & "INT") & " internal"
Notes: the formula range for the countif should run from one row BELOW the current row to the last row of data on your spreadsheet.

SMDR.xlsx

You would probably want to use two separate columns for external and internal calls and then sum them if required.
i.e.
=COUNTIFS(B3:B27,"<" &AI2,G3:G27,"<>" & "INT") in col AJ
=COUNTIFS(B3:B27,"<" &AI2,G3:G27,"=" & "INT")  in col AK
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
Comment Utility
CountIfs might be ok.  Lots of extensions might make for ugly formulas
Anyways,
Here's a sample in Access with the export to Excel.
There's a couple of tables, a form and a couple of queries
I imported your spreadsheet, and built the table tblSeconds.
The code processes SMDR and toggles fields to 1 from zero for the duration of the call
The one query totals all the extension values.
The second is the query to export data to Excel.

I pasted the data into Excel and built the chart.

Looking now, the sample doesn't have the right code for the processing.
Comparing datetimes is such a pain!
I finally had to coerce them to doubles and round to 6 figures!

Replace  cmdProcess_Click with
Private Sub cmdProcess_Click()
Dim x As Integer
Dim rs As Recordset
Dim TheDate As Date
Dim strCriteria As String
Set rs = CurrentDb.OpenRecordset("select * from tblSeconds;", dbOpenDynaset, dbSeeChanges)

Dim rs1 As Recordset
Set rs1 = CurrentDb.OpenRecordset("select * from SMDR;", dbOpenDynaset, dbSeeChanges)

'walk down rs1
Do Until rs1.EOF
    ' go to the start of the call
    TheDate = CDate(Replace(rs1!Call_Time, Chr(39), ""))
    strCriteria = "Round(CDbl([TheSecond]), 6) = " & Round(CDbl(TheDate), 6)
    rs.FindFirst strCriteria
    If rs.NoMatch = True Then GoTo skip
    'MsgBox rs!TheSecond
    For x = 1 To rs1!Duration_s
        rs.Edit
        rs.Fields(rs1!EXT).Value = 1
        rs.Update
        rs.MoveNext
    Next x
skip:
    rs1.MoveNext
Loop

MsgBox "processed!"

End Sub

Open in new window

Busy.mdb
Busy.xls
0
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
@Nick: Using the spreadsheet method I posted, It's the same number of columns of formulae regardless of the number of extensions, and the formulae themselves are the same as they are not dependent on the number of extensions either.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Good enough, and less complex then!
I run O2003 so countifs aren't an option.
0
 
LVL 18

Accepted Solution

by:
SimonAdept earned 250 total points
Comment Utility
@NIck: Your solution calculates actual concurrency whereas my method just shows the number of overlapping calls.
e.g. for a one hour call starting at 12:00, and several other subsequent short calls between 12:00 and 13:00, mine would count all the subsequent calls as overlapping with the first call, even if they weren't concurrent with each other, so it gives an idea of potential rather than actual concurrency.

Similarly, the query below (which is slightly more accurate than the Excel method because it doesn't just look at subsequent calls) can only report the number of overlapping external and internal calls rather than true concurrency:
SELECT t1.id,t1.call_time as StartTime,t1.duration,t1.call_type, t1.call_time + t1.duration as EndTime
,(SELECT COUNT(*) 
    FROM SMDR T2
    WHERE t2.call_time < (t1.call_time + t1.duration) 
    and (t2.call_time + t2.duration)>t1.call_time 
    AND t2.CALL_TYPE <>'INT'
   ) AS EXT_Overlaps
,(SELECT COUNT(*) 
    FROM SMDR T3
    WHERE t3.call_time < (t1.call_time + t1.duration) 
    and (t3.call_time + t3.duration)>t1.call_time 
    AND t3.CALL_TYPE ='INT'
   ) AS INT_Overlaps
FROM SMDR AS T1
ORDER BY T1.CALL_TIME

Open in new window


outputs something like this (I've chopped the datetimes and column titles to make it display on EE):
ID 	STARTTIME 	DURATION 	TYPE 	ENDTIME 	Ext_OL 	Int_OL
39361 	11:47:48 	00:20:59 	O 	12:08:47 	4 	3
39360 	11:48:01 	00:00:06 	I 	11:48:07 	3 	0
39362 	11:48:01 	00:01:28 	I 	11:49:29 	3 	0
39369 	11:53:49 	00:00:00 	INT 	11:53:49 	1 	0
39370 	12:01:09 	00:00:00 	I 	12:01:09 	1 	0
39371 	12:04:11 	00:01:03 	INT 	12:05:14 	1 	2
39372 	12:04:11 	00:01:03 	INT 	12:05:14 	1 	2

Open in new window


In summary, Nick's method is exact, and might just need modifying to distinguish internal calls from those using trunks. My overlap method may be easier to implement for a broad overview (e.g. looking at weeks or months worth of SMDR data at a time) because it doesn't need all the extensions to be defined in the table, but peak periods  might then need to be examined using Nick's method to establish how many concurrent trunks were used by the overlapping external calls.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@SimonAdept
As an Access guy, there are few occasions I can think of that lend themselves to using code to build tables.
This could well be one of them.
We want all the extensions listed in EXT of the SMDR data to be columns in the table.
A guy could calculate concurrency by minute rather than by second with some adjustment to the code.
That'd make for a prettier graph.
Excel 2003 complains that 32000 points are the max for a graph.

Nick67
0
 

Author Closing Comment

by:todjklki
Comment Utility
Excellent, everyone.  Thank you!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now