Splunk Search

How can I combine multiple rows into 1 row?

cfurstenau
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)

somesoni2
SplunkTrust
SplunkTrust

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

cmerriman
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

elliotproebstel
Champion

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

FrankVl
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

cfurstenau
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

FrankVl
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
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...