lookup table renaming a field & map visualisation

[wifemin]

tl;dr
how does renaming a field to "search" help?

how to make a map visualization with the lookup table/codes shown?

Hello Splunkers,

From my previous question, I was doing something related to extracting countries from feeds.
However I wasn't able to do the lookup table version answer from my question and I found that using rex doesn't really help me after a while.

I chanced upon this question "https://answers.splunk.com/answers/61664/search-set-of-strings-from-lookup-and-list-count-of-occuran..." and tried the code, and I want to know how does renaming your string field name as search help ?

The particular part of the code goes :
[| inputlookup your_lookup_here | rename yourStringFieldName as search | fields search | format]

I actually just tried this particular code itself and I saw what I wanted to see/extract on the events tab and was wondering how I could make it into a cluster map/choropleth map.

The lookup (csv) I have contains fields such as "country", "lat" and "lng" and when I changed the word "search" I couldn't get what I wanted.

Also, I tried the code as a whole, I got an error with the rename function on the second line of the code which goes:
| rename _raw as rawText

Last question,
How do I make to make a cluster map using lookup table that contains latitude and longitude as well as country name ?
My search on the other hand only have country shown.

The below is a statistics of what I have currently gotten
Country and count comes from feed whereas lat and lng comes from my country.csv

country count lat lng
Australia 2 -33.42004148 151.3000048
Bangladesh 1 24.24997845 89.92003048


I also used this code to get the above | lookup simplemaps-worldcities-basic.csv country as country OUTPUTNEW country lng lat

Thanks !

[DalJeanis]

It looks like the answer @rich7177 already gave you is exactly what you need.

To collect data onto a choropleth map, do these steps -

1) select your data
2) get the name of the country
3) aggregate the data 
4) use geom to assign the country a shape for the map

That's it.

Since you are pulling the data from random text, @rich7177 already gave you a methodology for creating a rex to do that.

However, this code will do it automatically for you, then you only need to look at each name and see if you want to delete them or change them.

| inputlookup geo_attr_countries.csv 
| fields country | format "(?<country>" "" "3" "" "|" ")" 
| rex field=search mode=sed "s/country=|\"|  //g"

For example, I'd change |Bosnia and Herzegovnia| to |Bosnia|Herzegovnia| , |Myanmar [Burma]| to |Myanmar|Burma| , |Republic of Moldova| to |Moldova| and so on.

---

Okay, here's an answer to your first question:

When a subsearch (in square brackets []) returns values, they are implicitly formatted one of two ways.

1) if there is only a result "search", then that value is returned as is. So, the following code resolves to "foo"

[| makeresults | eval search="foo" | table search ]
[| makeresults | eval search="index=\"foo\"" | table search ]
[| makeresults | eval search="index=\"foo\" | stats count | and whatever else you want to say " | table search ]

... the results are...

foo
index="foo"
index="foo" | stats count | and whatever else you want to say

If the field is NOT called search, then the result is inherently formatted as if the last command of the subsearch had been | format, without any parameters.

[| makeresults | eval index="foo" | table index ]
[| makeresults | eval index="foo bar"  | makemv index | mvexpand index| table index ]
[| makeresults | eval index="foo bar"  | makemv index | mvexpand index| table index  | eval source="baz"]

... the results are...

( index="foo" )
( index="foo" OR index="bar" )
( ( index="foo" AND source="baz") OR ( index="bar"  AND source="baz")  )

[niketn]

@wifemin, Can you add sample events? Does it have Country field extracted or with some pattern? You would need to perform stats on your raw data based on Country names and then lookup the CSV file to get latitude and longitude.

Assuming the above search for Country.csv lookup with country field in the lookup table,

index=A sourcetype=B source=C* host=D*   [| inputlookup Country.csv | rename country as search | fields search | format]

Running the above search would be equivalent to the following

index=A sourcetype=B source=C* host=D*  "Aruba" OR OR "Afghanistan" OR "Angola" OR ..........

While the above will filter only the events containing Country names, this will not be sufficient to plot GeoStats. You would still need lookup command to join your events with the lookup table using Country names. If your _raw events already have Country names extracted as a field lets say countryName, you can use the following in the base search as well:

index=A sourcetype=B source=C* host=D*   [| inputlookup Country.csv | table country| rename country as countryName]

[wifemin]

Hello,
There is no country field extracted (unless you were talking about using rex from my previous question, but it doesn't work well with the current sourcetype I have)

When I used "source="/home/ubuntu/TensorFlow/Text_Classification/scripts/export.csv" sourcetype="csv-tf2" raw_text!="raw_text" AND raw_text!="*timestamp*" [| inputlookup simplemaps-worldcities-basic.csv |rename country as search | fields search | format] | dedup_raw"

This is the events that I gotten (with the countries being highlighted)
""South Korea Spy Agency Admits Attempting to Rig Election"",""South Korea's spy agency has admitted that it had engaged in a far-reaching attempt to manipulate voters as it sought to help conservatives win parliamentary and presidential elections.""

"Australia confirms IS plotted airport terror attack"",""Australia confirms IS plotted airport terror attack ... However, the plan was aborted before the luggage went through the airport's security control, AFP ... The suspects disassembled the device and then resorted to a second plan to...""

With the country names highlighted, I would like to know how I could continue to make it into both choropelth map and cluster map using the longitude and latitude from the lookup table as there is no iplocation/longtitude/latitude on my feeds/events.

[DalJeanis]

First, try this on a small timeframe until you get answers.

"source="/home/ubuntu/TensorFlow/Text_Classification/scripts/export.csv" sourcetype="csv-tf2" raw_text!="raw_text" AND raw_text!="*timestamp*" 
| rex "(?<mycountry>South Korea|Australia)"
| where isnotnull(mycountry)
| dedup _raw
| timechart count by mycountry

Now you've proven you are getting data with counts and the name of a country on it.

Then take the last line off and replace it with this

| stats count by mycountry
| rename mycountry as Country
| geom geo_countries featureIdField=Country

Now you should be able to switch to the choropleth map view.

After that, you just have to replace the rex with a bigger one that will get all the countries you want. And, I guess preferably, switch the name to Country instead of mycountry all the way through.