Splunk Search

Can someone provide an example for Geom counts based on client IP?

spammenot66
Contributor

Hi all,

I'm trying to generate counts/hits based on client ip and create a map visualization similar to the one found on the site for 6.3 Geographic data visualizations. Can someone help and give a simple example?

1 Solution

arobbins_splunk
Splunk Employee
Splunk Employee

Something like this should work for the SPL:

assuming that the IP address you're interested in is "client_ip"

...generating search...
| iplocation client_ip
| stats count by Country
| geom geo_countries featureIdField=Country

you can then set the visualization type to Choropleth

View solution in original post

maraman_splunk
Splunk Employee
Splunk Employee

Hello,

just got the same error message. I had a typo just after geom...

with your version, that would give :
sourcetype="dcapi:realtime" |
iplocation c_ip|
stats count by Country|
geom geo_country featureIdField=Country

->Error in 'SearchOperator:Geom': could not resolve

fixed version
sourcetype="dcapi:realtime" |
iplocation c_ip|
stats count by Country|
geom geo_countries featureIdField=Country

Your mileage may vary but that's probably a typo in the geom command parameters (so the geom command won't find the info needed for the map, which would lead to this error I think)

spammenot66
Contributor

I've figured out the issue for anyone who gets the error: Error in 'SearchOperator:Geom': could not resolve

What you'll want to do is go to Setting > Lookups > Lookup Definition.

  • For app context drop down, select All.
  • For Owner drop down, select Any.
  • in the text box next to the green button with magnifying glass, type in geo

Make sure it lists the following:
Name Type
geo_attr_countries File
geo_attr_us_states File
geo_countries geo
geo_sf_neighborhoods geo
geo_us_states geo
geoip external

In my case, geo_countries, geo_sf_neighborhoods, geo_us_states and geoip was not avialable. To make these queries work, you're calling geo_us_states and/or geo_countries, so it needs to be there.

What i can't figure out is how to add these files manually. When i tried to define a new lookup for geo_countries, the type "geo" was not available, which means I wouldn't even be able to upload the kmz file even if i got access to it. The other thing i can't figure out is why these files and setup aren't readily available as part of upgrading to 6.3 on our onpremise enterprise server.

When i updated my local version at home, this setup was automatically and readily available. Can someone assist in providing info on how to manually update the system to include these geo lookups, that would be incredibly helpful.

ghendrey_splunk
Splunk Employee
Splunk Employee

The only things special about the lookup is that is has external_type=geo and the filename must refer to a .kmz file residing in the "lookups" folder. This is what I said earlier in the thread about how to manually define your geo lookups. Essentially you just need to define the lookup.:
"I tracked down "could not resolve". This actually is occurring because the "filename" key cannot be found in transforms.conf, corresponding to the geo lookup named "geo_countries". Please locate your transforms.conf file that contains a stanza named [geo_countries]. In this stanza you should see something like:
[geo_countries]
external_type=geo
filename=XXX
(where XXX is the name of a .kmz file that resides in a folder named "lookups" under the splunk etc root)."

0 Karma

jluo_splunk
Splunk Employee
Splunk Employee

Are you on Splunk 6.3? IIRC, geom wasn't implemented until 6.3. I could be wrong though...

spammenot66
Contributor

here's my query

sourcetype="dcapi:realtime" |
iplocation c_ip|
stats count by Country|
geom geo_countries featureIdField=Country

If i run it without the last line geom "geo_countries featureIdField=Country", it seems to return results fine
Country count
1 Spain 2
2 United States 126

But the minute i add the last line, i get the following error:
Error in 'SearchOperator:Geom': could not resolve
The search job has failed due to an error. You may be able view the job in the Job Inspector.

0 Karma

mikenagra
Explorer

you can't use geo_countries unless you declare it first before the pipe
| lookup geo_countries longitude as Long, latitude as Lat

0 Karma

arobbins_splunk
Splunk Employee
Splunk Employee

... I'm not sure how to help with that... but I'm going to get in front of someone who may... stay tuned...

0 Karma

strangelaw
Explorer

I have similar thingy ongoing. My (workable) search is:

index="feed_inputips" source="/home/splunk/inputs/inputips.csv" | lookup geo_countries longitude as Longitude, latitude as Latitude | stats count by featureId | geom geo_countries

Which allows me to have map with count of events (featureId) - but I am unable to have field 'SRC_ADDRESS' on the map - which IS available on inputips - can anyone provide assistance on this? How about captions?

Does it matter if this run on Search (viz), not on Dashboard?

arobbins_splunk
Splunk Employee
Splunk Employee

the choropleth map will only show a single aggregate split by region...

given that your aggregate is count per region, that is what the choropleth will show

ghendrey_splunk
Splunk Employee
Splunk Employee

it would be nice if the choropleth could render the count onto the map. It currently only shows the count when you mouseover the region.

arobbins_splunk
Splunk Employee
Splunk Employee

Something like this should work for the SPL:

assuming that the IP address you're interested in is "client_ip"

...generating search...
| iplocation client_ip
| stats count by Country
| geom geo_countries featureIdField=Country

you can then set the visualization type to Choropleth

View solution in original post

spammenot66
Contributor

im getting the following error: "Error in 'SearchOperator:Geom': could not resolve"

0 Karma

mikenagra
Explorer

you can't use geo_countries unless you declare it first before the pipe
| lookup geo_countries longitude as Long, latitude as Lat

arobbins_splunk
Splunk Employee
Splunk Employee

@mikenagra, I'm not sure if I understood your comment completely. I think that you stated that you can only use geo_countries in the geom command IFF geo_countries was used in the lookup command previously. This is not quite the case.

geo_countries, and geo_us_states are geo-lookup files. They can be used by two commands: lookup and geom.

Lookup uses the geo-lookup file to derive a region id (featureId) from specified per-row lat & lon
Geom uses a geo-lookup file -- and the field specified by featureIdField (defaults to "featureId") -- to insert a GeoJSON blob into the result set that represents the border of the referenced region. If you derived the featureId via some method other than using lookup you may use geom with geo_countries or geo_us_states as the other reference to the geo-lookup file.

0 Karma

mikenagra
Explorer

Makes sense. Maybe it was just my specific case where I had Lat and Long values in my indexed data that I needed to have a lookup to correlate this data. Without it, geom does absolutely nothing.

ie. This works:
index=main | lookup geo_us_states longitude as Long, latitude as Lat | stats count by featureId | geom geo_us_states

ie. This does not work:
index=main | stats count by featureId | geom geo_us_states

arobbins_splunk
Splunk Employee
Splunk Employee

your first case works because featureId is a field that it output from lookup

your second case does not work because featureId does not yet exist

index=main
| stats count by foo

| geom geo_us_states featureIdField=foo

should work if foo is in index=main, and the values of foo map to the names of US states as stored in geo_us_states. To see what is expected, check out geo_attr_us_states ( |inputlookup geo_attr_us_states )

0 Karma

mikenagra
Explorer

My foo is a zip-code which I use a lookup table to convert to Lat and Long values. In this case my foo does not map directly to the State names. There is an intermediary step that is taken care of by my transforms.conf and props.conf, but it can be inline search as well (| lookup ziplookup Zipcode as LOCATION OUTPUT Lat, Long). For ME, my LOCATION being a zip-code does not map at all to geo_us_states. I get a table with LOCATION and count but not State, if I don't do the ( | lookup geo_us_states longitude as Long, latitude as Lat) to correlate the data. Thanks for the discussion, it does help to learn this stuff. 😃 I could totally be doing this ass-backwards as well.
P.S. I am using this data for a choropleth.

This works:
index=main
| lookup ziplookup Zipcode as LOCATION OUTPUT Lat, Long
| lookup geo_us_states longitude as Long, latitude as Lat
| stats count by featureId
| geom geo_us_states

This does not work:
index=main
| lookup ziplookup Zipcode as LOCATION OUTPUT Lat, Long
| stats count by LOCATION
| geom geo_us_states featureIdField=LOCATION

0 Karma

arobbins_splunk
Splunk Employee
Splunk Employee

You're not doing anything backwards -- you just need to lookup into a different KML/KMZ. As you figured out, geo_us_states will only work if your featureId maps to state names. Similarly, geo_countries will only work if your featureId maps to country names.

You need a KML/KMZ file that maps to zip codes.

Here is a link to the blog post that @mporath wrote about adding your own KML/KMZ lookup files:

http://blogs.splunk.com/2015/10/01/use-custom-polygons-in-your-choropleth-maps/

Good luck!

0 Karma

ghendrey_splunk
Splunk Employee
Splunk Employee

Usually the geom command is applied after both a lookup has been done against the geo lookup table and the stats. This insures that each record that you stat is accompanied by the correct name of the geo-entity from the geo lookup table. Since you are not applying a geolookup, but rather just attaching a country name via geoIp, my suspicion is that the iplocation command may be attaching country names that are not in the geo spatial lookup. My further suspicion is that a blank country name is getting attached by the geoip. Then the geom command says "cannot resolve [blank]" since it cannot find the geometry for an empty country name. One thing you can do is dig out the log (inspect job through the UI, then click to see the dispatch log). I can tell a lot from those logs. The second thing is to use an eval to make sure there are no blank country names passing through from stats.

0 Karma

arobbins_splunk
Splunk Employee
Splunk Employee

Could you post your entire search?

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.