How do I show the number of events per city in Geostats visualization, not relating to IP addresses?

[colinmchugo]

Hi Splunk Gurus,

If I have city, department and office location in my data set. How do I show the number of events per city? I also want to be able to show a breakdown per department and finally show a breakdown of per office location.

So I want to a map visualization of the world and it shows X amount of events in two categories. Let's call it category A & category B so I can see for example that in New York there are 100 Category A events and 10 Category B events for that city. I also want to be able (maybe a new map) to show the breakdown of category a and b per department per city or department per state.

Thanks really appreciate this.

[DavidHourani · Accepted solution]

Hi colin,

If you want to split by category and then by department then you need three maps, or you can do that with a single map and a tweaked dashboard input. The search for your global per category map should be something like this :

yoursearchhere |  geostats latfield=LatitudeFieldFromSearch longfield=LongitudeFieldFromSearch count by category

The map per department for cat A should be like this :

yoursearchhere |search category=A |  geostats latfield=LatitudeFieldFromSearch longfield=LongitudeFieldFromSearch count by department

The map per department for cat B should be like this :

yoursearchhere |search category=B |  geostats latfield=LatitudeFieldFromSearch longfield=LongitudeFieldFromSearch count by department

More info on the geostats command here :
http://docs.splunk.com/Documentation/Splunk/7.0.1/SearchReference/Geostats

If you are using choropleth you will only be able to have a map colored based on a global count and not by category.
More info here :
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Viz/ChoroplethGenerate

Regards,
David

[colinmchugo]

Got it working it was a misspelling thanks

[DavidHourani]

Great! Please accept answer if it helped 😉

[colinmchugo]

Thanks David but that's not working for me, unfortunately. So I've got the following

source="X.csv" host="Y" index="main" sourcetype="xx.csv" 
| lookup officegeo2018.csv "Office Code" as "Office Location" 
| table Created Category Username Hostname Hostname "Office Location" Dept Country City Office Lattitude Longitude

The geostats with what you said does not work just shows nothing even though there is data there.
Thanks again.

[DavidHourani]

Can you share a line of data from the query you sent ? --- make sure u mask any sensitive info