paStash
paStash copied to clipboard
sipcapture
Cisco UCM CMR filter
in reference to #70 i want to add further information to homer by retrieving data of cisco CMR files.
the structure is as follwos:
column name | type | example
cdrRecordType | INTEGER | 2 globalCallID_callManagerId | INTEGER | 1 globalCallID_callId | INTEGER | 1549027 nodeId | INTEGER | 2 directoryNum | VARCHAR(50) | 11335 callIdentifier | INTEGER | 36951877 dateTimeStamp | INTEGER | 1593545181 numberPacketsSent | INTEGER | 119316 numberOctetsSent | INTEGER | 12076047 numberPacketsReceived | INTEGER | 119311 numberOctetsReceived | INTEGER | 16490570 numberPacketsLost | INTEGER | 0 jitter | INTEGER | 20 latency | INTEGER | 0 pkid | UNIQUEIDENTIFIER | dd934380-4391-409e-82bc-7d71ce4e8bd3 directoryNumPartition | VARCHAR(50) | Eingeloggt globalCallId_ClusterID | VARCHAR(50) | StandAloneCluster deviceName | VARCHAR(129) | CSF11335 varVQMetrics | VARCHAR(600) | CS=0;SCS=0 duration | INTEGER | 2387 videoContentType | VARCHAR(10) | videoDuration | INTEGER | numberVideoPacketsSent | INTEGER | numberVideoOctetsSent | INTEGER | numberVideoPacketsReceived | INTEGER | numberVideoOctetsReceived | INTEGER | numberVideoPacketsLost | INTEGER | videoAverageJitter | INTEGER | videoRoundTripTime | INTEGER | videoOneWayDelay | INTEGER | videoReceptionMetrics | VARCHAR(600) | videoTransmissionMetrics | VARCHAR(600) | videoContentType_channel2 | VARCHAR(10) | videoDuration_channel2 | INTEGER | numberVideoPacketsSent_channel2 | INTEGER | numberVideoOctetsSent_channel2 | INTEGER | numberVideoPacketsReceived_channel2 | INTEGER | numberVideoOctetsReceived_channel2 | INTEGER | numberVideoPacketsLost_channel2 | INTEGER | videoAverageJitter_channel2 | INTEGER | videoRoundTripTime_channel2 | INTEGER | videoOneWayDelay_channel2 | INTEGER | videoReceptionMetrics_channel2 | VARCHAR(600) | videoTransmissionMetrics_channel2 | VARCHAR(600) | localSessionID | VARCHAR(128) | 725db2ec00105000a00000e0c51c2dc0 remoteSessionID | VARCHAR(128) | 20682f0300105000a00000e0c51c2cc0 headsetSN | VARCHAR(129) | headsetMetrics | VARCHAR(1024) |
the file is simple csv, that means the example given looks in csv like this:
2,1,1549027,2,"11335",36951877,1593545181,119316,12076047,119311,16490570,0,20,0,"dd934380-4391-409e-82bc-7d71ce4e8bd3","Eingeloggt","StandAloneCluster","CSF11355","CS=0;SCS=0",2387,"",,,,,,,,,,"","","",,,,,,,,,,"","","725db2ec00105000a00000e0c51c2dc0","20682f0300105000a00000e0c51c2cc0","",""
first line of each csv contains column names, second line column types, line 3 ff contains data.
to clarify as in #70: in our setup every hour a new file is created with x datasets. each row contains one dataset. each file has always column names in first row, type-description in 2nd row and datasets in the following rows.
Alright the job here is slightly different - i see mostly session stats we COULD perhaps convert to a HEP qOS report, but none is filled. Could you find an example with more of the values available?
which values do you mean? most values are video, which we hardly use. all other calls will have at least jitter, latency and packet loss. here further examples:
2,1,2019806,11111,41387961,1605856474,207976,32029071,207896,32035435,77,16,0,bb843e8b-ee08-40d0-b1d2-c14042881fac,Eingeloggt,StandAloneCluster,CSF11111,CS=0.119;SCS=0.079,4161,,,,,,,,,,,,,,,,,,,,,,,,,57a4bc0400105000a00000e0c51c329a,43b4ebeb00105000a00000e0c51c3f36,,, 2,1,2019806,22222,41387960,1605856474,208128,32072599,208069,32044435,56,9,0,85969a7e-f2ef-4820-a90e-48ee8dfb8946,Eingeloggt,StandAloneCluster,CSF22222,CS=0;SCS=0,4162,,,,,,,,,,,,,,,,,,,,,,,,,43b4ebeb00105000a00000e0c51c3f36,57a4bc0400105000a00000e0c51c329a,,, 2,1,2019997,33333,41389303,1605856563,45241,5762053,17925,828318,3,17,0,e0cf069b-90ba-409b-867f-ba100f28a419,Eingeloggt,StandAloneCluster,CSF33333,CS=0.08;SCS=0.08,308,main,0,0,0,0,0,0,0,0,0,RxCodec=H264;RxReso=0x0;RxFrameRate=0,TxCodec=H264;TxReso=0x0;TxFrameRate=0,,,,,,,,,,,,,2c568e5c00105000a00098e7434cd239,000079ea00105000a0009829a680d391,,,
first example has packetloss 77, jitter 16, latency 0, varVQMetrics are filled as well (0.119/0.079 whatever that means, didn't check yet).
does that help?
This is the JSON object you should map data to - taken from HEPGen.js which does the same thing:
{
// media report
rcinfo: {
type: 'HEP',
version: 3,
payload_type: 'JSON',
captureId: 2001,
capturePass: 'myHep',
ip_family: 2,
protocol: 17,
proto_type: 34,
srcIp: pub_ip,
dstIp: peer_ip,
srcPort: 0,
dstPort: 0,
correlation_id: call_id
},
pause: 400,
payload: '{"CORRELATION_ID":"'+call_id+'","RTP_SIP_CALL_ID":"'+call_id+'","DELTA":19.983,"JITTER":0.017,"REPORT_TS":'+new Date().getTime()/1000+',"TL_BYTE":0,"SKEW":0.000,"TOTAL_PK":1512,"EXPECTED_PK":1512,"PACKET_LOSS":0,"SEQ":0,"MAX_JITTER":0.010,"MAX_DELTA":20.024,"MAX_SKEW":0.172,"MEAN_JITTER":0.005,"MIN_MOS":4.032, "MEAN_MOS":4.032, "MOS":4.032,"RFACTOR":80.200,"MIN_RFACTOR":80.200,"MEAN_RFACTOR":80.200,"SRC_IP":"'+pub_ip+'", "SRC_PORT":26872, "DST_IP":"'+peer_ip+'","DST_PORT":51354,"SRC_MAC":"00-30-48-7E-5D-C6","DST_MAC":"00-12-80-D7-38-5E","OUT_ORDER":0,"SSRC_CHG":0,"CODEC_PT":9, "CLOCK":8000,"CODEC_NAME":"g722","DIR":0,"REPORT_NAME":"'+pub_ip+':26872","PARTY":0,"TYPE":"PERIODIC"}'
}
great, thanks. didn't expect you to do all my work! 🥇 thx a lot, will test it on monday!