merge rows to one row if matches the result value by VM ( little complex situation)

[Satheesh_red]

I have a Splunk result like below.

VM	col1	col2
vm1	car	sedan
vm2	car	sedan
vm3	plane	Priv
vm4	bike	Fazer
vm5	bike	thunder

I would like to make them in a below format, would you please suggest me. I want to merge the same value into one (columns merge)

[glc_slash_it]

Hi,

you can group results by col1 

(search)
| stats values(VM) values(col2) by col1

------------
If this was helpful, some karma would be appreciated.

[Satheesh_red]

Hi @glc_slash_it 

Thanks for your reply,

It is giving a combination of several fields, but duplicates are showing up. I want to get rid of duplicates where two values matches and to show up as a single result value instead of two combinations.

And I want to display it by VM (in my example, VM columns will always be unique).

Regards,

[bowesmana]

To split by VM, just change it like this

| stats values(col*) as col* by VM

[Satheesh_red]

@bowesmana 

For more simplify it for you, split by VM and I'm looking to merge the values into one, example.

I have two values as 'car' those should be into one value and in single box. as like same if the result values matches should come. | stats values(col1) values(col2) is not helping as which is combination of values coming.

Regards, 

[yuanliu]

Splunk is not a substitute for spreadsheet application like Excel where you merge cells for visual effects.  It organizes data like a database.  As @glc_slash_it and @bowesmana explained, you either split by col1 or by VM.  You must ask yourself: Do you want to achieve Excel-like visual (split by col1) or do you want to maintain data logic (split by VM)?

If Excel-like effect is more important than data logic, the best you can emulate a cell merge as in your illustration is to use list function to retain the order of VM and col2, like

| stats list(*) as * by col1
| eval col2 = if(mvindex(col2, 0) == mvindex(col2, -1), mvindex(col2, 0), col2)
| table VM col*

Using the emulation given by @bowesmana, your mock data will give you

VM	col1	col2
vm4 vm5	bike	Fazer thunder
vm1 vm2	car	sedan
vm3	plane	Priv

This is the closest to your mock results.  It then becomes your job to convince your users that there is an invisible split line between vm4 and vm5, vm1 and vm2, etc.

[bowesmana]

You can't merge a single column across 2 other columns, as in your vm4/vm4 example. You can do

| stats values(VM) as VMs by col1 col2
| sort VMs

but it will give you separate rows for vm4/vm5

[Satheesh_red]

@bowesmana 

Ok, Thanks for your reply. I understand. Appreciated for your reply.

VM shouldn't be in values and combine, rest of the column's should combine if result values match, and show visualize. 

I'm still looking for some alternative options here. 

Regards,

[PickleRick]

Also please note (it's worth mentioning because that's not obvious) that if you aggregate some values into several multivalued fields (like in your case - multivalued VM field and col2 field) the contents of those multivalued fields are from now on independent on each other. So you can't for example - sort one using the order of the other.

Again - it's not a spreadsheet.

[bowesmana]

You can't combine Splunk columns inside a Splunk table, but you can make second and subsequent duplicates clear, like this example

| makeresults format=csv data="VM,col1,col2
vm1,car,sedan
vm2,car,sedan
vm3,plane,Priv
vm4,bike,Fazer
vm5,bike,thunder"

| stats values(col*) as col* by VM
| streamstats count as c1 by col1
| streamstats count as c2 by col2
| eval col1=if(c1>1, null(), col1)
| eval col2=if(c2>1, null(), col2)
| fields - c1 c2

[Satheesh_red]

@bowesmana 

I like this logic but could be hectic to use in my current environment. thanks.

Regards,