How can I group results without duplicates?

[rosho]

Hi

This is my command to find the number of times an authentication has been rejected.
But I would like to be able to eliminate duplicated results. for example I only have 2 host. But as I have 24 IPs, the "host" value appears 25 times.

 index=cisco_asa eventtype=cisco_authentication vendor_action="authentication Rejected"
| stats count by IP host server
| sort -count

Thank you

[koshyk]

So what you are doing is, you are "grouping" by IP, host & then by server. So host appearing multiple times, is because you asked it in the grouping.

So if you just need to group ONLY by host?

 index=cisco_asa eventtype=cisco_authentication vendor_action="authentication Rejected"
 | stats count by host
 | sort -count

If you want to view in a matrix format, you can do something like

 index=cisco_asa eventtype=cisco_authentication vendor_action="authentication Rejected"
 | stats count by host, IP
 | xyseries host, IP, count

[rosho]

I have only 2 hosts.

How can I make it look like this:

xx.xx.xx.xx
xx.xx.xx.xx BTS-BOU-5555X 8

207.164.79.74
69.156.29.208 BTS-MIS-5555X 12
70.24.234.54
70.54.70.131

Instead of:

xx.xx.xx.xx BTS-BOU-5555X 4
xx.xx.xx.xx BTS-BOU-5555X 4

207.164.79.74 BTS-MIS-5555X 3
69.156.29.208 BTS-MIS-5555X 3
70.24.234.54 BTS-MIS-5555X 3
70.54.70.131 BTS-MIS-5555X 3

[koshyk]

index=cisco_asa eventtype=cisco_authentication vendor_action="authentication Rejected"
  | stats count , values(IP) by host

[ddrillic]

dedup host should do it for you ; -)