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

Fri Apr 25 14:15:01 EDT 2008

In CM6.x it is CAR that processes the flat files and inserts into 
database.  Stop all of the CAR services and you should find the files 
left.  The file handling is a bit different in CM6.x.  Files are stored 
in a single location and then moved or linked into the processed 
directory.  Ryan provided paths in his post.

/Wes

Erick Bergquist wrote:
> Ok, thanks.  Yes, but on previous windows versions you could stop the
> insert process or adjust the time to before it inserted so you could
> look at them, etc and if they were bad they were moved to the bad
> folder. You then could maybe fix the bad records and drop them back in
> the CDR folder and sometimes figure out what data in the record was
> causing the problem then see if it was a configuration issue or a bug,
> etc.
>
> I was thinking I may have to set it up to FTP the files off elsewhere
> briefly so we have them to look at to move further for comparison.
>
> On Fri, Apr 25, 2008 at 12:27 PM, Ryan Ratliff <rratliff at cisco.com> wrote:
>   
>> In Windows the flat files were deleted as soon as they were imported into
>> the DB.
>>
>> If you want to get the flat files then your best bet is to set up an
>> external server and have the files uploaded to it.  These are the same files
>> that are imported into the DB, and remember you'll get 1 per minute, per
>> server by default.
>>
>> You can also poke around with 'utils file list' to see and 'utils file
>> view/get' to look at or upload specific files.
>>
>> admin:file list activelog cm/cdr_repository/*<dir>   car<dir>
>> destination1<dir>   destination2<dir>   destination3<dir>   preserve<dir>
>> processed<dir>   tmp<dir>   transdir count = 8, file count = 0
>>
>>
>>
>> -Ryan
>>
>>
>> On Apr 25, 2008, at 1:13 PM, Erick Bergquist wrote:
>> Some more info on this problem, I am working with TAC on this but
>> making slow progress with the engineer.  Basically, on some days there
>> are no CDR records being recorded when there are plenty of calls for
>> some reason.  Is there a way to get super user access and
>> download/view the actual CDR flat files created for those days like in
>> the Windows days? The CDR Export option in CAR exported 0 records but
>> I believe it is exporting data after it is in CAR database so if there
>> is none there for the days, then it can't export any data.
>>
>> For example, we have data on April 17th, but 0 calls for the 18th and
>> 19th, and then we have plenty of calls starting on the 20th again. It
>> is very sporadic but on average data is missed 1-2 times a week.
>>
>>
>> On Tue, Apr 8, 2008 at 12:29 PM, Wes Sisk <wsisk at cisco.com> wrote:
>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> cisco-voip mailing list
>> cisco-voip at puck.nether.net
>> https://puck.nether.net/mailman/listinfo/cisco-voip
>>
>>     