aggregate rows with same value hide count

[lemospt]

Hi guys,

i have the following query that produces table below

index=core_ct_report_* 
| eval brand=case(like(report_model, "cfg%"), "grandstream", like(report_model, "cisco%"), "Cisco", like(report_model, "ata%"), "Cisco", like(report_model, "snom%"), "Snom", like(report_model, "VISION%"), "Snom", like(report_model, "yealink%"), "Yealink", 1=1, "Other")
| stats count by fw_version,report_model,brand

| table brand report_model fw_version count |sort report_model, count desc

In this table i want to group the rows with the same value in report_model column, i use stats values() to achive that as follows

index=core_ct_report_* 
| eval brand=case(like(report_model, "cfg%"), "grandstream", like(report_model, "cisco%"), "Cisco", like(report_model, "ata%"), "Cisco", like(report_model, "snom%"), "Snom", like(report_model, "VISION%"), "Snom", like(report_model, "yealink%"), "Yealink", 1=1, "Other")
|stats count by fw_version,report_model,brand | stats values(brand) as brand values(fw_version) as fw_version values(count) as count by report_model

|table brand report_model fw_version count

but with this query the count is also grouped, on 6th row there are count values missing, the count missing has the value 1 so only one '1' is showed.

i can't remove count from stats values() or the count values doesn't appear in final table.

What i'm doing wrong?

Thanks in advance for your help.

[ITWhisperer · Accepted solution]

Values gives you an ordered set of unique values, try using the list aggregation function instead

index=core_ct_report_* 
| eval brand=case(like(report_model, "cfg%"), "grandstream", like(report_model, "cisco%"), "Cisco", like(report_model, "ata%"), "Cisco", like(report_model, "snom%"), "Snom", like(report_model, "VISION%"), "Snom", like(report_model, "yealink%"), "Yealink", 1=1, "Other")
|stats count by fw_version,report_model,brand | stats values(brand) as brand list(fw_version) as fw_version list(count) as count by report_model

|table brand report_model fw_version count