My requirement is to summarize the number of shipments to all zip codes for a particular day and plot the shipment count by the corresponding zip code in a Clustered Single Value Map Visualization or any map in Splunk. I used the following search but it seems to be summarizing by the total number of Zip codes instead of the total shipments by Zipcode.
source=shipment_details | stats count by SHIP_ZIPCODE | lookup geo_zipcode Zipcode AS SHIP_ZIPCODE | rename Lat as latitude, Long as longitude | geostats latfield=latitude longfield=longitude count
I am not sure if I am using the wrong map. Any suggestion would be much appreciated
Avoid leaving a field named as count, because it will bite you like in this example. The geostats command was happily counting the records for you -- records that had already been pre-summarized.
| stats count as zipcount by SHIP_ZIPCODE
| lookup geo_zipcode Zipcode AS SHIP_ZIPCODE
| rename Lat as latitude, Long as longitude
| geostats latfield=latitude longfield=longitude sum(zipcount) as count
Thanks for your quick reply in providing a suggestion for optimizing the query. I still have the two issues where the shipment counts are not tallying to the numbers I have in the dataset and the zipcode does not show when I drill down on the heatmap