how to show Value with individual count

[Bhargav99]

Hello Folks I am new to plunk please help me out of this I need Value with its individual count shown next to it .
I wrote this query

index=* sourcetype=my _sourcetype tag_type = AGENTID_COMPLETED User_Id="*"|dedup agentId | stats values(device_type_list) AS device_list,count AS "# of devices" by User_Id | eval device_list =mvjoin(device_list,",")



User_Id           | device_list | # of devices
x@gmail.com         WIN           1
ami@gmail.com       WIN           1
apea@gmail.com      WIN           3
bbir@gmail.com      LINUX         1
bmci@gmail.com      WIN           7
dn@gmail.com        LINUX         1
dslev@gmail.com     WIN           2
ee@gmail.com        LINUX,WIN     14   .................. I need as         LINUX(4), WIN (10)
fburr@gmail.com     WIN        2
fkong@gmail.com     WIN           3
gann@gmail.com      WIN           1
glyel@gmail.com     WIN           1
jlau@gmail.com      LINUX, WIN    7  ..................I need as         LINUX(4), WIN (10)

Thank you

[somesoni2 · Accepted solution]

Try this

index=* sourcetype=my _sourcetype tag_type = AGENTID_COMPLETED User_Id="*"|dedup agentId | stats count by User_Id device_type_list | eval device_type_list=device_type_list."(".count.")" | stats values(device_type_list) AS device_list,sum(count) AS "# of devices" by User_Id | eval device_list =mvjoin(device_list,",")

[Bhargav99]

seems perfect !!!!!! Only will try to remove the count for the single ones. Thank you so much!!!!!!!

[martin_mueller]

Replace your stats with this:

... | stats count as "# of devices" by User_Id device_type_list | rename device_type_list as device_list

[Bhargav99]

Hey I tried This but I need unique User Id to be shown in same record.