combine multiple values of a table column based on other column value

[architkhanna]

I have table in my panel that has columns including owner,country,position,wbs.
Right now, seperate rows are made if one owner has multiple wbs.
I wanted to make a single row for a owner that has multiple wbs values and shown in a comma seprated wbs values.

Right now i have

owner wbs
abc 100
xyz 101
abc 102
abc 103

it should show

owner wbs
abc 100,102,103
xyz 101

[kamlesh_vaghela]

@architkhanna,

Can you please try this?

YOUR_SEARCH | stats delim="," values(wbs) as wbc by owner | nomv wbc

Sample Search:

| makeresults | eval owner="abc",wbs="100" | append [| makeresults | eval owner="xyz",wbs="101"] | append [| makeresults | eval owner="abc",wbs="102"] | append [| makeresults | eval owner="abc",wbs="103"] | stats delim="," values(wbs) as wbc by owner | nomv wbc

[Edited Search]

| makeresults | eval owner="abc",wbs="100" | append [| makeresults | eval owner="xyz",wbs="101"] | append [| makeresults | eval owner="abc",wbs="102"] | append [| makeresults | eval owner="abc",wbs="103"] | stats delim="," values(wbs) as wbc latest(_time) as Time by owner | nomv wbc | sort Time | fields - Time

You can | sort Time as per your requirement.

Added payrate column

Try:

YOUR_SEARCH | stats delim="," values(wbs) as wbc latest(_time) as Time values(payrate) as payrate by owner | nomv wbc |nomv payrate | sort - Time | fields - Time

Sample:

| makeresults | eval owner="abc",wbs="100",payrate="72" | append [| makeresults | eval owner="xyz",wbs="101",payrate="21"] | append [| makeresults | eval owner="abc",wbs="102",payrate="65"] | append [| makeresults | eval owner="abc",wbs="103",payrate="82"] | stats delim="," values(wbs) as wbc latest(_time) as Time values(payrate) as payrate by owner | nomv wbc |nomv payrate | sort - Time | fields - Time

Thanks

[architkhanna]

Just a thought.
This messes up the order of columns. This new column have moved to almost end of the table for which I need to scroll (there are many fields and earlier it used to come in 4th place)
Does Splunk decide the order randomly.

[architkhanna]

Also, I am getting duplicate rows now 😞

[kamlesh_vaghela]

Added new search in Answer. Please check and revert. Accept and upvote this answer if it helps you.

[architkhanna]

SO we are almost near.the table has many rows out of which I showed two collumns in example. Now using the following code
YOUR_SEARCH | stats delim="," values(wbs) as wbc by owner | nomv wbc

I am getting one row with wbs values comma seperated but like 101,102,101. Same value is repeated.this is due to the fact that I have two rows having same owner and same wbs but different other values.

owner wbs payrate
abc 100 72
xyz 101 21
abc 102 65
abc 100 82

it is showing

owner wbs payrate
abc 100,102,100 72
xyz 101 21

[kamlesh_vaghela]

Please check updated answer.

[kamlesh_vaghela]

@architkhanna,

Did you get chance to check my updated answer?

[architkhanna]

Thank you so so much.this works.