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.
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
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.
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...
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.
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.