Count number of events from IIS logs.

[tundeawe]

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

[niketn · Accepted solution]

@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]

thanks you!

[niketn]

@tundeawe, I am glad you found this useful! I have converted my comment to answer. Please Accept to mark this question as answered!

[richgalloway]

Please share your query.

[somesoni2]

If you're already doing a |stats before the | iplocation..., then you final statsshould say sum(count) not just count. As @richgalloway suggested, share you full query for better/direct answer.

[tundeawe]

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