Assigning Lat and Lon to multiple locations.

[dathrimar]

Hi, Im having trouble getting the latitude and longitudes for a cluster map to work properly when given computer names with know coordinates. The data in the index doesnt have the lat or lon in it unfortunately.

In this example I am trying to figure out a way to eval against multiple standard naming conventions to assign their latitude and longitude. If i had 5 locations with the corresponding naming conventions where xxxx is a unique identifier within those 5 location and I know the latitude and longitude of each location. How would I go about evaluating every field in the "Computer Name" column for which location it belongs to and then apply the corresponding Lat & Lon so it can be plotted on a cluster map. 

See below naming convention and their corresponding Lat and Lon example.

Loc1xxxx: Lat 10.1010 Lon -10.10.10, Loc2xxxx: Lat 20.2020 Lon -20.2020, Loc3xxxx: Lat 30.3030 Lon -30.3030, Loc4xxxx: Lat 40.4040 Lon -40.4040, Loc5xxxx: Lat 50.5050 Lon -50.5050

For this example each location with have 5 computers for simplicity sake. Loc10001- Loc10005, etc.

Here is what I have so far which will resolve the lat and lon for a single location but I am having trouble figuring out how to expand it to other locations.

index="index_name"

| dedup "Computer Name"

| rename "Computer Name" as WKS

| eval lat=if(match(WKS, "Loc1"), "10.1010", "0")

| eval lon=if(match(WKS, "Loc1"), "-10.1010", "0")

| geostats latfield=lat longfield=lon count

[richgalloway · Accepted solution]

There are a couple of ways to do this.  The first is to replace the if functions with case functions so multiple locations can be tested.  You could also use nested ifs, but case is easier to read, IMO.

index="index_name"
| dedup "Computer Name"
| rename "Computer Name" as WKS
| eval lat=case(match(WKS, "Loc1"), "10.1010", match(WKS, "Loc2"), "20.2020", match(WKS, "Loc3"), "30.3030", match(WKS, "Loc4"), "40.4040", match(WKS, "Loc5"), "50.5050", 1==1,"0")
| eval lon=case(match(WKS, "Loc1"), "-10.1010", match(WKS, "Loc2"), "-20.2020", match(WKS, "Loc3"), "-30.3030", match(WKS, "Loc4"), "-40.4040", match(WKS, "Loc5"), "-50.5050", 1==1,"0")
| geostats latfield=lat longfield=lon count

 Another and more extensible method is to use a lookup file.  The lookup file would contain locations and their corresponding coordinates.  It might look like this:

Location  Lat     Lon
Loc1xxxx  10.1010 -10.1010
Loc2xxxx  20.2020 -20.2020
Loc3xxxx  30.3030 -30.3030
Loc4xxxx  40.4040 -40.4040
Loc5xxxx  50.5050 -50.5050

Then the search would be something like this:

index="index_name"
| dedup "Computer Name"
| rename "Computer Name" as WKS
| rex field=WKS "<<regex to extract location>>"
| lookup location.csv Location as location OUTPUT Lat Lon
| geostats latfield=Lat longfield=Lon count

[ITWhisperer]

First you might want to split WKS into a mv-field then expand it. Then you can use a case statement on the value to determine the lat and lon

index="index_name"
| dedup "Computer Name"
| rename "Computer Name" as WKS
| eval WKS=split(WKS,",")
| mvexpand WKS
| eval lat=case(match(WKS, "Loc1"), "10.1010", match(WKS, "Loc2"), "20.2020",1==1,"0")
| eval lon=case(match(WKS, "Loc1"), "-10.1010", match(WKS, "Loc2"), "-20.2020",1==1,"0")
| geostats latfield=lat longfield=lon count

[SinghK]

I think using lookup table would be easier.

[dathrimar]

So I do have a lookup table I am working on which will be where I can pull the actual lat and lon from as well as the naming convention for each site. For this case the field names would be lat, lon, and Naming(Loc01, Loc2, etc).