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
@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
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.
Also, I am getting duplicate rows now 😞
Added new search in Answer. Please check and revert. Accept and upvote this answer if it helps you.
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
Please check updated answer.
@architkhanna,
Did you get chance to check my updated answer?
Thank you so so much.this works.