Splunk Search

How can I combine multiple rows into 1 row?

Engager

I have a search that returns the following table:

| Key     | Value   |

|---------|---------|
| user    | bob     |
| ip      | 1.1.1.1 |
| traffic | 500     |
| user    | bill    |
| ip      | 2.2.2.2 |
| traffic | 200     |

And I need it to look like this:

| user | ip      | traffic |

|------|---------|---------|
| bob  | 1.1.1.1 | 500     |
| bill | 2.2.2.2 | 200     |`

I've tried using commands like transpose and xyseries without any luck.

Tags (3)

Revered Legend

Try like this

your current search giving above table with field Key and Value
| eval sno=if(Key="user",1,0) | accum sno
| xyseries sno Key Value | fields - sno
0 Karma

Super Champion

i am not sure this will 100% work with your entire dataset, but it's a start. this is just a run-anywhere command. you can start using this with your data from the streamstats. as @FrankVI and @elliotrproebstel mentioned, it'll be easier help if we know how these match together in the first place or if this is your raw dataset and it's always user>ip>traffic in that order in the table.

|makeresults|eval data="key=user,value=bob key=ip,value=1.1.1.1 key=traffic,value=500 key=user,value=bill key=ip,value=2.2.2.2 key=traffic,value=200"|makemv data|mvexpand data|rename data as _raw|kv|table key value|streamstats first(value) as dummy reset_before="key=\"user\""|xyseries  dummy key  value|fields - dummy

How are you deciding which user/ip/traffic pairings go together? Just ordering? It might help to see the earlier part of the query to ensure we are aligning the data correctly.

0 Karma

Ultra Champion

What does your original data look like and how do you arrive at these results? Is it really just this, or is there any other data? Because from just the sample that you shared here, I think it will be quite hard to achieve what you want, after all: how should Splunk decide which rows of your original table to merge into 1 row in the results?

The only thing I can think of right now would be to try this through transactions, to pull each consecutive 3 lines into 1 transaction, and then work some magic to translate the key/value fields into fieldnames and values...

0 Karma

Engager

The original query might be my issue. I'm pulling it from an array of JSON objects that looks something like this:

{
"entry" : [
{
"dataName" : "user",
"dataValue" : "bob"
},
{
"dataName" : "ip",
"dataValue" : "1.1.1.1"
}
]
}

I used mvzip and mvexpand to get the first table, but that lost the "entry" grouping.

0 Karma

Ultra Champion

Not sure how you are ingesting this data, but I guess Ideally you'd want each "entry" to end up in a separate event. Would that be possible?

Alternatively you could extract the entire entry as a single multi-valued field, then run mvexpand to split it into separate events and only then extract the actual fields for each event.

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!