Appending/Adding count of results in the column header

[pushpender07]

Hi All,

I have a search - index=ABC sourcetype=XYZ | stats values(user), dc(user) by region | transpose header_field=region | fields – column which produces the following result:

Region1      Region2          Region3
ABC             XYZ           MNO
PQR             STU           BCD
MKL     
3              2                2

I want the count of distinct users to be appended to the column name in the table. Final result should look like the table below. Is this possible in Splunk?

Region1(3)  Region2(2)  Region3(2)
ABC        XYZ         MNO
PQR        STU         BCD
MKL     

Added a picture of the table to make it clear

[niketn]

@pushpender07, try the following:

index=ABC sourcetype=XYZ 
| stats values(user) as user dc(user) as dc_user by region 
| eval region=region."(".dc_user.")"
| transpose header_field=region 
| search column=user
| fields – column

[pushpender07]

this one does not work, it just displays one row with user text in it. Response from @DalJeanis works perfectly

[niketn]

I have updated, it should have been values(user) and not last(user)

[pushpender07]

still does not work, what is the use of search column = user? It shows the same response with one row and "user" as text in it

[niketn]

| search column=user gets rid of column dc_user (count of distinct users) after transpose, since count is already appended to table header region. You can remove pipes 4, 5 and 6 and then put them back in the query one by one to understand what they are doing.

If results are not the way you expect, maybe I missed something you want. As far as there is another answer solving your problem you should be good!

[DalJeanis · Accepted solution]

Try this...

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

[niketn]

@DalJeanis...You beat me to it 🙂

[woodcock]

I was snoozing but we all 3 commented before @someshoni2!

[DalJeanis]

@woodcock - you must have sneezed while typing @somesoni2.

... and it took all my self-control not to type "woodstock"...

[woodcock]

I answer to everything.

[pushpender07]

Perfect, thanks it works