Splunk Search

How can I change the formatting of a Splunk table?

pushpender07
Explorer

Hi, I have a search -

index=ABC sourcetype=XYZ 
    | stats values(user), dc(user) as usercount by region 
    | eval region = region." (".usercount.")"
    | fields - usercount
    | transpose header_field=region 
    | fields - column

which gives me a list of user names by region as shown in picture 1 below. What I would prefer to see is the formatting in picture 2--where if number of user names become more than 3, the 4th one is shown adjacent to 1st. For the search I'd like to see that if the number of usernames is more than 15, they should be displayed in a two column kind of view.

Is this possible in Splunk?alt text

1 Solution

DalJeanis
SplunkTrust
SplunkTrust

Okay, you are getting VERY fiddly with how you present stuff. Haven't you got REAL work to do? 😉

 index=ABC sourcetype=XYZ 
 | stats values(user) as user, dc(user) as usercount by region
 | eval region = region." (".usercount.")"
 | fields - usercount
 | mvexpand user
 | streamstats count as reguserno by region
 | eval reguserno=reguserno%3
 | stats values(user) as user by region reguserno 
 | nomv user
 | stats values(user) as user by region 
 | transpose header_field=region 
 | fields - column

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

Okay, you are getting VERY fiddly with how you present stuff. Haven't you got REAL work to do? 😉

 index=ABC sourcetype=XYZ 
 | stats values(user) as user, dc(user) as usercount by region
 | eval region = region." (".usercount.")"
 | fields - usercount
 | mvexpand user
 | streamstats count as reguserno by region
 | eval reguserno=reguserno%3
 | stats values(user) as user by region reguserno 
 | nomv user
 | stats values(user) as user by region 
 | transpose header_field=region 
 | fields - column

DalJeanis
SplunkTrust
SplunkTrust

Note that this solution assumes the length of your user names are identical.

0 Karma

pushpender07
Explorer

Haha, right now it is like "Give them a finger, and they'll take the whole hand" situation for me :).

Thanks for this, not working right now, maybe because user names are of different length

One question - why is the length of user name a factor? Asking because in my case I will have usernames with different length

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@pushpender07 -

That method will spackle the different values together on one line. If they don't have the same length, then it would look wonky. Here's run-anywhere code to prove that it works as written -

 | makeresults 
 | eval mydata="Region1@ABC,BCD,CDE,FEF Region2@XYZ,MNO,PQR Region3@123,456,789,234,345,678,910"
 | makemv mydata 
 | mvexpand mydata 
 | eval region=mvindex(split(mydata,"@"),0) 
 | eval user=mvindex(split(mydata,"@"),1)
 | makemv delim="," user 
 | mvexpand user  
 | table user region
 | rename COMMENT as "above just creates test data"

 | stats values(user) as user, dc(user) as usercount by region
 | eval region = region." (".usercount.")"
 | fields - usercount
 | mvexpand user
 | streamstats count as reguserno by region
 | eval reguserno=reguserno%3
 | stats values(user) as user by region reguserno 
 | nomv user
 | stats values(user) as user by region 
 | transpose header_field=region 
 | fields - column

Splunk doesn't like leaving spaces between items. It is possible to do, but unfortunately, the interface does not present the results in a fixed-width font anyway, so "columnizing" results internal to a field isn't really an option.

Here's a version that will create multiple columns for each region, and will go beyond 3 vertically (MaxColLength) if a MaxColLength of 3 would push the number of horizontal columns for a single region to more than 3

 | stats values(user) as user, dc(user) as usercount by region
 | eventstats max(usercount) as maxusers
 | eval region = region." (".usercount.")"
 | eval MaxColLength=if(maxusers<9,3, ceiling(maxusers/3))
 | fields - usercount maxusers
 | mvexpand user
 | streamstats count as reguserno by region
 | eval reguserno=floor((reguserno+MaxColLength-1)/MaxColLength) 
 | stats values(user) as user by region reguserno 
 | eval region = region.substr("          ",1,reguserno)
 | fields - reguserno
 | transpose 0 header_field=region
 | fields - column

Note - I just can't stop giving cookies to that mouse, can I?

0 Karma

pushpender07
Explorer

Note - I just can't stop giving cookies to that mouse, can I? - Haha, thanks a lot. Cookies are much appreciated. This works for me, thanks a ton.

I will try to merge the region name as in this case the region name is repeated in the column.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@pushpender07 - sorry, that's a glass of milk I don't have. There ought to be a way to make the column headings after the first one disappear, but I couldn't find a way that didn't require a straw, and then a napkin, and then eventually starting over with a moose.

0 Karma

pushpender07
Explorer

haha, thanks :). will post if I am able to do so.

0 Karma

adonio
Ultra Champion

i saw an answer here before, i think it involves modifying or creating a .css
the great thing about it that iirc it can be done in the dashboard level
here is a related answer as i couldn't find the one i though ive seen:
https://answers.splunk.com/answers/277847/how-does-one-change-results-table-font-size-in-a-d.html
hope it helps

0 Karma

pushpender07
Explorer

Thanks, don't know how to use .css, will find out and check. I might not have the required permissions level.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...