Splunk Search

How to rearrange table by values in a column

andrewhlui
Explorer

So I have the following data as output statistics from a search:

User      Group         Number
Andy          A            123
Andy          B            123
Andy          C            123
Bob           A            123
Bob           B            123
Cam           A            123
Cam           B            123
Cam           C            123

How can I rearrange it so that it becomes:

User         A         B        C
Andy       123       123      123
Bob        123       123        0
Cam        123       123      123

Also, what is this rearranging called?

0 Karma
1 Solution

andrewhlui
Explorer

Never mind, I figured out an answer:

... | xyseries User, Group, Number

View solution in original post

0 Karma

abalogh_splunk
Splunk Employee
Splunk Employee

Hi,

There is a command called transpose.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose

Try it out with DalJeanis example:

| makeresults
| eval User="Andy Bob Cam", Group="A B C"
| makemv User
| mvexpand User
| makemv Group
| mvexpand Group
| Where NOT (Group="C" AND User="Bob")
| eval Number = 123
| rename COMMENT as "the above just enters your test data"
| chart sum(Number) over User by Group
| transpose

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

You are looking for the chart command...

| makeresults 
| eval User="Andy Bob Cam", Group="A B C" 
| makemv User 
| mvexpand User 
| makemv Group 
| mvexpand Group 
| Where NOT (Group="C" AND User="Bob") 
| eval Number = 123
| rename COMMENT as "the above just enters your test data"

| chart sum(Number) over User by Group

...and then after you review the above, add this on the end and look at this handy little reverse tool...

| untable User Group Number
0 Karma

gcusello
Legend

Hi andrewhlui,
add to your search

| chart sum(Number) AS Number over User BY Group

If you like you can use different functions instead sum.
Bye.
Giuseppe

0 Karma

andrewhlui
Explorer

Never mind, I figured out an answer:

... | xyseries User, Group, Number

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@andrewhliu - yep, xyseries will work too. Check out the untable command on the results.

We're converting your comment to an answer. Please accept the answer so that the question will show as answered.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!