topic Re: How to break out multiple values into a new column? in Splunk Search

How to break out multiple values into a new column?

cspires64 — Wed, 27 May 2015 16:24:25 GMT

Here is what my data looks like:

time     col-a     col-b     col-c    col-d
12:00     5          2        x,6       1
                              y,0
                              z,2

12:05     5          1        x,4       1
                              y,1
                              z,3

How do break out the multiple values in column c to look like:

time     col-a     col-b     col-c.x    col-c.y    col-c.z   col-d
12:00      5         2         6           0           2       1
12:05      5         1         4           1           3       1

Re: How to break out multiple values into a new column?

somesoni2 — Wed, 27 May 2015 18:55:02 GMT

Is the no of element fixed for column C? (example show 3, will it always be 3 or will it be dynamic)

Re: How to break out multiple values into a new column?

cspires64 — Wed, 27 May 2015 20:14:36 GMT

it will always be 3

Re: How to break out multiple values into a new column?

cspires64 — Wed, 27 May 2015 20:17:32 GMT

The only way I could create it was to do a table with col-a, col-b, and col-d and join that with an xyseries of col-c. However, this slows down the search.

Re: How to break out multiple values into a new column?

somesoni2 — Wed, 27 May 2015 20:23:22 GMT

Last question, will the values x,y,z will also be constant OR they can change?

Re: How to break out multiple values into a new column?

cspires64 — Wed, 27 May 2015 20:30:43 GMT

xyz will always be constant

Re: How to break out multiple values into a new column?

somesoni2 — Wed, 27 May 2015 20:34:48 GMT

If the number of element in the field col-c is always 3, this

1) if first values in the mv field (e.g. x OR y OR z) is constant, try this

Your current search so far | eval "col-c.x"=mvindex(split(mvindex('col-c',0),","),1) | eval "col-c.y"=mvindex(split(mvindex('col-c',1),","),1) | eval "col-c.z"=mvindex(split(mvindex('col-c',2),","),1) | fields - "col-c"

2) If the values in mvfield (e.g. x OR y OR z is dynamic, try this

Your current search so far | eval fieldname1=mvindex(split(mvindex('col-c',0),","),0) | eval fieldname2=mvindex(split(mvindex('col-c',1),","),0) | eval fieldname3=mvindex(split(mvindex('col-c',2),","),0) | eval "col-c.{fieldname1}"=mvindex(split(mvindex('col-c',0),","),1) | eval "col-c.{fieldname2}"=mvindex(split(mvindex('col-c',1),","),1) | eval "col-c.{fieldname3}"=mvindex(split(mvindex('col-c',2),","),1) | fields - fieldname* "col-c"

Please ensure that you update the field names and prefix as per yours.

Re: How to break out multiple values into a new column?

schatzb — Wed, 27 May 2015 20:43:41 GMT

I was just typing that out. Good work with the dynamic field names.

Re: How to break out multiple values into a new column?

jacobwilkins — Wed, 27 May 2015 21:49:51 GMT

I feel like there should be a more elegant way to do this, maybe something with map or foreach...

    | eval extrakey=md5(tostring(_time)+tostring(colc)) 
    | mvexpand colc 
    | rex field=colc "^(?<fname>[^,]*),(?<fvalue>.*)$" 
    | eval colc.{fname}=fvalue 
    | fields - fname, fvalue, colc 
    | stats values(*) as * by extrakey

Apologies for dropping the hyphen from your field names. This solution works for n-number of pairs in colc. This solution does NOT work well for large datasets where mvexpand blows you up. The "extrakey" business is because I tested with synthetic data. Depending on your circumstance, you might want to calculate that differently, or just use _raw.

Re: How to break out multiple values into a new column?

tincupchalice — Wed, 27 May 2015 22:04:00 GMT

So something like

| mvjoin(colc, ":")
| rex field=colc "x,(?<colcx>\d+):y,(?<colcy>\d+):z,(?<colcz>\d+)"
| table cola colb colcx colcy colcz cold