Splunk Search

How to break out multiple values into a new column?

cspires64
Path Finder

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
Tags (2)
0 Karma

tincupchalice
Path Finder

So something like

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

jacobwilkins
Communicator

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.

somesoni2
Revered Legend

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.

schatzb
Explorer

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

0 Karma

somesoni2
Revered Legend

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

0 Karma

cspires64
Path Finder

it will always be 3

0 Karma

cspires64
Path Finder

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.

0 Karma

somesoni2
Revered Legend

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

0 Karma

cspires64
Path Finder

xyz will always be constant

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...