My objective is to map physical locations using IP addresses as source data in Tableau.
I have a log of several thousand entries of user sessions in a MySQL database.
Each row in the database has a dotted quad IP Address in a field named [clientIP] .
a data sample looks like this:
In Tableau [clientIP] field is recognized as dimensional data.
I have installed an R server and can connect to it successfully.
On the R server I have installed the RDSTK package, which allows use of the Data Science Toolkit found at Data Science Toolkit.
The Data Science ToolKit contains an IP address to Location function named ip2coordinates().
in R, a sample output of this function is
ip.address dma_code latitude country_code3 area_code longitude country_name
1 126.96.36.199 807 37.4192 USA 650 -122.0574 United States
postal_code region locality country_code
1 94043 CA Mountain View US
to create a single return value containing only the latitude and longitude, you can enter the following:
> paste(ip2coordinates("188.8.131.52")[c(3,6)], collapse= ',')
Note that if I enter the IP address incorrectly, such as by using commas instead of periods in the dotted quad IP address, I get the following error:
> paste(ip2coordinates("8,8,8,8")[c(3,6)], collapse= ',')
Error in `[.data.frame`(ip2coordinates("8,8,8,8"), c(3, 6)) :
undefined columns selected
In my Tableau workbook, I have created a calculated field named calculatedCustomerLocation and in it I have entered the following:
SCRIPT_STR( "require(RDSTK); paste(ip2coordinates(.arg1)[c(3,6)], collapse= ',')", [clientIP])
Tableau refuses to accept this, giving an aggregation error. If I attempt to type the [clientIP] value as a string using STR([clientIP]), I get a similar aggregation error. If I attempt to type the [clientIP] value using ATTR([clientIP]), I get the following error:
Error in `[.data.frame`(ip2coordinates(.arg1), c(3, 6)) :
undefined columns selected
I assume that I am failing to get a string value for [clientIP] into .arg1, or failing to pass that in an acceptable format to the R server.
my questions: answers to either will be appreciated:
a) how do I debug this? there appears to be no way to break this up into intermediate steps so I can see what Tableau is passing to the R server. There is no Tableau log I am aware of that will show me what the query looks like. Similiarly, the R server has no apparent method for logging the content of a remote call. Finally, the DSTK server is remote and no logs are available to look at. so I am at a loss to understand what value for the .arg1 field is being passed, in what format it is being passed, or how I am supposed to determine that. Any thoughts?
b) how do I fix this? I cannot aggregate a string value; I am attempting to pass a text string to the R server and have it pass a text string back. it seems to me the SCRIPT_STR type is pretty useless if you cannot do this sort of thing, so my assumption is that I have some basic syntax incorrect, but cannot through experimentation discover what that might be. Can you help me understand what I am doing wrong?
Thanks for reading...