This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

Hello,

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. 

Thanks! 

Parents
  • 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
    where  
        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. 

Reply
  • 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
    where  
        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. 

Children