topic Re: How do I create a report that lists all servers and devices reporting to Splunk broken out by country? in Reporting

How do I create a report that lists all servers and devices reporting to Splunk broken out by country?

jldebell — Wed, 08 Apr 2015 19:32:02 GMT

We need to create a report that lists all devices and servers reporting into Splunk. We want to have the report broken out by country. We don't have an internal cross-reference to identify servers to countries. I was using this search based on other Answer questions.

 index=_internal source="/opt/splunk/var/log/splunk/metrics.log*" sourcetype="splunkd" fwdType="*" | dedup sourceHost|stats count  by hostname, sourceHost, fwdType, guid, os, arch

I tried to plug in the geoip/iplocation searches to see if we could obtain the location of our servers. I wasn't able to get results.

Please advise if you have suggestions.

Thanks!

Jenn

Re: How do I create a report that lists all servers and devices reporting to Splunk broken out by country?

harsmarvania57 — Wed, 08 Apr 2015 19:37:24 GMT

Are your forwarder have public IP address?? If your forwarders do not have public IP then you can't filter out with GeoIP/IPlocation.

Re: How do I create a report that lists all servers and devices reporting to Splunk broken out by country?

jldebell — Wed, 08 Apr 2015 19:52:57 GMT

Thank you for asking. I didn't think of that. The ip addresses are private, so GeoIP/IPLocation won't work.

Re: How do I create a report that lists all servers and devices reporting to Splunk broken out by country?

lguinn2 — Thu, 09 Apr 2015 00:53:33 GMT

You would need to make an inventory of all your devices. Store it in a CSV file like this

device,ip,country,latitude,longitude
www1,10.10.2.101,US,33.8090,-117.9190
www2,10.20.2.102,France,48.8687,2.7818

I added the lat/lon just for fun, but you don't need it. Use this CSV file to create a lookup table: Use field lookups...

If you do this, then you could run this search (if you named the lookup "device_lookup"):

index=_internal sourcetype="splunkd" group=tcpin_connections
| dedup sourceHost
| lookup device_lookup device as sourceHost 
| stats count  by hostname, sourceHost, fwdType, guid, os, arch, country

index=_internal sourcetype="splunkd" group=tcpin_connections
| eval sourceHost=coalesce(hostname, sourceHost) 
| lookup device_lookup device as sourceHost 
| stats sum(kb) as total_KB  by sourceHost, fwdType, guid, os, arch

Or even

index=_internal sourcetype="splunkd" group=tcpin_connections
| eval sourceHost=coalesce(hostname, sourceHost) 
| lookup device_lookup device as sourceHost 
| geostats latfield=latitude longfield=longitude sum(kb) as total_KB  by sourceHost