Search code examples
mysqldatabaseshoretel

ShoreTel MySQL Querying

Background information:

My company uses ShoreTel for our VoIP service; while their canned reports are great, they have information that is not needed to us and the presentation is not the easiest to consume the data. I need to create a single report on all Sales calls that ultimately combine two canned reports that ShoreTel provides -- Individual and Group reports; the reason for combining the two reports is to have one Pivot Table that the Sales Manager can view and quickly digest each Salesperson/groups calls. I have reversed engineered the database in MySQL Workbench and have found all tables and columns that should return the specific data I want. The three tables and the columns are listed below are the ones that produce the data I am looking to use in my Report/Pivot Table:

agentactivity                   |   Connect                     |   Call
-------------                       ---------                       ------
agentactivity.StartTimeStamp    |   connect.PartyTable          |   call.CallType
agentactivity.EndTimeStamp      |   connect.ConnectReason       |   call.DialedNumber
agentactivity.AgentDN           |   connect.PartyID             |   call.CallerID
agentactivity.AgentFirstName    |   connect.PartyIDName         |   call.Duration
agentactivity.AgentLastName     |   connect.Duration            |   call.Extension 
agentactivity.State             |   connect.TalkTimeSeconds     |
agentactivity.WorkgroupDN       |                               |
agentactivity.WorkgroupName     |                               |

What I need assistance with, as I am a novice to working with MySQL, is to get a query built that I can use to mine out the data I want; I can build individual queries that get me part of the data (shown below), but when it comes to using two (let alone three) tables, that is where it all starts to crumble. I have attempted my own query, though I want to make sure that this is correct regarding syntax and usability. As stated above, the point of this is to create a single Pivot Table with all the required information out of the database.

Previously read/attempted:

http://stackoverflow.com/questions/14191506/how-to-join-two-tables <-Helped a little but not a lot; still confused.

http://stackoverflow.com/questions/22418525/shoretel-outgoing-calls-query <--Provide more information; was able to build off this one on my own, only able to use for single results.

http://stackoverflow.com/questions/20405898/multiple-select-case-staments-in-query-for-shoretel-reports?rq=1 <--Was not what I needed for my task.

https://community.spiceworks.com/how_to/69423-shoretel-call-detail-record-lookup <--Most helpful yet and I tried customizing this to suit my needs; failed.

https://www.experts-exchange.com/questions/28286779/Shoretel-SQL-query-in-Excel.html <--Was not useful in anyway as I need the correct query to dump data into Excel.

http://dev.mysql.com/doc/refman/5.7/en/join.html <--This is where I started regarding 'Joins' for tables but it starts to get confusing.

My queries for individual data returns (to be honest, these were not my queries originally, I customized them to suit my needs):

SELECT PartyIDName,GroupID,ConnectTime,DisconnectTime,ConnectReason 
FROM shorewarecdrarchive.`connect` 
WHERE PartyType=12;

SELECT StartTime,DialedNumber,CallerID,EndTime,Extension,CallType 
FROM shorewarecdrarchive.`call` 
WHERE Extension=6333 AND date(StartTime) BETWEEN '2016-08-01' AND '2016-08-16' AND CallType=3;

SELECT ID, StartTime, StartTimeMS, EndTime, EndTimeMS, TIMEDIFF(EndTime,StartTime) AS `CallDuration`, LongDistance, CallType, CallerID, DialedNumber, Extension 
FROM shorewarecdrarchive.`call` 
WHERE Extension=6317 DATE(StartTime) = '2016-09-01' AND TIME(StartTime) >= '00:00:00' AND TIME(StartTime) <= '23:59:59' ORDER BY CallType,StartTime;

My attempt at the query using more than 1 table:

SELECT
    call.CallType,
    call.DialedNumber,
    call.CallerID,
    call.Duration,
    connect.PartyTable,
    connect.ConnectReason,
    connect.PartyID,
    connect.PartyIDName,
    connect.PartyIDLastName,
    connect.Duration,
    connect.TalkTimeSeconds,
    agentactivity.StartTimeStamp,
    agentactivity.EndTimeStamp,
    agentactivity.AgentDN,
    agentactivity.AgentFirstName,
    agentactivity.AgentLastName,
    agentactivity.State,
    agentactivity.WorkgroupDN,
    agentactivity.WorkgroupName 
FROM 
    shorewarecdrarchive.`call` 
    INNER JOIN shorewarecdrarchive.`connect` 
        ON connect.CallTableID=call.ID 
    INNER JOIN shorewarecdrarchive.'agentactivity' 
        ON call.Extension=agentactivity.AgentDN 
WHERE 
    TIME(StartTime) >= '00:00:00' 
    AND TIME(StartTime) <= '23:59:59' BETWEEN '2016-09-01' AND '2016-09-16' 
ORDER BY 
    CallType,StartTime;

Any and all assistance on this will be greatly appreciated!

Solution

  • Thank you to mcNets for the assistance on this issue I was having. The below query works and can be used to mine out all data that would be in the canned Individual & Group Reports.

    SELECT 
    call.CallType,
    call.DialedNumber,
    call.CallerID,
    connect.PartyType,
    connect.ConnectReason,
    connect.PartyID,
    connect.PartyIDName,
    connect.PartyIDLastName,
    connect.TalkTimeSeconds,
    agentactivity.StartTimeStamp,
    agentactivity.EndTimeStamp,
    agentactivity.AgentDN,
    agentactivity.AgentFirstName,
    agentactivity.AgentLastName,
    agentactivity.State,
    agentactivity.WorkgroupDN,
    agentactivity.WorkgroupName
FROM
    shorewarecdrarchive.call
LEFT JOIN
    shorewarecdrarchive.connect ON connect.CallTableID = call.ID
LEFT JOIN
    shorewarecdrarchive.agentactivity ON call.Extension = agentactivity.AgentDN
WHERE
    StartTime >= '2016-09-01 00:00:00'
AND StartTime < '2016-09-17 00:00:00';

    Thank you once again for all your help.