How to count each tupple values by day of the week

[nishan_perera]

I got a query like this,

%asa deny OR denied | eval dest_port = if(isnum(dest_port),dest_port,00)| eval denyTuppleValue = src_ip."-".dest_ip."-".dest_port | stats values(denyTuppleValue),count(denyTuppleValue) by date_wday

Result

date_wday   values(denyTuppleValue) count(denyTuppleValue)
wednesday   xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx   520
                     xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx
                    xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx
    thursday  xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx   10
                   xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx
                  xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx

But i need to get the count for each denyTuppleValue not the count for the day. Which would look like

date_wday   values(denyTuppleValue) count(denyTuppleValue)
wednesday   xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx   110
           xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx      20
            xx.xxx.xxx.xxx-xxx.xx.xxx.x-xx     130

Any ideas would be much apprecieated.

[somesoni2 · Accepted solution]

Give this a try

 %asa deny OR denied | eval dest_port = if(isnum(dest_port),dest_port,00)| eval denyTuppleValue = src_ip."-".dest_ip."-".dest_port | stats count(denyTuppleValue) as count by date_wday,denyTuppleValue | stats values(denyTuppleValue) values(count) by date_wday

[nishan_perera]

this is also works, but you need to click the day inorder to go to the view i want. but yeah i can reuse your query to get the values i'm after and saving into a CSV file. thanks for the help

[martin_mueller]

You can even leave off the values(denyTuppleValue) when grouping by that very field.

[nishan_perera]

true , i should get rid of that

[musskopf]

What happen if you change your search to:

%asa deny OR denied | eval dest_port = if(isnum(dest_port),dest_port,00)| eval denyTuppleValue = src_ip."-".dest_ip."-".dest_port | stats values(denyTuppleValue),count(denyTuppleValue) by date_wday, denyTuppleValue

[nishan_perera]

this works , but the day get repeated for each value. but i think this is what i after. Cheers for the help