Dashboards & Visualizations

How to implement a cluster map based on telephone dialing codes?

klchandrakanth
Explorer

Hi Team,

We are trying to implement cluster map based on telephone dialing codes. need your help in implementing.
How to create maps and how to map the volume against the corresponding area and how to show the volume numbers in the corresponding area in the graph?

Your help is appreciated.

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Combine your two searches and you should have it.

source="log-ura" | eval PhoneDDD = substr(PhNumber,1,2) | lookup DDDLookupSample.csv DDD_Code as PhoneDDD | geostats latfield=Latitude longfield=Longitude count by DDD_Code
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Combine your two searches and you should have it.

source="log-ura" | eval PhoneDDD = substr(PhNumber,1,2) | lookup DDDLookupSample.csv DDD_Code as PhoneDDD | geostats latfield=Latitude longfield=Longitude count by DDD_Code
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

klchandrakanth
Explorer

Perfect. Thanks. below command worked. Thanks for your quick help. you are awesome.

source="log-ura" | eval PhoneDDD = substr(PhNumber,1,2) | lookup DDDLookupSample.csv DDD_Code as PhoneDDD | geostats latfield=Latitude longfield=Longitude count by PhoneDDD

0 Karma

klchandrakanth
Explorer

@richgalloway, why i am getting few results in OTHER ?

geobin latitude longitude 11 12 16 19 21 22 24 27 31 32 34 35 37 OTHER
bin_id_zl_0_y_2_x_2 -27.93652 -52.55455 907 79 315 116 124
bin_id_zl_0_y_2_x_3 -22.56003 -44.16995 17830 1716 1384

bin_id_zl_0_y_3_x_2 -3.21593 -47.30641 2327

0 Karma

richgalloway
SplunkTrust
SplunkTrust

I assume you have data with DDD codes not in your lookup file.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

klchandrakanth
Explorer

I am guessing issue with multiple DDD vs states.
Can I use multiple DDD codes like below.

54,Rio Grande Do Sul,-30.88004148,-55.53000615
55,Rio Grande Do Sul,-30.88004148,-55.53000615
56,Rio Grande Do Sul,-30.88004148,-55.53000615
57,Rio Grande Do Sul,-30.88004148,-55.53000615
58,Rio Grande Do Sul,-30.88004148,-55.53000615
59,Rio Grande Do Sul,-30.88004148,-55.53000615
69,Rondonia,-11.64002724,-61.20999536
95,Roraima,1.816231505,-61.12767481
47,Santa Catarina,-27.23003172,-52.03001306
48,Santa Catarina,-27.23003172,-52.03001306
49,Santa Catarina,-27.23003172,-52.03001306
11,Sao Paulo,-23.65283405,-46.52781661
12,Sao Paulo,-23.65283405,-46.52781661
13,Sao Paulo,-23.65283405,-46.52781661
14,Sao Paulo,-23.65283405,-46.52781661
15,Sao Paulo,-23.65283405,-46.52781661

0 Karma

richgalloway
SplunkTrust
SplunkTrust

That looks good.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

klchandrakanth
Explorer

Not sure what is the issue, even if i do count by ddd instead of states, still i see OTHER. i think its not issue with look up. Any light on the issue is. Much appreciated. Thanks for your help so far @richgalloway

0 Karma

klchandrakanth
Explorer

Found it.

globallimit=0 fixed the issue with "OTHER".

globallimit, Controls the number of pies in the pie-chart. All other split-by values will be grouped under "OTHER".Setting globallimit=0 will remove all limits and all columns will be rendered

0 Karma

klchandrakanth
Explorer

@richgalloway, any help ? i am stuck and not getting any information what are the causes for OTHER results.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

For US and for Canada, it looks like there are ESRI files (or similar) available for that from at least a couple of places.

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=United_States_Telephone_Area_Code_Boun...

You may have another issue, though. Area codes are no longer geographically distinct; most of them in the most populous locations are overlays, so you may have three different area codes that represent identical geography. You need to identify what the purpose of your map is before you decide on representation.

I suspect that you probably should go with plotting all overlays to the exact same coordinates, and aggregating the results as a single pie chart. Presumably, if you are presenting a geographical representation, then you are trying to see where your calls are coming from.

Check your underlying data and see if your assumptions are correct or if there is a more specific field, though. I make calls from my Dallas 469 number while I am on site in Florida or Kansas or San Jose.

0 Karma

klchandrakanth
Explorer

Thanks for the details. I am trying this in Brazil.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The same caution applies to Brazil as most states there have more than one area code.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

klchandrakanth
Explorer

I am not sure where to start. All i have is telephone area codes. Do i need to have a lookup created with telephone area codes with latitude and longitude? To proceed further?

0 Karma

klchandrakanth
Explorer

I have created a sample lookup table with telephone area code, state,lat and long.
with below command i am able to see the coordinates.

| inputlookup DDDLookupSample.csv | geostats count by DDD_Code latfield=Latitude longfield=Longitude

However, not able to map my query result to the map. Any help is appreciated.
my base query that is giving the count of the area codes with below.

source="log-ura" | eval PhoneDDD = substr(PhNumber,1,2) | stats count by PhoneDDD

PhoneDDD count
11 6860
12 216
13 98
15 446

My CSV is like this.

DDD_Code States Latitude Longitude
11 Sao Paulo -23.65283405 -46.52781661
68 Acre -9.070003236 -68.66997929
82 Alagoas -9.48000405 -35.83996769
96 Amapa -0.039598369 -51.17998743
92 Amazonas -3.289580873 -60.6199797

0 Karma

klchandrakanth
Explorer

@niketnilay or @richgalloway, any help ?

0 Karma

niketnilay
Legend

@klchandrakanth, is this dialing code lookup required only for US or anywhere in World?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

klchandrakanth
Explorer

This is for area lookup in Brazil

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Yes, you will need a means to convert area codes into coordinates. A lookup table should work well. Depending on your data, you would then use the geom or geostats command to plot it on a map.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

klchandrakanth
Explorer

Thank you for your quick response. I will try and get back to you.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

What have you tried so far?

---
If this reply helps you, an upvote would be appreciated.
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!