I’ve been working on a query to pull total calls. For each call there can be multiple rows of data. Each call has a unique id but it seems that depending on how the call is routed there can be 1 call with multiple unique id’s (this is an assumption). I was told that all calls that come in from our 800 numbers will had a dcontext of ‘from-did-direct’. In some cases this is true. I’ve also found that most calls have a dcontext of ‘ext-queues’ and ‘from-internal’. This leads me to believe that i should filter out everything other than ‘from-did-direct’ and ‘ext-queues’.
Sample Call log:
3/28/11 2:39 PM S11-8622353941 <8622353941> 8622353941 1003 from-internal Local/1003@from-internal-5c4b,2 SIP/1003-08f5c250 Dial SIP/1003|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301337594
3/28/11 2:39 PM S11-8622353941 <8622353941> 8622353941 1001 from-internal Local/1001@from-internal-361b,2 SIP/1001-b2b93860 Dial SIP/1001|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301337594
3/28/11 2:39 PM S11-8622353941 <8622353941> 8622353941 655 ext-queues Zap/22-1 Local/1002@from-internal-4261,1 Queue 655|tr|||30 70 70 ANSWERED 1301337594
3/28/11 2:52 PM S11-5167490815 <5167490815> 5167490815 1003 from-internal Local/1003@from-internal-82fa,2 SIP/1003-08fe3c40 Dial SIP/1003|25|WwtrM(auto-blkvm) 7 0 NO ANSWER 1301338358
3/28/11 2:52 PM S11-5167490815 <5167490815> 5167490815 1001 from-internal Local/1001@from-internal-8f07,2 SIP/1001-090b3238 Dial SIP/1001|25|WwtrM(auto-blkvm) 7 0 NO ANSWER 1301338358
3/28/11 2:52 PM S11-5167490815 <5167490815> 5167490815 655 ext-queues Zap/21-1 Local/1002@from-internal-7ed8,1 Queue 655|tr|||30 190 190 ANSWERED 1301338358
3/28/11 3:08 PM S11-2096885924 <2096885924> 2096885924 655 ext-queues Zap/23-1 Local/1003@from-internal-9823,1 Queue 655|tr|||30 245 245 ANSWERED 1301339305
3/28/11 3:08 PM S11-2096885924 <2096885924> 2096885924 1001 from-internal Local/1001@from-internal-9de7,2 SIP/1001-08f3fab8 Dial SIP/1001|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301339306
3/28/11 3:08 PM S11-2096885924 <2096885924> 2096885924 1002 from-internal Local/1002@from-internal-d196,2 SIP/1002-09058ee0 Dial SIP/1002|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301339306
3/28/11 3:11 PM S10-4052079668 <4052079668> 4052079668 1003 from-internal Local/1003@from-internal-85ad,2 SIP/1003-090b3238 Dial SIP/1003|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301339504
3/28/11 3:11 PM S10-4052079668 <4052079668> 4052079668 1001 from-internal Local/1001@from-internal-f7c5,2 SIP/1001-090854b8 Dial SIP/1001|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301339504
3/28/11 3:11 PM S10-4052079668 <4052079668> 4052079668 655 ext-queues Zap/20-1 Local/1002@from-internal-73fe,1 Queue 655|tr|||30 89 89 ANSWERED 1301339504
3/28/11 3:20 PM S11-6626487322 <6626487322> 6626487322 1002 from-internal Local/1002@from-internal-fe67,2 SIP/1002-08f8a440 Dial SIP/1002|25|WwtrM(auto-blkvm) 3 0 NO ANSWER 1301340017
3/28/11 3:20 PM S11-6626487322 <6626487322> 6626487322 1001 from-internal Local/1001@from-internal-df3c,2 SIP/1001-08fe3c40 Dial SIP/1001|25|WwtrM(auto-blkvm) 3 0 NO ANSWER 1301340017
3/28/11 3:20 PM S11-6626487322 <6626487322> 6626487322 655 ext-queues Zap/23-1 Local/1003@from-internal-914f,1 Queue 655|tr|||30 209 209 ANSWERED 1301340017
3/28/11 4:17 PM S10-2294155220 <2294155220> 2294155220 655 ext-queues Zap/23-1 Local/1003@from-internal-fd8a,1 Queue 655|tr|||30 755 754 ANSWERED 1301343438
3/28/11 4:17 PM S10-2294155220 <2294155220> 2294155220 1001 from-internal Local/1001@from-internal-dbf3,2 SIP/1001-08f5c250 Dial SIP/1001|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301343439
3/28/11 4:17 PM S10-2294155220 <2294155220> 2294155220 1002 from-internal Local/1002@from-internal-8895,2 SIP/1002-090854b8 Dial SIP/1002|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301343439
3/28/11 6:56 PM S10-3234475447 <3234475447> 3234475447 1001 from-internal Local/1001@from-internal-fe8f,2 SIP/1001-090b3238 Dial SIP/1001|25|WwtrM(auto-blkvm) 6 0 NO ANSWER 1301352984
3/28/11 6:56 PM S10-3234475447 <3234475447> 3234475447 1003 from-internal Local/1003@from-internal-ea30,2 SIP/1003-08ed6ce8 Dial SIP/1003|25|WwtrM(auto-blkvm) 6 0 NO ANSWER 1301352984
3/28/11 6:56 PM S10-3234475447 <3234475447> 3234475447 655 ext-queues Zap/23-1 Local/1002@from-internal-0774,1 Queue 655|tr|||30 707 707 ANSWERED 1301352984
3/29/11 10:39 AM S11-4234040582 <4234040582> 4234040582 655 ext-queues Zap/21-1 Local/1003@from-internal-769c,1 Queue 655|tr|||30 209 208 ANSWERED 1301409543
3/29/11 10:39 AM S11-4234040582 <4234040582> 4234040582 1002 from-internal Local/1002@from-internal-0264,2 SIP/1002-b3cf8e20 Dial SIP/1002|25|WwtrM(auto-blkvm) 6 0 NO ANSWER 1301409544
3/29/11 10:39 AM S11-4234040582 <4234040582> 4234040582 1001 from-internal Local/1001@from-internal-bae0,2 SIP/1001-090854b8 Dial SIP/1001|25|WwtrM(auto-blkvm) 6 0 NO ANSWER 1301409544
3/29/11 12:10 PM S11-4844805766 <4844805766> 4844805766 655 ext-queues Zap/18-1 Local/1002@from-internal-36c5,1 Queue 655|tr|||30 704 703 ANSWERED 1301415048
3/29/11 12:10 PM S11-4844805766 <4844805766> 4844805766 1003 from-internal Local/1003@from-internal-7cad,2 SIP/1003-08ed6ce8 Dial SIP/1003|25|WwtrM(auto-blkvm) 2 0 NO ANSWER 1301415049
3/29/11 12:10 PM S11-4844805766 <4844805766> 4844805766 1001 from-internal Local/1001@from-internal-d1ee,2 SIP/1001-b2b4fd08 Dial SIP/1001|25|WwtrM(auto-blkvm) 2 0 NO ANSWER 1301415049
3/29/11 12:12 PM S11-8322062548 <8322062548> 8322062548 1002 from-internal Local/1002@from-internal-def5,2 SIP/1002-090fc180 Dial SIP/1002|25|WwtrM(auto-blkvm) 4 0 NO ANSWER 1301415159
3/29/11 12:12 PM S11-8322062548 <8322062548> 8322062548 1001 from-internal Local/1001@from-internal-f083,2 SIP/1001-0908b950 Dial SIP/1001|25|WwtrM(auto-blkvm) 4 0 NO ANSWER 1301415159
3/29/11 12:12 PM S11-8322062548 <8322062548> 8322062548 655 ext-queues Zap/16-1 Local/1003@from-internal-6519,1 Queue 655|tr|||30 1793 1793 ANSWERED 1301415159
3/29/11 12:35 PM S11-2819487425 <2819487425> 2819487425 1001 from-internal Local/1001@from-internal-b2a3,2 SIP/1001-08f8a440 Dial SIP/1001|25|WwtrM(auto-blkvm) 3 0 NO ANSWER 1301416539
3/29/11 12:35 PM S11-2819487425 <2819487425> 2819487425 1003 from-internal Local/1003@from-internal-ab36,2 SIP/1003-090b3238 Dial SIP/1003|25|WwtrM(auto-blkvm) 3 0 NO ANSWER 1301416539
3/29/11 12:35 PM S11-2819487425 <2819487425> 2819487425 655 ext-queues Zap/23-1 Local/1002@from-internal-eedc,1 Queue 655|tr|||30 265 265 ANSWERED 1301416539
3/29/11 12:59 PM S11-7196712901 <7196712901> 7196712901 1002 from-internal Local/1002@from-internal-9248,2 SIP/1002-b2b14038 Dial SIP/1002|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301417944
3/29/11 12:59 PM S11-7196712901 <7196712901> 7196712901 1001 from-internal Local/1001@from-internal-914d,2 SIP/1001-09099780 Dial SIP/1001|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301417944
3/29/11 12:59 PM S11-7196712901 <7196712901> 7196712901 655 ext-queues Zap/23-1 Local/1003@from-internal-9c42,1 Queue 655|tr|||30 285 285 ANSWERED 1301417944
3/29/11 1:02 PM S10-2259375634 <2259375634> 2259375634 1001 from-internal Local/1001@from-internal-b2f5,2 SIP/1001-b4210b68 Dial SIP/1001|25|WwtrM(auto-blkvm) 9 0 NO ANSWER 1301418126
3/29/11 1:02 PM S10-2259375634 <2259375634> 2259375634 1003 from-internal Local/1003@from-internal-a2a3,2 SIP/1003-b2921890 Dial SIP/1003|25|WwtrM(auto-blkvm) 9 0 NO ANSWER 1301418126
3/29/11 1:02 PM S10-2259375634 <2259375634> 2259375634 655 ext-queues Zap/21-1 Local/1002@from-internal-e492,1 Queue 655|tr|||30 300 300 ANSWERED 1301418126
3/29/11 2:31 PM S10-2259375634 <2259375634> 2259375634 655 ext-queues Zap/21-1 Local/1002@from-internal-7f94,1 Queue 655|tr|||30 96 95 ANSWERED 1301423480
3/29/11 2:31 PM S10-2259375634 <2259375634> 2259375634 1003 from-internal Local/1003@from-internal-fabc,2 SIP/1003-08f8a440 Dial SIP/1003|25|WwtrM(auto-blkvm) 7 0 NO ANSWER 1301423481
3/29/11 2:31 PM S10-2259375634 <2259375634> 2259375634 1001 from-internal Local/1001@from-internal-4584,2 SIP/1001-b292ba88 Dial SIP/1001|25|WwtrM(auto-blkvm) 7 0 NO ANSWER 1301423481
3/29/11 2:31 PM S10-2259375634 <2259375634> 2259375634 1002 from-internal Local/1002@from-internal-7f94,2 SIP/1002-b3cf4ef8 Dial SIP/1002|25|WwtrM(auto-blkvm) 95 88 ANSWERED 1301423481
3/29/11 6:00 PM S10-2259375634 <2259375634> 2259375634 1001 from-internal Local/1001@from-internal-e538,2 SIP/1001-091102d0 Dial SIP/1001|25|WwtrM(auto-blkvm) 6 0 NO ANSWER 1301436000
3/29/11 6:00 PM S10-2259375634 <2259375634> 2259375634 1002 from-internal Local/1002@from-internal-4b0f,2 SIP/1002-09088290 Dial SIP/1002|25|WwtrM(auto-blkvm) 6 0 NO ANSWER 1301436000
3/29/11 6:00 PM S10-2259375634 <2259375634> 2259375634 655 ext-queues Zap/23-1 Local/1003@from-internal-00b2,1 Queue 655|tr|||30 264 264 ANSWERED 1301436000
3/30/11 10:16 AM S10-8472464432 <8472464432> 8472464432 1003 from-internal Local/1003@from-internal-c419,2 SIP/1003-08e09920 Dial SIP/1003|25|WwtrM(auto-blkvm) 8 0 NO ANSWER 1301494600
3/30/11 10:16 AM S10-8472464432 <8472464432> 8472464432 1002 from-internal Local/1002@from-internal-e8d1,2 SIP/1002-b2bccc58 Dial SIP/1002|25|WwtrM(auto-blkvm) 8 0 NO ANSWER 1301494600
3/30/11 10:16 AM S10-8472464432 <8472464432> 8472464432 655 ext-queues Zap/21-1 Local/1001@from-internal-f10b,1 Queue 655|tr|||30 555 555 ANSWERED 1301494600
3/30/11 1:58 PM S11-3147764955 <3147764955> 3147764955 1002 from-internal Local/1002@from-internal-4ab7,2 SIP/1002-09098ba8 Dial SIP/1002|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301507899
3/30/11 1:58 PM S11-3147764955 <3147764955> 3147764955 1001 from-internal Local/1001@from-internal-c3e1,2 SIP/1001-09126110 Dial SIP/1001|25|WwtrM(auto-blkvm) 5 0 NO ANSWER 1301507899
3/30/11 1:58 PM S11-3147764955 <3147764955> 3147764955 655 ext-queues Zap/23-1 Local/1003@from-internal-478e,1 Queue 655|tr|||30 734 734 ANSWERED 1301507899
[size=50][/size]
SQL Query:
Insert Into @PBX (Prepend,calldate,clid,src)
select * from openquery(PBXCDR,'
SELECT ''S10'', calldate, clid, src FROM cdr
WHERE clid LIKE ''%S10-%''
AND dcontext IN (''ext-queues'',''from-did-direct'') AND disposition IN (''ANSWERED'', '''')
order by calldate
')
Any help on how i should be filtering through the data or definitions of these dcontext’s and what i can gain from them would be greatly appreciated.