topic Re: Appending/Adding count of results in the column header in Splunk Search

Appending/Adding count of results in the column header

pushpender07 — Tue, 08 Aug 2017 18:22:03 GMT

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

Re: Appending/Adding count of results in the column header

DalJeanis — Tue, 08 Aug 2017 18:33:06 GMT

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

Re: Appending/Adding count of results in the column header

niketn — Tue, 08 Aug 2017 18:39:37 GMT

@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

Re: Appending/Adding count of results in the column header

niketn — Tue, 08 Aug 2017 18:40:32 GMT

@DalJeanis...You beat me to it 🙂

Re: Appending/Adding count of results in the column header

pushpender07 — Tue, 08 Aug 2017 18:48:40 GMT

Perfect, thanks it works

Re: Appending/Adding count of results in the column header

pushpender07 — Tue, 08 Aug 2017 18:50:26 GMT

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

Re: Appending/Adding count of results in the column header

woodcock — Tue, 08 Aug 2017 19:06:11 GMT

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

Re: Appending/Adding count of results in the column header

DalJeanis — Tue, 08 Aug 2017 19:13:55 GMT

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

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

Re: Appending/Adding count of results in the column header

woodcock — Tue, 08 Aug 2017 19:18:24 GMT

I answer to everything.

Re: Appending/Adding count of results in the column header

niketn — Wed, 09 Aug 2017 02:11:03 GMT

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

Re: Appending/Adding count of results in the column header

pushpender07 — Wed, 09 Aug 2017 16:23:56 GMT

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

Re: Appending/Adding count of results in the column header

niketn — Thu, 10 Aug 2017 01:36:49 GMT

| 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!