counting number of events based on city with geoip

[nina15]

hi...
I want to use advanced charting reports and I actually need to have a pie chart of cities of the country the query was originated from.
up to this point, I can list down queries from that country and cities I want but Im having dificulties to count them. this:

sourcetype="*" | geoip | search geoip_country_name="Finland"

lists me all events from Finland, for all Finland's cities. I tried:

sourcetype="*" | geoip | search geoip_country_name="Malaysia" | timechart count by geoip_city

which shows me a piechart that counts values of all timestamps!

how can I simply count the geoip_city???

[nina15]

I found the answer myself... 🙂
finally to get exactly what I want, it actually has to be like this:

SourceIP=* | geoip SourceIP | search SourceIP_country_name="United States" | stats count(SourceIP_city) as "City Count" by SourceIP_city | rename SourceIP_city as City

[nina15]

thats great... thanks...
and when I want to specify a country??
i've tried these ways, doesnt work and i get errors:

SourceIP=* | geoip SourceIP SourceIP_country_name="United States" | stats count(clientip_city) as "City Count" by clientip_city | rename clientip_city as City

SourceIP=* | geoip SourceIP | SourceIP_country_name="United States" | stats count(clientip_city) as "City Count" by clientip_city | rename clientip_city as City

SourceIP=* | geoip SourceIP_country_name="United States" | stats count(clientip_city) as "City Count" by clientip_city | rename clientip_city as City

SourceIP=* | geoip SourceIP AND SourceIP_country_name="United States" | stats count(SourceIP_city) as "City Count" by SourceIP_city | rename SourceIP_city as City

SourceIP=* | geoip SourceIP | stats count(SourceIP_city) as "City Count" by SourceIP_city  AND SourceIP_country_name="United States" | rename SourceIP_city as City

what is the correct way of querying it?

[dmaislin_splunk]

SourceIP=* | geoip SourceIP | stats count(clientip_city) as "City Count" by clientip_city | rename clientip_city as City

[nina15]

ok... Im getting confused and I need a bit of help here...
the clientip here is a predefined search operator/field? or a field name that should be defined by user?
none of the above actually gives me any results...
maybe I should explain that for all events I am implementing field extraction using field separator "tab" and the field that events originate from is called SourceIP

[dmaislin_splunk]

Is this what you want?

clientip=* | geoip clientip | stats count(clientip_city) as "City Count" by clientip_city | rename clientip_city as City

Or

clientip=* | geoip clientip | timechart span=1d count(clientip_city) as "City Count" by clientip_city | rename clientip_city as City

OR

clientip=* | geoip clientip | chart count(clientip_city) as "City Count" by clientip_city | rename clientip_city as City