Getting Data In

Match search value to a range within a CSV Lookup

Explorer

I have a drilldown search which can find a mobile devices lat/long. I need to find the general geofence area of the users home.

for example: The users home location is at: LAT 35.5 and LONG -118.5.
Their mobile device is reading 35.4, -118.6.

I need to increase the range to allow of margin of error, such as LAT BETWEEN 35.4 AND 35.6, and LONG BETWEEN -118.4 AND 118.6

I have a home.csv inputlookup such as:

LAT, LONG, HOME
35.5, -118.6  House1
30.5, -117.6  House2

The primary search returns Name, userLat, userLong
I need to create a table that can show:

NAME, LAT, LONG, HOME
Will, 35.4, -118.5, House 2

I currently have a search as

| tstats latest(username) as username, latest(userLat) as userLatitude, latest(userLong) as userLongitude 
| table username, userLatitude, userLongitude
| join [| inputlookup " home.csv" | eval Home=if( LAT<userLatitude + 0.01 
         AND LAT > userLatitude - 0.01 AND 
        LONG<userLongitude + 0.01 AND LONG > userLongitude - 0.01, HOME, "")  ]

Depending on my search, no results are returned, all users have the same home, as if it does not iterate though the homes.csv list.

Update:
I have exhausted all my ideas, someone mentioned converting my CSV into a KML geolocation point file and creating alerts.

0 Karma

Esteemed Legend

The best way to do this is to create your own external lookup (AKA scripted lookup😞
https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Configureexternallookups

An even easier option would be to convert your lookup to a kmz and use a geospatial lookup:
https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Configuregeospatiallookups

0 Karma

Super Champion

I feel, Splunk has to improve "lookup" command to cater for ranges
But you can try an idea like

| makeresults
| eval latitude=35.4
|map search="|inputlookup home.csv | eval maxLAT=LAT+0.2| eval minLAT=LAT-0.2| where (maxLAT > $latitude$  AND minLAT < $latitude$)"

You can extend this to Longitude and so on.

0 Karma

Explorer

I've tried this solution, I cannot get the parent search to expose the variables in the subsearch.

either the lookup LAT or the search LAT is null, I cannot figure out how to get them to be on the same line.

Pseudocode would look something like this:

for(User u : users) {
   for(Home h: homes) {
      if(u.getLat() + 2 < h.getLat() && u.getLat() -2 > h.getLat() ) {
         u.setHome(h.getHome());
       }
    }
}
0 Karma

Explorer

I have tried to make results using eval and subsearch the inputlookup table.

| makeresults
| eval Status="Hello World"
| eval latitude=25.1
|join type=left [|inputlookup "airport_list.csv" | where LAT >= latitude | eval Status=if(LAT>latitude, Home, "NOT UPDATED")  | table LAT, Status, latitude]

Lookup Table
LAT, LONG, HOME
35.5, -118.6 House1
30.5, -117.6 House2
2.2 , 35 House3

My results from this test return:
LAT | Status | _time | latitude
empty | Hello World | timestamp | 25.1

It seems as though it never iterated though my lookup

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!