I would like to group it by Country, source IP, destination IP, sum(cs_bytes), sum(sc_bytes) where the country is the United States and count is greater than 1000. So far I have the | | iplocation c_ip | where Country="United States". However, when I do the stats count by all the fields I get a count of 1.
It is meant to look like this:
Country | Source IP | Dest IP | sum(cs_bytes) | sum(cs_bytes) | count
United States | 10.x.x.x | 195.x.x.x | 25,222,222 | 5,565,454 | 4000
@tundeawe, If you want to show communication between each c_ip and dest_ip, please try the following:
sourcetype=iis host=a OR host=B or host=C earliest=05/12/2018:0:0:0 earliest=05/13/2018:0:0:0
| stats count as EventCount sum(sc_bytes) as sc_bytes sum(cs_bytes) as cs_bytes by c_ip, dest_ip
| iplocation c_ip
| where Country="United States
| table Country c_ip dest_ip cs_bytes sc_bytes "Event Count"
If you want to show it only based on c_ip (without dest_ip or latest dest_ip or with a list of dest_ips, which seems to be the case based on dedup c_ip that you have performed)
sourcetype=iis host=a OR host=B or host=C earliest=05/12/2018:0:0:0 earliest=05/13/2018:0:0:0
| stats count as EventCount sum(sc_bytes) as sc_bytes sum(cs_bytes) as cs_bytes values(dest_ip) as dest_ip by c_ip
| iplocation c_ip
| where Country="United States
| table Country c_ip dest_ip cs_bytes sc_bytes "Event Count"
@tundeawe, If you want to show communication between each c_ip and dest_ip, please try the following:
sourcetype=iis host=a OR host=B or host=C earliest=05/12/2018:0:0:0 earliest=05/13/2018:0:0:0
| stats count as EventCount sum(sc_bytes) as sc_bytes sum(cs_bytes) as cs_bytes by c_ip, dest_ip
| iplocation c_ip
| where Country="United States
| table Country c_ip dest_ip cs_bytes sc_bytes "Event Count"
If you want to show it only based on c_ip (without dest_ip or latest dest_ip or with a list of dest_ips, which seems to be the case based on dedup c_ip that you have performed)
sourcetype=iis host=a OR host=B or host=C earliest=05/12/2018:0:0:0 earliest=05/13/2018:0:0:0
| stats count as EventCount sum(sc_bytes) as sc_bytes sum(cs_bytes) as cs_bytes values(dest_ip) as dest_ip by c_ip
| iplocation c_ip
| where Country="United States
| table Country c_ip dest_ip cs_bytes sc_bytes "Event Count"
thanks you!
@tundeawe, I am glad you found this useful! I have converted my comment to answer. Please Accept to mark this question as answered!
Please share your query.
If you're already doing a |stats
before the | iplocation...
, then you final stats
should say sum(count)
not just count. As @richgalloway suggested, share you full query for better/direct answer.
sourcetype=iis host=a OR host=B or host=C earliest=05/12/2018:0:0:0 earliest=05/13/2018:0:0:0
| iplocation c_ip | where Country="United States
| eventstats c as "Event Count" sum(sc_bytes) as sc_bytes sum(cs_bytes) as cs_bytes by c_ip
| dedup c_ip
| table Country c_ip dest_ip cs_bytes sc_bytes "Event Count"
This is my query thank you very much!
@somesoni2 and @richgalloway