Split command take comma as a value when null value exists for a field

[dchando]

Hi,

I am trying to use Split command to separate and get few fields. However I am getting different fields value due to null present in event data.

I applied Split on coming events and below are 2 cases from event data

1) F0,F1,F2,F3,F4,F5,F6 - Here I am able to get fields F5 and F6. Good.

2) F0,F1,,,F4,F5,F6 - Here I am getting F4 filed data in F5 and F5 filed data into F6. Because of  null values on F2 and F3.

Is there any solution for this?

Thank you!!!

[bowesmana]

I am assuming that when you say F4 in F5 is about the list of values of the split multi value field.

See this example. You can see that split will split the second row values indicating that there is 'nothing' in the F2/F3 place, but as you can see from the count, there are 7 values in each row for the 'split' field.

| makeresults
| eval f=split("F0,F1,F2,F3,F4,F5,F6:F0,F1,,,F4,F5,F6",":")
| mvexpand f
| table f
| eval split=split(f,",")
| eval count_of_split_results=mvcount(split)
| eval x=mvmap(split,if(len(split)=0,"-",split))

Note that is uses mvmap, which is a Splunk 8 function. But this simple iterates through the mv field values and changes any length 0 field to a hypen.

[ericjorgensenjr]

You might have some luck if you replace the null values with some text indicating the values are null. For example:

| eval F3=if(isnull(F3),"null",F3)

Then you should be able to use the split command to do what you're trying to accomplish.

[dchando]

Thanks

I am not sure about that...

F0,F1,,,F4,F5,F6 is _raw data in event.

I am trying to use split function to get to the F5 and F6. But having 2 missing value making 

[ericjorgensenjr · Accepted solution]

It's a little clunky but something like this would work. If your raw contains more than just this comma separated list you'll want to anchor the regex around the surrounding text. Another suggestion is to have splunk parse the CSV at either index or search time with the sourcetype props/transforms.

| rex field=_raw "^(?<F0>[^,]+)," | rex field=_raw "^(?:[^,]+?,){1}(?<F1>[^,]+?)," | rex field=_raw "^(?:[^,]+?,){2}(?<F2>[^,]+?)," | rex field=_raw "^(?:(?:[^,]+)?,){3}(?<F3>[^,]+?)," | rex field=_raw "^(?:(?:[^,]+)?,){4}(?<F4>[^,]+?)," | rex field=_raw "^(?:(?:[^,]+)?,){5}(?<F5>[^,]+?)," | rex field=_raw "^(?:(?:[^,]+)?,){6}(?<F6>[^,]+)?"