Getting Data In
Highlighted

.csv files column only import partially

Communicator

Hi all, I have a .csv file import problem. To process data I normally upload csv files to HUE browser then use summary index("skype_session" in this case) to import them into Splunk. But one column in the csv file does not import completely, they only show first line but the actual column contains much more data.

This is one cell from the QoEReport column:

{"Session":{"MediaStartTime":"2018-05-29T20:02:14.8033217","MediaEndTime":"2018-05-29T20:22:27.2147183","IsFromReceived":true,"IsToReceived":true,"ConferenceUri":"sip:billy.lam@oocl.com;gruu;opaque=app:conf:focus:id:7MFQBNVH","MediationServerBypassFlag":null,"FromOS":"Darwin Kernel Version 17.5.0: Fri Apr 13 19:32:32 PDT 2018; root:xnu-4570.51.2~1/RELEASE_X86_64","ToOS":"","FromCPUName":"Intel(R) Core(TM) i5-5250U CPU @ 1.60GHz","ToCPUName":"","FromCPUNumberOfCores":2,"ToCPUNumberOfCores":null,"FromCPUProcessorSpeed":1600,"ToCPUProcessorSpeed":1800,"FromVirtualizationFlag":null,"ToVirtualizationFlag":null,"PossibleDataMissing":false},"MediaLines":[{"MediaLineLabelText":"main-audio","MidCallReport":false,"FromConnectivityIce":"DIRECT","ToConnectivityIce":"","Transport":"UDP","Security":"SRTP","FromPort":50004,"ToPort":52320,"FromRelayIPAddr":"52.113.1.28","ToRelayIPAddr":"52.114.60.71","FromRelayPort":58012,"ToRelayPort":null,"FromCaptureDev":"Built-in Microphone","ToCaptureDev":"","FromCaptureDevDriver":"","ToCaptureDevDriver":"","FromRenderDev":"Built-in Output","ToRenderDev":"","FromRenderDevDriver":"","ToRenderDevDriver":"","FromVPN":false,"ToVPN":false,"FromLinkSpeed":146080000,"ToLinkSpeed":1000000000,"FromNetworkConnectionDetail":"wifi","ToNetworkConnectionDetail":"wired","FromIPAddr":"52.114.60.71","ToIPAddr":"52.114.60.71","FromBssid":null,"ToBssid":null,"FromReflexiveLocalIPAddr":"98.210.208.202","ToReflexiveLocalIPAddr":"10.11.180.137","FromWifiDriverDeviceDesc":"","ToWifiDriverDeviceDesc":"","FromWifiDriverVersion":"","ToWifiDriverVersion":"","FromWifiRSSI":0,"ToWifiRSSI":0,"FromSSID":"","ToSSID":"","FromWifiChannel":0,"ToWifiChannel":0,"FromActivePowerProfile":0,"ToActivePowerProfile":0,"FromWifiHandovers":0,"ToWifiHandovers":0,"FromWifiChannelSwitches":0,"ToWifiChannelSwitches":0,"FromWifiChannelReassociations":0,"ToWifiChannelReassociations":0,"FromWifiRadioFrequency":0,"ToWifiRadioFrequency":0,"FromWifiSignalStrength":0,"ToWifiSignalStrength":0,"PossibleDataMissing":false}],"AudioStreams":[{"JitterInterArrival":10,"JitterInterArrivalMax":24,"PacketLossRate":0.01353227,"PacketLossRateMax":0.09027778,"BurstDensity":null,"BurstDuration":null,"BurstGapDensity":null,"BurstGapDuration":null,"BandwidthEst":25245423,"RoundTrip":520,"RoundTripMax":11099,"PacketUtilization":2843,"RatioConcealedSamplesAvg":0.02746676,"ConcealedRatioMax":0.01598402,"PayloadDescription":"SIREN","AudioSampleRate":16000,"AudioFECUsed":true,"SendListenMOS":null,"OverallAvgNetworkMOS":3.487248,"DegradationAvg":0.2727518,"DegradationMax":0.2727518,"NetworkJitterAvg":253.0633,"NetworkJitterMax":1149.659,"JitterBufferSizeAvg":220,"JitterBufferSizeMax":1211,"PossibleDataMissing":false,"StreamDirection":"FROM-to-TO"},{"JitterInterArrival":10,"JitterInterArrivalMax":24,"PacketLossRate":0.01342051,"PacketLossRateMax":0.09027778,"BurstDensity":null,"BurstDuration":null,"BurstGapDensity":null,"BurstGapDuration":null,"BandwidthEst":2347573,"RoundTrip":721,"RoundTripMax":1703,"PacketUtilization":2906,"RatioConcealedSamplesAvg":0.02746676,"ConcealedRatioMax":null,"PayloadDescription":"SIREN","AudioSampleRate":16000,"AudioFECUsed":true,"SendListenMOS":3.5,"OverallAvgNetworkMOS":null,"DegradationAvg":null,"DegradationMax":null,"NetworkJitterAvg":null,"NetworkJitterMax":null,"JitterBufferSizeAvg":null,"JitterBufferSizeMax":null,"PossibleDataMissing":false,"StreamDirection":"TO-to-FROM"}],"VideoStreams":null,"AudioSignals":[{"PossibleDataMissing":false,"SubmittedByFromUser":true,"SendSignalLevel":-20,"RecvSignalLevel":-14,"SendNoiseLevel":-64,"RecvNoiseLevel":-58,"AudioSpeakerGlitchRate":null,"AudioMicGlitchRate":null,"VsEntryCauses":null,"EchoEventCauses":null,"RecvSignalLevelCh1":-14,"RecvSignalLevelCh2":null,"RecvNoiseLevelCh1":-58,"RecvNoiseLevelCh2":null,"SendSignalLevelCh1":-20,"SendSignalLevelCh2":null,"SendNoiseLevelCh1":-64,"SendNoiseLevelCh2":null,"RenderSignalLevel":0.0,"RenderNoiseLevel":0.0,"RenderLoopbackSignalLevel":null},{"PossibleDataMissing":false,"SubmittedByFromUser":false,"SendSignalLevel":null,"RecvSignalLevel":null,"SendNoiseLevel":null,"RecvNoiseLevel":null,"AudioSpeakerGlitchRate":null,"AudioMicGlitchRate":null,"VsEntryCauses":null,"EchoEventCauses":null,"RecvSignalLevelCh1":null,"RecvSignalLevelCh2":null,"RecvNoiseLevelCh1":null,"RecvNoiseLevelCh2":null,"SendSignalLevelCh1":null,"SendSignalLevelCh2":null,"SendNoiseLevelCh1":null,"SendNoiseLevelCh2":null,"RenderSignalLevel":0.0,"RenderNoiseLevel":0.0,"RenderLoopbackSignalLevel":null}],"AppSharingStreams":null,"FeedBackReports":null,"AudioClientEvents":[{"PossibleDataMissing":false,"SubmittedByFromUser":true,"NetworkSendQualityEventRatio":0.0,"NetworkReceiveQualityEventRatio":0.0,"NetworkDelayEventRatio":0.23,"NetworkBandwidthLowEventRatio":0.0,"CPUInsufficientEventRatio":0.0,"DeviceHalfDuplexAECEventRatio":0.0,"DeviceRenderNotFunctioningEventRatio":0.0,"DeviceCaptureNotFunctioningEventRatio":0.0,"DeviceGlitchesEventRatio":0.0,"DeviceLowSNREventRatio":0.0,"DeviceLowSpeechLevelEventRatio":0.0,"DeviceClippingEventRatio":0.0,"DeviceEchoEventRatio":0.0,"DeviceNearEndToEchoRatioEventRatio":0.0,"DeviceMultipleEndpointsEventCount":0,"DeviceHowlingEventCount":0,"DeviceRenderZeroVolumeEventRatio":0.0,"DeviceRenderMuteEventRatio":0.0},{"PossibleDataMissing":false,"SubmittedByFromUser":false,"NetworkSendQualityEventRatio":null,"NetworkReceiveQualityEventRatio":null,"NetworkDelayEventRatio":null,"NetworkBandwidthLowEventRatio":null,"CPUInsufficientEventRatio":null,"DeviceHalfDuplexAECEventRatio":null,"DeviceRenderNotFunctioningEventRatio":null,"DeviceCaptureNotFunctioningEventRatio":null,"DeviceGlitchesEventRatio":null,"DeviceLowSNREventRatio":null,"DeviceLowSpeechLevelEventRatio":null,"DeviceClippingEventRatio":null,"DeviceEchoEventRatio":null,"DeviceNearEndToEchoRatioEventRatio":null,"DeviceMultipleEndpointsEventCount":0,"DeviceHowlingEventCount":0,"DeviceRenderZeroVolumeEventRatio":null,"DeviceRenderMuteEventRatio":null}],"VideoClientEvents":null,"TraceRoutes":null}

But when I display the columns in the Splunk they only have the first one line: {"Session":{"MediaStartTime":"2018-05-29T20:02:14.8033217", One assumption is that the cell contains multiple comma. So when I want to extract multiple values from QoEReport column nothing showed up.

alt text

Does anyone know how to import the files completely? Thanks a lot!

0 Karma
Highlighted

Re: .csv files column only import partially

Ultra Champion

Can you elaborate a bit on the import process? Maybe it's just me, but I've never heard of "HUE browser" and also not entirely sure what you mean by "then use summary index("skype_session" in this case) to import them into Splunk".

0 Karma
Highlighted

Re: .csv files column only import partially

Communicator

Yeah sure. So basically HUE is an Analytics Workbench for browsing, querying and visualizing data. After I upload the files to it I'd add a path in Splunk specifying that's where the Skype session files stored and use the index="skype_seesion" to denote this path. (The connection was established by the other team a long time ago) So that when I specify index at the beginning of the search the Splunk would know I only need files related to the Skype.

0 Karma
Highlighted

Re: .csv files column only import partially

Ultra Champion

Apart from the comment from @richgalloway, which probably already points you into how to solve this, it would be interesting to know a bit more detail on this part: After I upload the files to it I'd add a path in Splunk specifying that's where the Skype session files stored and use the index="skype_seesion" to denote this path.

That sounds like configuring a Splunk input somehow, but it would be good to know how exactly you do that.

0 Karma
Highlighted

Re: .csv files column only import partially

SplunkTrust
SplunkTrust

By default, Splunk assumes a cell containing embedded commas is enclosed by quotation marks. Your example cell is not. You may be able to change the default behavior to use curly braces, but the best option may be to change from CSV to PSV (Pipe Separated Values) to avoid the comma problem entirely. That assumes, of course, that you have control over how the file is written.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: .csv files column only import partially

Communicator

Thanks for your comment! This problem has been haunting me for a while, I guess now change is unavoidable.

0 Karma