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
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
@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
this one does not work, it just displays one row with user text in it. Response from @DalJeanis works perfectly
I have updated, it should have been values(user)
and not last(user)
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
| 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!
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
@DalJeanis...You beat me to it 🙂
I was snoozing but we all 3 commented before @someshoni2!
@woodcock - you must have sneezed while typing @somesoni2.
... and it took all my self-control not to type "woodstock"...
I answer to everything.
Perfect, thanks it works