This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Dashboard for Reporting on Call Totals/Minutes & Phone System Types


I am looking to create a dashboard that tracks and reports call counts for the current and last month as well as duration (in mins) for those calls. 

It would also be ideal to be able to track which phone systems these calls are going through, along with a simple count of how many of our sites use each phone system. 

Has anyone built anything along these lines or is looking to do something similar? I could use some help writing the SWQL for these widgets. 


  • Do you want a summary for the organization, total calls and total minutes, or broken down somehow? 

  • It would be great to have it broken down by current and last month (total calls and total minutes) and then by phone system. Is that what you were asking?

  • Yup, I have a similar thing, let me dig it up. 

  • SELECT  
        Count(DisplayName) as [Call Count]
    ,   Sum(Duration) as [Call Duration in Seconds]
    , CallManagerName
    , Case 
        When Month(DateTimeOrigination) = 1 Then 'January'
        When Month(DateTimeOrigination) = 2 Then 'February'
        When Month(DateTimeOrigination) = 3 Then 'March'
        When Month(DateTimeOrigination) = 4 Then 'April'
        When Month(DateTimeOrigination) = 5 Then 'May'
        When Month(DateTimeOrigination) = 6 Then 'June'
        When Month(DateTimeOrigination) = 7 Then 'July'
        When Month(DateTimeOrigination) = 8 Then 'August'
        When Month(DateTimeOrigination) = 9 Then 'September'
        When Month(DateTimeOrigination) = 10 Then 'October'
        When Month(DateTimeOrigination) = 11 Then 'November'
        When Month(DateTimeOrigination) = 12 Then 'December'
    End as [MonthName]
    FROM Orion.IpSla.VoipCallDetails
        MONTHDIFF(ToLocal(DateTimeOrigination), Getdate()) < 3 --Comment out to change to previous week from previous month
    Group By  CallManagerName, Month(DateTimeOrigination)

    This SWQL is not exactly what you asked for, its calls, duration, and Call Manager for this month, and the 2 previous Months. You could put this into the Dash widget or a report. 

  • This is great, thank you! Do you have anything for tracking of phone system types? 

  • I don't, but maybe one of these relationships point you in the right direction? My voice team has enough coded into the CM name to figure out what they need.

    Select top 100 
    , VCD.CCMMonitoring.ClusterName
    , VCD.CCMMonitoring.MonitoringType.Code
    , VCD.CCMMonitoring.MonitoringType.Description
    , VCD.CCMMonitoring.Region.RegionName 
    FROM Orion.IpSla.VoipCallDetails VCD

  • Thank you, this is super helpful!

  • How could I now tweak this to track call duration in minutes, instead of seconds?