VNQM - Schedule a CDR Report.

vlt06

Hello, 

 

Looking to see if possible or if anyone has been able to schedule a CDR report on a re-occurring basis?

 

I can find the calls I need via the "Search VoIP Calls" section when querying all calls to a certain destination, but wondering if this can be scheduled?

 

Any help - much appreciated! 

jm_sysadmin

Absolutely! I used the SWQL below to power a report that gets sent out Monday Morning before the recipients come in. This is the template I used for several that go out. Typically to get reports on entire teams, but you can trim it down to just one extension. Once you get it in a report the way you want it, just go to the report, edit it and use the Schedule Report tab to send it out. I attach a PDF for them to save since I don't keep the data around forever. 

SELECT  
    DisplayName as [Call]
,   ToLocal(DateTimeOrigination)  as [Call Start Time]
,   ToLocal(DateTimeDisconnect)  as [Call End Time]
, CASE 
       When (DestCCMPhoneExtension like '00001' or OrigCCMPhoneExtension like '00001') Then '550-0001 Leon Adato'
       When (DestCCMPhoneExtension like '00002' or OrigCCMPhoneExtension like '00002') Then '550-0002 Kevin Sparenberg'
       When (DestCCMPhoneExtension like '00003' or OrigCCMPhoneExtension like '00003') Then '550-0002 Cafeteria Pizza Station'

End as [Extension]
, CASE  
    When (OrigCCMPhoneExtension is not Null) Then 'Outbound'
    Else 'InBound'
End as [Inbound / Outbound]
,   Duration as [Call Duration in Seconds]
,  OrigDeviceName  as [Call Origin Phone]
,  Case 
    When (OrigCCMPhoneExtension is Null ) Then CallingPartyNumber
    Else OrigCCMPhoneExtension
End as [Call Origin Extension]


, Case 
    When DestCCMPhoneExtension like '00000' Then 'Voice Mailbox'   
    When OriginalCalledPartyNumber like '00000' Then 'Voice Mailbox'
    When (DestDeviceName is Null or DestDeviceName like '' ) Then 'External Call' 
    When Length(DestCCMPhoneExtension) > 5 Then 'External Call'
    When Length(OriginalCalledPartyNumber) > 5 Then 'External Call'
    Else DestDeviceName
End as [Call Destination Phone]

,  Case
    When (DestCCMPhoneExtension is Null ) Then OriginalCalledPartyNumber
    Else DestCCMPhoneExtension
End as [Call Destination Extension]


,  Case 
    When WeekDay(Tolocal(DateTimeOrigination)) = 0 Then 'Sunday'
    When WeekDay(Tolocal(DateTimeOrigination)) = 1 Then 'Monday'
    When WeekDay(Tolocal(DateTimeOrigination)) = 2 Then 'Tuesday'
    When WeekDay(Tolocal(DateTimeOrigination)) = 3 Then 'Wednesday'
    When WeekDay(Tolocal(DateTimeOrigination)) = 4 Then 'Thursday'
    When WeekDay(Tolocal(DateTimeOrigination)) = 5 Then 'Friday'
    When WeekDay(Tolocal(DateTimeOrigination)) = 6 Then 'Saturday'
End as [Day of the Week]

FROM Orion.IpSla.VoipCallDetails
where  
    DayDiff(ToLocal(DateTimeOrigination), Getdate() ) < 8 
    and 
    DayDiff(ToLocal(DateTimeOrigination), Getdate() ) > 0 
    and 
   (DestCCMPhoneExtension like '00001'
    or DestCCMPhoneExtension like '00002'
    or DestCCMPhoneExtension like '00003'
    or OrigCCMPhoneExtension like '00001'
    or OrigCCMPhoneExtension like '00002'
    or OrigCCMPhoneExtension like '00003' )