VoIPosaur | Voice over IP info

Hopefully these will help some people.  They are queries to help when you are searching your callmanager CDR database

'Show all calls to and from a particular number, drop the OR statement for only calls to a number

 SELECT DATEADD([second], dateTimeOrigination - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Called_At, callingPartyNumber AS Extension, originalCalledPartyNumber, finalCalledPartyNumber, DATEADD([second], dateTimeConnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS TimeConnect, DATEADD([second], dateTimeDisconnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Time_Disconnect, duration, callingPartyNumberPartition, originalCalledPartyNumberPartition (FROM CallDetailRecord (WHERE (originalCalledPartyNumber = '1234') OR (callingPartyNumber = '1234') (ORDER BY dateTimeOrigination DESC

 'Show all 911 Calls

 SELECT DATEADD([second], dateTimeOrigination - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Called_At, callingPartyNumber AS Extension, originalCalledPartyNumber, finalCalledPartyNumber, DATEADD([second], dateTimeConnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS TimeConnect, DATEADD([second], dateTimeDisconnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Time_Disconnect, duration, callingPartyNumberPartition, originalCalledPartyNumberPartition (FROM CallDetailRecord (WHERE (originalCalledPartyNumber = '911')(ORDER BY dateTimeOrigination DESC

 'Show all DN's and their e164 Masks 

 (SELECT CCM0304.dbo.NumPlan.DNOrPattern, CCM0304.dbo.DeviceNumPlanMap.e164Mask (FROM CCM0304.dbo.NumPlan, CCM0304.dbo.DeviceNumPlanMap (WHERE CCM0304.dbo.NumPlan.pkid=CCM0304.dbo.DeviceNumPlanMap.fkNumPlanAND  CCM0304.dbo.DeviceNumPlanMap.e164Mask = '1234567890'

 

'Show all calls from phones with a particular e164 Mask

 SELECT DATEADD([second], dateTimeOrigination - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Called_At, callingPartyNumber AS Extension, originalCalledPartyNumber, finalCalledPartyNumber, DATEADD([second], dateTimeConnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS TimeConnect, DATEADD([second], dateTimeDisconnect - 32400, CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS Time_Disconnect, duration, callingPartyNumberPartition, originalCalledPartyNumberPartition (FROM CallDetailRecord (WHERE (callingPartyNumber IN (        (SELECT CCM0304.dbo.NumPlan.DNOrPattern (        FROM    CCM0304.dbo.NumPlan, CCM0304.dbo.DeviceNumPlanMap (        WHERE   CCM0304.dbo.NumPlan.pkid = CCM0304.dbo.DeviceNumPlanMap.fkNumPlan                 AND (CCM0304.dbo.DeviceNumPlanMap.e164Mask = '1234567890')) (ORDER BY dateTimeOrigination DESC