How to create geo map from manually extracted values ?

[georgear7]

I want to create geo map using 'geostats' command and am extracting and assigning country, latitude and longitude using eval command.

Source log file:

Application_Name	Transaction_Name	Status
NP_DEV_AU	Homepage	0
NP_DEV_VN	Homepage	0
NP_DEV_AU	Login	1
NP_DEV_HK	Homepage	1
NP_DEV_ID	Login	0
NP_DEV_ID	Homepage	0
NP_DEV_VN	Login	0
NP_DEV_HK	Login	1

Below is my query.

index=foo host=ab03u source=*sm.log sourcetype=bol_logs
| rex field=Application_Name "NP_DEV_(?<Market>\w+.*)"
| rex field=Application_Name "NP_DEV_(?<Market>\w+.*)"
| eval Country=case(Market=="HK","Hong Kong", Market=="TH","Thailand", Market=="VN","Vietnam", Market=="AU","Australia", Market=="ID","Indonesia", Market=="PH","Philippines", Market=="MY","Malaysia", Market=="SG","Singapore", Market=="CN","China")
| chart values(Status) by Country,Transaction_Name
| eval Result=case((Homepage!=Login), "Failure", (Homepage==0 AND Login==0), "Success", (Homepage==1 AND Login==1), "Failure")
| eval latitude=case(Country=="Hong Kong","22.396428", Country=="Thailand","15.870032", Country=="Vietnam","14.058324", Country=="Australia","-25.274398", Country=="Indonesia","-0.789275", Country=="Philippines","12.879721", Country=="Malaysia","4.210484", Country=="Singapore","1.352083", Country=="China","35.86166")
| eval longitude=case(Country=="Hong Kong","114.109497", Country=="Thailand","100.992541", Country=="Vietnam","108.277199", Country=="Australia","133.775136", Country=="Indonesia","113.921327", Country=="Philippines","121.774017", Country=="Malaysia","101.975766", Country=="Singapore","103.819836", Country=="China","104.195397")
| geostats latfield=latitude longfield=longitude values(Result) by Country

So, my final output will be,

Country	Result
Australia	Failure
Vietnam	Success
Hong Kong	Failure
Indonesia	Success

From this table i want to create one geo map using 'geostats' command, output should be in bubbles. and whichever country having 'Failure' as the Result, it should shows in red color bubble & success should be in green color bubble.

When i used above query, it's not showing any result for cluster map.

@skakehi_splunk @rnowitzki @richgalloway @woodcock @somesoni2 @niketn ..Need all your expertise on this.

[richgalloway]

The geostats needs positive numbers, not strings, to display.

[georgear7]

@richgalloway I converted my output to numbers. Now, '0' means success, '1' means failure. and the requirement is to color it according to the values.

0 - Green color

1 - Red color

I have used below, but it's not helping.

<option name="mapping.fieldColors">{"0":0x00FF00,"1":0xFF0000}</option>

Please suggest how to achieve this.

[richgalloway]

"It's not helping" what, exactly?
In my tests, zero values did not produce graphics, hence the "positive numbers" in my reply.

[georgear7]

@richgalloway Yes, you're correct. "0" is not giving graph.

So, I have assigned "1" is for 'Success' and "2" is for 'Failure'.

Now i want to color my bubble according to values. If it's 1, bubble should be in green color and if it's 2, it should be in red.

[rnowitzki · Accepted solution]

Hi @georgear7 ,

I think I have what you need.

This SPL makes up some sample data of yours and puts it on a map:

| makeresults 
| eval "Country" = "Australia Vietnam HongKong Indonesia"
| makemv Country
| mvexpand Country
| eval Result = case(Country="Australia",2,Country="Vietnam",1,Country="HongKong",2, Country="Indonesia",1)
| eval latitude=case(Country=="HongKong","22.396428", Country=="Vietnam","14.058324", Country=="Australia","-25.274398", Country=="Indonesia","-0.789275")
| eval longitude=case(Country=="HongKong","114.109497", Country=="Vietnam","108.277199", Country=="Australia","133.775136", Country=="Indonesia","113.921327")
| geostats latfield=latitude longfield=longitude count by Result

=> Save it as Dashboard Panel and edit the source.
=> Add this to the Options:

<option name="mapping.fieldColors">{"1":"0x65a637","2":"0xd93f3c"}</option>

Et voila:

I hope it helps

BR
Ralph

[georgear7]

Hi @rnowitzki ,

Thanks for your efforts in getting this, i can able to color the bubble now.

One more help i need from you regarding changing the size of the map. If you look at the countries which i mentioned in query, those are belongs to Asia and Australia continent. So i want to show those countries region only in my map without manually zooming it every time.

Due to this reason, countries like Hong Kong, Thailand, Vietnam are coming as a big bubble when we are not zooming in.

[rnowitzki]

Hi @georgear7 ,

This can be done with:

        <option name="mapping.map.center">(14,100)</option>
        <option name="mapping.map.scrollZoom">0</option>
        <option name="mapping.map.zoom">4</option>

 
I tried to set it around "your" countries", if not close enough, play around with the lat/long of the map.center and map.zoom option to set is as needed.


BR
Ralph

[georgear7]

Hi @rnowitzki ,

This is perfectly working fine, thanks a bunch for all your help. Much appreciated!

[richgalloway]

I have not been successful specifying colors for a pie chart since the Advanced XML days. The Visualization manual does not list a way to do it.