June 11, 2009 at 9:44 am
Has anyone worked with the text dumps you can get from Cisco Call Manager?
I beleive these are ragged right formatted files with headers reproduced for each group of records. The data looks like this (bear with me, it's long and this is just 2 records) :
Call Type :Simple
CDR
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 clientMatterCode authCodeDescription authorizationLevel
1 4 495128 70560982 Jun 1, 2009 03:51:14 PM 4 0 172.17.80.12 2512 0 0 4 173.17.80.12 24580 4 20 0 0 0 0 0.0.0.0 0 70560983 4 0 173.17.54.12 2544 2544 0 16 4 173.17.54.12 28496 4 20 0 0 0 0 0.0.0.0 0 Jun 1, 2009 03:51:20 PM Jun 1, 2009 04:00:09 PM 2544 5ceab0b1-440d-49d4-b5ed-1bb7677d708d pt_internal pt_internal pt_internal pt_internal 529 SEP001A6BCE8755 SEP001D45B5BC8E 0 12 0 0 0 0 0 Mid_America_Cluster 0 null null 0 null 0 0
Origination CMR
cdrRecordType globalCallID_callManagerId globalCallID_callId nodeId directoryNum (callingPartyNumber) directoryNum (finalCalledPartyNumber) callIdentifier dateTimeStamp numberPacketsSent numberOctetsSent numberPacketsReceived numberOctetsReceived numberPacketsLost jitter latency pkid directoryNumPartition (callingPartyNumberPartition)directoryNumPartition (finalCalledPartyNumberPartition)globalCallId_ClusterID deviceName QoS varVQMetrics
1 4 495128 4 4 2512 2544 70560982 70560983 null null null null null null null null null null null null null null 5ceab0b1-440d-49d4-b5ed-1bb7677d708d pt_internal pt_internal Zip_America_Cluster SEP001A6BCE8755 SEP001D45B5BC8E null null _
Destination CMR
cdrRecordType globalCallID_callManagerId globalCallID_callId nodeId directoryNum (callingPartyNumber) directoryNum (finalCalledPartyNumber) callIdentifier dateTimeStamp numberPacketsSent numberOctetsSent numberPacketsReceived numberOctetsReceived numberPacketsLost jitter latency pkid directoryNumPartition (callingPartyNumberPartition)directoryNumPartition (finalCalledPartyNumberPartition)globalCallId_ClusterID deviceName QoS varVQMetrics
1 4 495128 4 4 2512 2544 70560982 70560983 null null null null null null null null null null null null null null 5ceab0b1-440d-49d4-b5ed-1bb7677d708d pt_internal pt_internal Fid_America_Cluster SEP001A6BCE8755 SEP001D45B5BC8E null null _
How would I parse something like this to get it into SQL 2005?
I tried using the "Import Wizard" with ragged right settings, but it doesn't work.
I think the biggest reason that it does not work is because the header is repeated for each record. Cisco Call Manager does not allow you to modify the output of this dump.
Can anything be done with it?
I'm starting to wonder if it's impossible with the SQL 2005 tools.
June 11, 2009 at 11:22 am
...I beleive these are ragged right formatted files...
To get this to work you really hafta know the structure of the file. What are the delimiters?; are there file headers apart from the record headers?...etc.
Does Cisco provide any documentation on the file?
-MarkO
"You do not really understand something until you can explain it to your grandmother" - Albert Einstein
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply