[cisco-voip] Finding out what are error records in CAR in 5.1.2

[Wes Sisk]

Hi Erick,

You can use the 'first 1 *' syntax to get an overview of the table schema:

admin:run sql select first 1 * from car:tbl_billing_data
cdrrecordtype globalcallid_callmanagerid globalcallid_callid 
origlegcallidentifier datetimeorigination orignodeid origspan origipaddr 
callingpartynumber callingpartyunicodeloginuserid origcause_location 
origcause_value origprecedencelevel origmediatransportaddress_ip 
origmediatransportaddress_port origmediacap_payloadcapability 
origmediacap_maxframesperpacket origmediacap_g723bitrate 
origvideocap_codec origvideocap_bandwidth origvideocap_resolution 
origvideotransportaddress_ip origvideotransportaddress_port 
destlegidentifier destnodeid destspan destipaddr 
originalcalledpartynumber finalcalledpartynumber 
finalcalledpartyunicodeloginuserid destcause_location destcause_value 
destprecedencelevel destmediatransportaddress_ip 
destmediatransportaddress_port destmediacap_payloadcapability 
destmediacap_maxframesperpacket destmediacap_g723bitrate 
destvideocap_codec destvideocap_bandwidth destvideocap_resolution 
destvideotransportaddress_ip destvideotransportaddress_port 
datetimeconnect datetimedisconnect lastredirectdn pkid 
originalcalledpartynumberpartition callingpartynumberpartition 
finalcalledpartynumberpartition lastredirectdnpartition duration 
origdevicename destdevicename origcallterminationonbehalfof 
destcallterminationonbehalfof origcalledpartyredirectonbehalfof 
lastredirectredirectonbehalfof origcalledpartyredirectreason 
lastredirectredirectreason destconversationid globalcallid_clusterid 
joinonbehalfof comment seq_id load_id charge_to user_id manager_id 
charge call_classification call_type datetimestamporigination 
datetimestampconnect datetimestampdisconnect orignumberpacketssent 
orignumberoctetssent orignumberpacketsreceived orignumberoctetsreceived 
orignumberpacketslost origjitter origlatency origquality_of_service 
destnumberpacketssent destnumberoctetssent destnumberpacketsreceived 
destnumberoctetsreceived destnumberpacketslost destjitter destlatency 
destquality_of_service authcodedescription authorizationlevel 
clientmattercode origvarvqmetrics destvarvqmetrics callsecuredstatus 
origconversationid


I suspect callingpartynumber will provide what you need for outbound 
calls.  I would try:
run sql select count(*) from car:tbl_billing_data where 
callingpartynumber like '%4515'

for example to find a count of calls from my extension.

/Wes

Erick Bergquist wrote:
> Thanks.
>
> Is there a way to search the car table on extension rather then user
> id? These don't have user id's tied to them that I can find.
>
> The error codes are all 31110 , or  31147, or 31148
>
> Is there a  keyword on the run statement to do the records starting at end?
>
> On Mon, Apr 7, 2008 at 7:43 PM, Wes Sisk <wsisk at cisco.com> wrote:
>   
>> Hi Erick,
>>
>>  The original "CDRInsert" process is replaced by the CAR loader.  CAR
>> attempts to load the flat files directly into database and inserts errors
>> into tbl_billing_error table.
>>
>>  Helpful statements from the CLI:
>>  run sql select count(*) from car:tbl_billing_data where user_id='abc'
>>  run sql select count(*) from car:tbl_billing_data where
>> callingpartyunicodeloginuserid='abc'
>>  run sql select count(*) from car:tbl_billing_data where
>> finalcalledpartyunicodeloginuserid='abc'
>>  run sql select * from car:tbl_billing_data where user_id='abc'
>>  run sql select * from car:tbl_billing_data where
>> callingpartyunicodeloginuserid='abc'
>>  run sql select * from car:tbl_billing_data where
>> finalcalledpartyunicodeloginuserid='abc'
>>
>>  then for the tbl_billing_error table:
>>  run sql select first 1 * from car:tbl_billing_error
>>  run sql select first 1 * from car:tbl_error_id_map
>>
>>  Here are the decodes for error_code:
>>  CDR:
>>  31101: CDR's globalCallID_callManagerId <= 0
>>  31102: CDR's globalCallID_callId <= 0
>>  31103: CDR's origLegCallIdentifier <= 0
>>  31105: CDR's dateTimeOrigination <= 0
>>  31108: CDR's destLegIdentifier <= 0
>>  31110: CDR's dateTimeConnect <= 0
>>  31111: CDR's dateTimeDisconnect <= 0
>>  31122: CDR's duration < 0
>>  31147: CDR's origDeviceName is empty
>>  31148: CDR's destDeviceName is empty
>>  31151: CDR's origCallTerminationOnBehalfOf < 0
>>  31152: CDR's destCallTerminationOnBehalfOf < 0
>>  31153: CDR's lastRedirectRedirectOnBehalfOf < 0
>>  31155: CDR's destConversationId < 0
>>  31156: CDR's globalCallId_ClusterID is empty
>>
>>  Orig CMR:
>>  31123: Orig CMR's globalCallID_callManagerId <= 0
>>  31124: Orig CMR's globalCallID_callId <= 0
>>  31129: Orig CMR's callIdentifier <= 0
>>  31125: Orig CMR's numberPacketsSent < 0
>>  31126: Orig CMR's numberPacketsReceived < 0
>>  31127: Orig CMR's jitter < 0
>>  31149: Orig CMR's deviceName is empty
>>  31157: Orig CMR's globalCallId_ClusterID is empty
>>
>>  Dest CMR:
>>  31140: Dest CMR's globalCallID_callManagerId <= 0
>>  31141: Dest CMR's globalCallID_callId <= 0
>>  31145: Dest CMR's callIdentifier <= 0
>>  31142: Dest CMR's numberPacketsSent < 0
>>  31143: Dest CMR's numberPacketsReceived < 0
>>  31144: Dest CMR's jitter < 0
>>  31150: Dest CMR's deviceName is empty
>>  31158: Dest CMR's globalCallId_ClusterID is empty
>>
>>
>>
>>  Erick Bergquist wrote:
>>
>>     
>>>
>>> I have a issue where there are CDR records for various dates in the
>>> CDR database (from CDR search on extensions) but the System Summary,
>>> Traffic by extension report shows 0 calls for the same extensions
>>> selected in the CDR search.  There are 70,000 or so errors in the
>>> Tbl_Billing_Data database from looking at the table information under
>>> manual database purge.\
>>>
>>> In pre 5.x days, one could go look at the BAD subfolder and see what
>>> the bad records were, etc and fix them maybe and put them back to be
>>> loaded in. How can one tell what is bad record in 5.1.2 and why the
>>> records are not in CAR and showing up in the report?
>>>
>>> The version is 5.1.2.1000-11
>>>
>>> Erick
>>> _______________________________________________
>>> cisco-voip mailing list
>>> cisco-voip at puck.nether.net
>>> https://puck.nether.net/mailman/listinfo/cisco-voip
>>>
>>>
>>>