-
-
Notifications
You must be signed in to change notification settings - Fork 28
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Cisco UCM CDR filter #70
Comments
Hi Andre - Are those materialized as individual files? |
he file is simple csv, that means the example given looks in csv like this: 1,2,28377,36951964,1593547211,2,36951964,152200202,"+4912345678","",0,16,4,152200202,23618,2,20,0,0,0,0,0,0,"0","0",36951967,2,36951967,152200202,"+4923456789","+4923456789","",0,0,4,152200202,23320,2,20,0,0,0,0,0,0,"0","0",1593547211,1593547987,"+4912345678","dbfc3109-c95c-4a46-96fe-d94d89e20cde","PSTN_OUT","","PSTN_OUT","",776,"CUBE","CUBE",12,17,0,18,0,146,0,"StandAloneCluster",18,"","",0,"",2,2,0,0,64,64,"","","","10.100.10.99","10.100.10.99",0,0,0,0,0,0,0,0,0,0,0,0,1,"216DF07ABA4311EAB627B490D23A7537",1,"4AF15980000100000009151F0212640A",0,0,0,"","",5,"","","","","","","","",0,0,"","","","","","","","",0,0,0,"+!","+!","","","","","fe17729285175238945d2e0c49e9929c","b78e7565083b5488b59e1cdc3ba7f58c" |
first line of each csv contains column names, second line column types, line 3 ff contains data. |
no, datasets are grouped by time - in our setup each hour a new file is created containing all CDRs / CMRs for that timeframe (one file for CDRs, one for CMRs). |
wait up - i'm confused. if its a CSV file each hour, how can the values be spread on multiple lines? it seems unlikely. Could you please provide the first 3-4 lines of any such csv file? |
sorry for confusion. here the first 4 lines of a cdr file of 2020 june 30th 21:13: "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","origRSVPAudioStat","origRSVPVideoStat","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","destRSVPAudioStat","destRSVPVideoStat","dateTimeConnect","dateTimeDisconnect","lastRedirectDn","pkid","originalCalledPartyNumberPartition","callingPartyNumberPartition","finalCalledPartyNumberPartition","lastRedirectDnPartition","duration","origDeviceName","destDeviceName","origCallTerminationOnBehalfOf","destCallTerminationOnBehalfOf","origCalledPartyRedirectOnBehalfOf","lastRedirectRedirectOnBehalfOf","origCalledPartyRedirectReason","lastRedirectRedirectReason","destConversationId","globalCallId_ClusterID","joinOnBehalfOf","comment","authCodeDescription","authorizationLevel","clientMatterCode","origDTMFMethod","destDTMFMethod","callSecuredStatus","origConversationId","origMediaCap_Bandwidth","destMediaCap_Bandwidth","authorizationCodeValue","outpulsedCallingPartyNumber","outpulsedCalledPartyNumber","origIpv4v6Addr","destIpv4v6Addr","origVideoCap_Codec_Channel2","origVideoCap_Bandwidth_Channel2","origVideoCap_Resolution_Channel2","origVideoTransportAddress_IP_Channel2","origVideoTransportAddress_Port_Channel2","origVideoChannel_Role_Channel2","destVideoCap_Codec_Channel2","destVideoCap_Bandwidth_Channel2","destVideoCap_Resolution_Channel2","destVideoTransportAddress_IP_Channel2","destVideoTransportAddress_Port_Channel2","destVideoChannel_Role_Channel2","IncomingProtocolID","IncomingProtocolCallRef","OutgoingProtocolID","OutgoingProtocolCallRef","currentRoutingReason","origRoutingReason","lastRedirectingRoutingReason","huntPilotPartition","huntPilotDN","calledPartyPatternUsage","IncomingICID","IncomingOrigIOI","IncomingTermIOI","OutgoingICID","OutgoingOrigIOI","OutgoingTermIOI","outpulsedOriginalCalledPartyNumber","outpulsedLastRedirectingNumber","wasCallQueued","totalWaitTimeInQueue","callingPartyNumber_uri","originalCalledPartyNumber_uri","finalCalledPartyNumber_uri","lastRedirectDn_uri","mobileCallingPartyNumber","finalMobileCalledPartyNumber","origMobileDeviceName","destMobileDeviceName","origMobileCallDuration","destMobileCallDuration","mobileCallType","originalCalledPartyPattern","finalCalledPartyPattern","lastRedirectingPartyPattern","huntPilotPattern","origDeviceType","destDeviceType","origDeviceSessionID","destDeviceSessionID" |
each new file contains x datasets, where each row contains one dataset (call detail record). except the first two rows of each files are containing headers. hope to calrify it. ;) |
overall, the first step is pretty easy - pastash already has a CSV parser - we just skip the first 2 lines and teach it what headers. From there we have a JSON object we can use to form a HEP packet of various types.
From here its on you to tell us what could/should be done with all those columns. If you have enough elements, we can transform them into a meaningful payload. If you'd like to send the CDR as log type for playing, make sure the compute_field actually grabs the right Call-ID column, and then use the following output:
|
sounds really good, correlation id is fine. |
besides my last question i have some errors, a field seems to be too large in my files: [Fri, 20 Nov 2020 09:09:48 GMT] INFO Start reading /opt/ciscoCdr/cdr_StandAloneCluster_02_202011191314_46926 at 2 fd 578 |
and last but not least: paStash is running, but i don't see any data in table hep_proto_100_default or hep_proto_100_default__. how to debug it? running pastash in debug mode looks quite fine:
config file: netstat on local server: |
I doubt this would or should work, but I can't tell without seeing the config you're running. The point of pastash is not sending the data as-is (unless its already well formatted) for rather transforming it into what it should be. In this case, nothing will work without a correlation_id to begin with and sending the raw CSV seems to me the wrong choice. The CSV parser (below) will turn the rows into a JSON object. You might want to try send that AFTER electing a correlation ID (call-id of the sip session, from the CDR)
|
ah sorry, just piped the output of my config, the filter was set as suggested from you before. here completely: filter { output { |
Mind the |
yes, homer is running locally. will check communication on port 9060 |
lot of traffic from heplify-clients, but no traffic from my local server, where paStash for parsing CDRs and heplify-server (docker) are running. changed the config to host sipmon01.fqdn but no change... |
ok, got a step further. there is traffic from paStash to heplify-server (had to dump the docker interface), but all packets are failing: 14:11:50.762308 IP sipmon01.37059 > 172.18.0.3.9060: UDP, bad length 8151 > 1472 |
Try adding the following filter to generate a custom payload with a few elements of your CDR to test with:
|
Hi,
I want to heplify Cisco UCM CDRs (currently version 12.5, but afaik the structure didn't change in the last years).
The plain-text-files have the following structure:
column-name | type | example
cdrRecordType | INTEGER | 1
globalCallID_callManagerId | INTEGER | 2
globalCallID_callId | INTEGER | 28377
origLegCallIdentifier | INTEGER | 36951964
dateTimeOrigination | INTEGER | 1593547211
origNodeId | INTEGER | 2
origSpan | INTEGER | 36951964
origIpAddr | INTEGER | 152200202
callingPartyNumber | VARCHAR(50) | +49123456789
callingPartyUnicodeLoginUserID | VARCHAR(128) |
origCause_location | INTEGER | 0
origCause_value | INTEGER | 16
origPrecedenceLevel | INTEGER | 4
origMediaTransportAddress_IP | INTEGER | 152200202
origMediaTransportAddress_Port | INTEGER | 23618
origMediaCap_payloadCapability | INTEGER | 2
origMediaCap_maxFramesPerPacket | INTEGER | 20
origMediaCap_g723BitRate | INTEGER | 0
origVideoCap_Codec | INTEGER | 0
origVideoCap_Bandwidth | INTEGER | 0
origVideoCap_Resolution | INTEGER | 0
origVideoTransportAddress_IP | INTEGER | 0
origVideoTransportAddress_Port | INTEGER | 0
origRSVPAudioStat | VARCHAR(64) | 0
origRSVPVideoStat | VARCHAR(64) | 0
destLegIdentifier | INTEGER | 36951967
destNodeId | INTEGER | 2
destSpan | INTEGER | 36951967
destIpAddr | INTEGER | 152200202
originalCalledPartyNumber | VARCHAR(50) | +4923456789
finalCalledPartyNumber | VARCHAR(50) | +4923456789
finalCalledPartyUnicodeLoginUserID | VARCHAR(128) |
destCause_location | INTEGER | 0
destCause_value | INTEGER | 0
destPrecedenceLevel | INTEGER | 4
destMediaTransportAddress_IP | INTEGER | 152200202
destMediaTransportAddress_Port | INTEGER | 23320
destMediaCap_payloadCapability | INTEGER | 2
destMediaCap_maxFramesPerPacket | INTEGER | 20
destMediaCap_g723BitRate | INTEGER | 0
destVideoCap_Codec | INTEGER | 0
destVideoCap_Bandwidth | INTEGER | 0
destVideoCap_Resolution | INTEGER | 0
destVideoTransportAddress_IP | INTEGER | 0
destVideoTransportAddress_Port | INTEGER | 0
destRSVPAudioStat | VARCHAR(64) | 0
destRSVPVideoStat | VARCHAR(64) | 0
dateTimeConnect | INTEGER | 1593547211
dateTimeDisconnect | INTEGER | 1593547987
lastRedirectDn | VARCHAR(50) | +49258147369
pkid | UNIQUEIDENTIFIER | dbfc3109-c95c-4a46-96fe-d94d89e20cde
originalCalledPartyNumberPartition | VARCHAR(50) | PSTN_OUT
callingPartyNumberPartition | VARCHAR(50) |
finalCalledPartyNumberPartition | VARCHAR(50) | PSTN_OUT
lastRedirectDnPartition | VARCHAR(50) |
duration | INTEGER | 776
origDeviceName | VARCHAR(129) | CUBE
destDeviceName | VARCHAR(129) | CUBE
origCallTerminationOnBehalfOf | INTEGER | 12
destCallTerminationOnBehalfOf | INTEGER | 17
origCalledPartyRedirectOnBehalfOf | INTEGER | 0
lastRedirectRedirectOnBehalfOf | INTEGER | 18
origCalledPartyRedirectReason | INTEGER | 0
lastRedirectRedirectReason | INTEGER | 146
destConversationId | INTEGER | 0
globalCallId_ClusterID | VARCHAR(50) | StandAloneCluster
joinOnBehalfOf | INTEGER | 18
comment | VARCHAR(2048) |
authCodeDescription | VARCHAR(50) |
authorizationLevel | INTEGER | 0
clientMatterCode | VARCHAR(32) |
origDTMFMethod | INTEGER | 2
destDTMFMethod | INTEGER | 2
callSecuredStatus | INTEGER | 0
origConversationId | INTEGER | 0
origMediaCap_Bandwidth | INTEGER | 64
destMediaCap_Bandwidth | INTEGER | 64
authorizationCodeValue | VARCHAR(32) |
outpulsedCallingPartyNumber | VARCHAR(50) |
outpulsedCalledPartyNumber | VARCHAR(50) |
origIpv4v6Addr | VARCHAR(64) | 10.10.10.99
destIpv4v6Addr | VARCHAR(64) | 10.100.10.99
origVideoCap_Codec_Channel2 | INTEGER | 0
origVideoCap_Bandwidth_Channel2 | INTEGER | 0
origVideoCap_Resolution_Channel2 | INTEGER | 0
origVideoTransportAddress_IP_Channel2 | INTEGER | 0
origVideoTransportAddress_Port_Channel2 | INTEGER | 0
origVideoChannel_Role_Channel2 | INTEGER | 0
destVideoCap_Codec_Channel2 | INTEGER | 0
destVideoCap_Bandwidth_Channel2 | INTEGER | 0
destVideoCap_Resolution_Channel2 | INTEGER | 0
destVideoTransportAddress_IP_Channel2 | INTEGER | 0
destVideoTransportAddress_Port_Channel2 | INTEGER | 0
destVideoChannel_Role_Channel2 | INTEGER | 0
IncomingProtocolID | INTEGER | 1
IncomingProtocolCallRef | VARCHAR(32) | 216DF07ABA4311EAB627B490D23A7537
OutgoingProtocolID | INTEGER | 1
OutgoingProtocolCallRef | VARCHAR(32) | 4AF15980000100000009151F0212640A
currentRoutingReason | INTEGER | 0
origRoutingReason | INTEGER | 0
lastRedirectingRoutingReason | INTEGER | 0
huntPilotPartition | VARCHAR(50) |
huntPilotDN | VARCHAR(50) |
calledPartyPatternUsage | INTEGER | 5
IncomingICID | VARCHAR(50) |
IncomingOrigIOI | VARCHAR(50) |
IncomingTermIOI | VARCHAR(50) |
OutgoingICID | VARCHAR(50) |
OutgoingOrigIOI | VARCHAR(50) |
OutgoingTermIOI | VARCHAR(50) |
outpulsedOriginalCalledPartyNumber | VARCHAR(50) |
outpulsedLastRedirectingNumber | VARCHAR(50) |
wasCallQueued | INTEGER | 0
totalWaitTimeInQueue | INTEGER | 0
callingPartyNumber_uri | VARCHAR(255) |
originalCalledPartyNumber_uri | VARCHAR(255) |
finalCalledPartyNumber_uri | VARCHAR(255) |
lastRedirectDn_uri | VARCHAR(255) |
mobileCallingPartyNumber | VARCHAR(50) |
finalMobileCalledPartyNumber | VARCHAR(50) |
origMobileDeviceName | VARCHAR(129) |
destMobileDeviceName | VARCHAR(129) |
origMobileCallDuration | INTEGER | 0
destMobileCallDuration | INTEGER | 0
mobileCallType | INTEGER | 0
originalCalledPartyPattern | VARCHAR(50) | +!
finalCalledPartyPattern | VARCHAR(50) | +!
lastRedirectingPartyPattern | VARCHAR(50) |
huntPilotPattern | VARCHAR(50) |
origDeviceType | VARCHAR(100) |
destDeviceType | VARCHAR(100) |
origDeviceSessionID | VARCHAR(128) | fe17729285175238945d2e0c49e9929c
destDeviceSessionID | VARCHAR(128) | b78e7565083b5488b59e1cdc3ba7f58c
The text was updated successfully, but these errors were encountered: