topic Re: Match search value to a range within a CSV Lookup in Getting Data In

Match search value to a range within a CSV Lookup

wrussell12 — Thu, 09 May 2019 01:36:58 GMT

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.

Re: Match search value to a range within a CSV Lookup

wrussell12 — Fri, 10 May 2019 01:08:28 GMT

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

It seems as though it never iterated though my lookup

Re: Match search value to a range within a CSV Lookup

koshyk — Fri, 10 May 2019 10:07:04 GMT

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.

Re: Match search value to a range within a CSV Lookup

wrussell12 — Fri, 10 May 2019 20:47:59 GMT

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());
       }
    }
}

Re: Match search value to a range within a CSV Lookup

woodcock — Sat, 11 May 2019 05:37:54 GMT

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