Implementing R functionality on Tableau Server

R (https://www.r-project.org/) is a free software environment for statistical computing and graphics. It compiles and runs on a wide variety of UNIX platforms, Windows and MacOS. In this example, I will be installing and configuring R on Ubuntu 14.04 and enabling R functionality with Tableau Server by installing the Rserve library.

‚ÄčIn order to install R¬†‚Äč‚Äčand R packages from the ‘Compreshensive R Archive Network’ (CRAN) we will use the Advanced Packaging Tool (APT) and therefre¬†need¬†‚Äč‚Äčadd the repository to¬†‚Äč‚Äčthe list of sources¬†‚Äčas well as the¬†‚Äčpublic key¬†‚Äčto authenticate packages¬†downloaded using APT, this will ensure we install the latest version of both R (r-base)and the CRAN package for Rserve.

sudo sh -c 'echo "deb http://cran.rstudio.com/bin/linux/ubuntu trusty/" >> /etc/apt/sources.list'‚Äč‚Äč
gpg --keyserver keyserver.ubuntu.com --recv-key E084DAB9
gpg -a --export E084DAB9 | sudo apt-key add -
sudo apt-get update 
sudo apt-get install ‚Äč r-base‚Äč‚Äč

To verify the installation we can enter an interactive shell session, once loaded we shall quit the session

R 
q(save="no")

Now we will install the Rserve CRAN package by invoking the install.package() function in R. In order for the package to be available to all users this is installed as root (su).

sudo su - -c "R -e \"install.packages('Rserve', repos = 'http://cran.rstudio.com/')\""‚Äč

Again, we can verify the installation by entering the R interactive shell session, confirming the Rserve library is available and then quit the session.

R
‚Äčlibrary(Rserve)
q(save="no")

By default Rserve only accepts local connections, in order to enable remote connections will we will need to modify the configuration file ‘/etc/Rserve.conf’. A detailed list of other Rserve connection properties that may be set see https://rforge.net/Rserve/doc.html#start.

remote enabled

In order to ensure the Rserve process is initialised at startyp as a daemon we will need to create the shell script ‘/etc/init.d/Rserve.sh’ as below. As no ownership of files are required for the invocation of the Rserve CRAN package, we will use the ‘nobody’ account to start the daemon.

#!/bin/bash 
sudo -u nobody R CMD Rserve --vanilla 

In order to execute the shell script we will require to set execute permissions to the shell script and add a link to initialise the shell script at startup. ‚Äč

sudo chmod 755 /etc/init.d/Rserve.sh
sudo update-rd.d /etc/init.d/Rserve.sh defaults

To confirm the Rserve process is initialised at startup using the shell script we can reboot the instance and confirm the process is running

ps aux | grep Rserve

The next step is optional, in this example I only want to permit inbound connections on the TCP service port 6311 (Rserve) from the Tableau Server. By default rules added to iptables are ephemeral and on restart will be removed. In order to save the configuration we will install the ‘iptables-persistent’ package.

sudo apt-get install iptables-persistent 

I will firstly insert a drop rule for all connections (IPv4) to the destination port 6311(tcp), then insert an accept rule for the Tableau Server (10.0.0.2) to the destination port 6311(tcp) and then save the updates to preserve the iptables configuration.

sudo iptables -I INPUT -p tcp -s 0.0.0.0/0 --dport 6311 -j DROP
sudo iptables -I INPUT -p tcp -s 10.0.0.2 --dport 6311 -j ACCEPT
sudo service iptables-persistent save‚Äč

The last step is to configure the Tableau Server VizSQL Server connection properties for a Rserve host (10.0.0.3) and port (6311) to enable R functionality within workbooks, optional configuration parameters are also available to use a username and password but in the example access is restricted by firewall rules.

tabadmin stop
‚Äčtabadmin set vizqlserver.rserve.host 10.0.0.3 
tabadmin set vizqlserver.rserve.port 6311
tabadmin configure
tabadmin start 

The configuration is now complete, to verify the VizSQL Server configuration, invoke ‘tabadmin configure -o ‘ to confirm the configuration parameter has been set by dumping the current configuration to a file.

Posting IMDB ratings to Twitter, my first multi-step Zap

I am sure the majority of us reading this article have one way or another automated tasks as part of our day jobs, with services such as Zapier and IFTTT we can easily take this approach for tasks in our personal lives to build integrations between applications we use every day.

In this example, I will be using a ‘Zap’¬†to automate the task of publishing a tweet containing¬†ratings from my IMBD account for a film or TV series I have recently viewed. On a separate note, I am still quite not sure why this is not integrated as a sharing option for account settings!

What is a Zap? Well lets take the official explanation:

A Zap is a blueprint for a task you want to do over and over. In words, a Zap looks like this:

“When I get a new thing in A, do this other thing in B.”

So in my example, when I post a review to IMDB (A) a message to Twitter should be posted containing my rating(B).

Firstly, I created the trigger which would use the RSS app by Zapier to discover new item feed items from the publicly available URL of the RSS feed of my ratings from IMDB. To retrieve the URL, login to your IMDB account, select ‘Your Activity > Your Ratings’ and select the RSS icon in the right corner.

Also, in order to post you will require the list to be public, to which you will need to select ‘Change list settings’ and select ‘Make this list a public list visible to all public IMDB users’.

Ratings_Screenshot

In my example the URL ‘http://rss.imdb.com/user/ur43561079/ratings’ will be used for the feed. In order to determine what triggers a new feed item, we will select ‘Different Guid/URL’ which is the default option.

Now that we have our trigger for the multi-step task, our final goal is to post a tweet containing content from items retrieving in the feed. In this step I have created an action using the Twitter app to create a Tweet (limited to 10 per hour) and account. Now we are provided with a template which will contain the content of the message you require to post from Twitter. Here you can select items returned from the feed trigger to generate the message by selecting the item returned by selecting a field.

So, lets have a look at the information returned from a new feed item:

      <item>
            <pubDate>Sat, 13 Feb 2016 00:00:00 GMT</pubDate>
            <title>The Man in the High Castle (2015 TV Series)</title>
            <link>http://www.imdb.com/title/tt1740299/</link>
            <guid>http://www.imdb.com/title/tt1740299/</guid>
            <description>
                mail-deangrant-689-891137 rated this 7.
            </description>
            <pubDate>Sat Feb 13 00:00:00 2016</pubDate>
        </item>

In my example, I want to post a message similar to ‘Rated {title} a {rating}. #imdb {link}’. From reading the above we can see that we cannot retrieve a field item only containing the rating score, but only a description item containing the text ‘{username} rated this {rating}’. So lets go back a step, from here I want to select the text pattern containing the rating score. We can achieve this by creating a step prior to posting the message to use the Code app to run python in response to information received from the trigger. In this example we want to provide the description field as the input item (input[‘description’]) and split the string into substrings where the text pattern ‘rated this ‘ is used to split the string and return the item in the array containing the rating score.

string = input['description']
rating = string.split('rated this ',1)[1]

return {
   'rating': rating
}

Now back to posting the message to Twitter we can select the following field items to generate the message as per my requirements. Field items prefixed with Step 1 are items returned from the RSS app and Step 2 being data returned from the Code app.

Zapier_Screenshot_IMDBtoTwitter_Step3

When the zap is run, and a new feed item is discovered the message should be posted to twitter based on the template created above and should read similar to the item below:

Twitter_Screenshot_Zap_Post

Finally, if we look at the task history we can see how the zap was triggered and data in and data out received/returned during invocation of each step.

Step 1 – Found 1 new in Item in Feed in RSS.

Data In

url:
http://rss.imdb.com/user/ur43561079/ratings
trigger_style:
smart

Data Out 

description:
mail-deangrant-689-891137 rated this 7.
pubDate:
Sat, 13 Feb 2016 00:00:00 GMT,Sat Feb 13 00:00:00 2016
title:
The Man in the High Castle (2015 TV Series)
raw__guid:
http://www.imdb.com/title/tt1740299/
raw__link:
http://www.imdb.com/title/tt1740299/
raw__pubDate:
Sat, 13 Feb 2016 00:00:00 GMT,Sat Feb 13 00:00:00 2016
raw__description:
mail-deangrant-689-891137 rated this 7.
raw__title:
The Man in the High Castle (2015 TV Series)
link:
http://www.imdb.com/title/tt1740299/
guid:
http://www.imdb.com/title/tt1740299/
id:
http://www.imdb.com/title/tt1740299/
Fields with no value:
content

Step 2 – Sent 1 new Run Python to Code.

Data In 

input:
description:
mail-deangrant-689-891137 rated this 7.
code:
string = input['description']
rating = string.split('rated this ',1)[1]

return {
   'rating': rating
}

Data Out 

runtime_meta:
duration_ms:
1
memory_used_mb:
20
logs:
rating:
7.
id:
HmFhdaYZDDFsYw0iVXiGETIf1STwsiKj

Step 3 – Sent 1 new Tweet to Twitter

Data In 

message:
Rated The Man in the High Castle (2015 TV Series) a 7. #imdb http://www.imdb.com/title/tt1740299/

Data Out 
N/A

Identifying applications vulnerable to the Sparkle MiTM attacks

As recently disclosed (https://vulnsec.com/2016/osx-apps-vulnerabilities/) you may be already be aware of a vulnerability in Sparkle that exposes a large number of applications to man-in-the-middle (MiTM) attacks over insecure HTTP channels.

In order to identify Applications that are susceptible to MiTM attacks that install malicious code in the Sparkle software framework invoke the below from a terminal window. From the output we are looking for applications to which the version string is prior to 1.13.1 to which these will be vulnerable if set to load over HTTP.

find /Applications -path '*Autoupdate.app/Contents/Info.plist' -exec echo {} \; -exec grep -A1 CFBundleShortVersionString '{}' \; | grep -v CFBundleShortVersionString

The applications ‘Info.plist’ file will have a ‘SUFeedURL’ key which can identify any assets that are being loaded over unsecured HTTP. Alternatively, you can attempt to update the application and perform a packet capture using a utility such as Wireshark to determine if the HTTP protocol is being used.

A list of applications that are dependent on Sparkle can be found here, but not all of these may be communicating over insecure HTTP.

vCenter Server 5.5 Update 3a: Update fails with Warning 32014.

Recently during an upgrade of a an instance of a vCenter Server System to 5.5 Update 3a the installation returned an error with the following message:

Warning 32014. A utility for phone home data collector couldn’t be executed successfully Please see its log file (with name PhoneHome) and vminst.log in system temporary folder for more details.

Upon restarting the ‘VMware VirtualCenter Server’ service the following error message is returned:

Error 1053: The service did not respond to the start or control request in a timely fashion.”

This is a known issue affecting vCenter 5.5 where the installer failed to update the ‘deployPkg.dll’ file during the upgrade process, to which currently there is no resolution.

To workaround the issue we have two options. Firstly, we can rollback the instance of the vCenter Server System prior to the upgrade and remove the file from patch cache which by default is located at ‘C:\Windows\Installer\$PatchCache$\Managed\05550F1E83248734780F0115742A159D\5.5.0’ and perform the upgrade.

Alternatively, you can perform the following to provide resolution to the issue.

1) Browse to ‘C:\Program Files\VMware\Infrastructure\VirtualCenter Server\’ and remove the file ‘deployPkg.dll’.

2) Download the file ‘2134141_deployPkg.zip‘ from the official VMware site and extract to the location ‘C:\Program Files\VMware\Infrastructure\VirtualCenter Server\’.

3) Start both the ‘VMware VirtualCenter Server Service’ and ‘VMware VirtualCenter Management Webservices’ services.

4) Uninstall the Profile-Driven Storage service by running the following command with elevated privelages.

msiexec.exe /x {7BC9E9D9-3DF6-4040-B4A1-B6A3A8AE75BA} SKIPVCCHECK=1 SUPPRESS_CONFIRM_UNINSTALL="1" /qr

5) Install the vCenter Server 5.5 Update 3a version of the Profile-Driven Storage¬†by running the following command with elevated privelages. In the below example, I am using ‘vcenter.dean.local’ as the FQDN of my vCenter Server System and the installation media is mounted on D:\.

msiexec.exe /L*V "%temp%\sps_vminst.log" /I "D:\vCenter-Server\Profile-Driven Storage\VMware vSphere Profile-Driven Storage.msi" INSTALLDIR="C:\Program Files\VMware\Infrastructure\" COMPUTER_FQDN=vcenter.dean.local TOMCAT_MAX_MEMORY_OPTION="S" VC_KEYSTORE_TYPE=PKCS12 VC_KEYSTORE_PASSWORD=testpassword VC_SSL_DIR="C:\ProgramData\VMware\VMware VirtualCenter\SSL\" VC_SPS_EXTENSION_DIR="C:\Program Files\VMware\Infrastructure\VirtualCenter Server\extensions\com.vmware.vim.sps\" IS_URL="https://vcenter.domain.local:10443" ARPSYSTEMCOMPONENT=1 SKIPVCCHECK=1 /qr

Now confirm that all your services are running and you are able to access the vCenter Server System.

As this is a known issue, you can prevent the symptom of this prior to performing an upgrade by removing the file from the patch cache prior to running your vCenter Server System upgrade.

{php}IPAM: The API server module and automating IP address reservation

I have recently provided articles in regards to the installation and configuration of {php}IPAM IP. One of my use cases for investigating the use of IP address management systems was to provide a API/server module to request an IP address for allocation.

By default, a module is provided to request an IP address¬†by browsing to Administration > IPAM settings > Feature Settings ¬†and enabling the ‘IP request module’.

IPAM_IP_Request_Module_Enabled

However, this requires user interaction in order to approve the pending request. Now this is where I discovered a number of php scripts by Doug Morris that leverage the API framework. In this scenario, getFreeIP.php provides the functionality to retrieve the first available IP address for a subnet. In addition the following scripts are available from the repository:

getIPs.php – dump information for a subnet.
removeHost.php – removes IP address information from a subnet for a host.
tokenValid.php – validates API token for requests. ‚Äč

In order to ¬†retrieve the first available IP address for a subnet we will also require to validate the API token for the request so we will also need the tokenValid.php file as well. The files should be placed in the ‘/var/www/phpipam/api’ directory.

cd /tmp 
git clone https://github.com/covermymeds/phpIPAM-api.git
cd /tmp/api
cp getFreeIP.php tokenValid.php /var/www/phpipam/api

In order to submit a request to the API we need to browse to Administration > IPAM Settings > Feature Settings and enable the API server module. Then browse to Administration > IPAM Settings > API Management to create an API key to which you will specify an application identifier and for this use case set ‘Read’ application permissions.

API_Enable_IPAM

API_App_IPAM

In order for the API server module to provide data to client requests the php curl and mcrypt extensions are required to be installed, the mcrypt extension enabled and a restart of the Apache Web Server to apply.

sudo apt-get install php5-curl php5-mcrypt
sudo php5enmod mcrypt
sudo service apache2 restart

Now we have enabled API server module, created the API key and placed the script files on the host. We can now send an HTTPS request which requires the following information:

Application Identifier
Application Code
Subnet
Hostname
Owner

The request will return the first free IP address in for the subnet and reserve the IP address. If an IP address has already been reserved for the hostname, this will be returned. In this example I am submitting a request to reserve an IP address for the hostname ‘server1.dean.local’ in the subnet ‘10.0.0.0’ and specifying the owner as ‘deangrant’

curl https://ipam.dean.local/api/getFreeIP.php?apiapp=dean&apitoken=30c13f9d33668a5e13e79a5865dc409f&subnet=10.0.0.0&server1.dean.local&user=deangrant
10.0.0.3

Posting messages to Slack channel using Windows PowerShell

Download: Send-ToSlack

As previously commented I had been exploring the use of Slack and integration with other tools. One use case was to send messages to channels within Slack when certain steps in an automated workflow were triggered and to provide notification of the status upon completion.

Therefore, I created a Windows PowerShell function that would post a message to a specified slack channel using the Web API methods available, which according to Slack:

The Slack Web API allows you to build applications that interact with Slack in more complex ways than the integrations we provide out of the box.

Currently, my requirement is only to post messages and therefore the function only provides support for the chat.PostMessage method to posts a message to a public channel, private group, or IM channel.

In order to provide authentication to the API this can be achieved by a bearer token to identify a single user which uses¬†¬†a generated full-access token, see ‘https://api.slack.com/web‘ for more details and to generate/retrieve your bearer token information.

If you plan to authenticate several users, it is recommended to use OAuth2 by registering your application. Currently, this method for authenticatin is not supported within the function.

The function uses the Invoke-WebRequest cmdlet to send a request to the URL ‘https://slack.com/api/chat.postMessage‘ and provide the arguments for generating the message that will be posted to the specified Slack channel. The protocol ‘https’ is specified as all methods must be called using this protocol.

The ‘chat.PostMessage’ method has mandatory requirements to specify the token, channel and text arguments. In addition, the function currently provides the ability to specify the ‘icon’¬†and ‘username’ arguments when posting a message.

The response will contain a JSON object, which contains a top-level boolean property which indicates success of failure. In the event of an error posting the message to the slack channel conditional logic is used to determine if the response contains an error ‘”ok”:false’ and processing the string to terminate with an error and return the machine-readable error code.

On successfully posting a message to a channel, you will receive a customised output to the console session to confirm. The success code is determined by the response containing the string ‘”ok”:true’.

Send-ToSlack -Channel "#apitest" -Text "This is a test message generated by the function Send-ToSlack" -Token "xoxp-17822671332-9811436111-15776151506-a5a9c3855"
Successfully sent the message to the slack channel #apitest.

Slack_postMessage

 

 

 

In addition, to the above the function also provides the ability to encrypt your bearer token using an encryption key and retrieve the string using the ‘System.Management.Automation.PSCredential’ class.

 

 

Generating certificate requests with additional subject identities using OpenSSL

The below provides steps to how the process used to create a certificate request to issue to certificate authority server in an internal environment. However, the steps to create the certificate request can be performed if submitting a certificate request to a third party certificate authority.

Firstly, I will create a configuration file (openssl.cnf) to be used generating the certificate request. The certificate request will be created specifying a default key size of 2048 bits, and sha256 digest algorithm. In this example, I will be submitting a certificate request for the server ‘server1.domain.local’ with the additional subject identities ‘server1,’192.168.0.1’, ‘server1.domain.local’ and ‘www.dean.local’.

[ req ]
default_bits = 2048
‚Äčdefault_md = sha256
default_keyfile = rui.key
distinguished_name = req_distinguished_name
encrypt_key = no
prompt = no
string_mask = nombstr
req_extensions = v3_req

[ v3_req ]
basicConstraints = CA:FALSE
keyUsage = digitalSignature, keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth, clientAuth
subjectAltName = DNS:server1, IP:192.168.0.1, DNS:server1.dean.local, DNS: www.dean.local

[ req_distinguished_name ]
countryName = GB
stateOrProvinceName = Midlothian
localityName = Edinburgh
0.organizationName = Dean Grant
organizationalUnitName = Servers
commonName = server1.dean.local ‚Äč

We will now create the certificate request to send to the certificate authority, to which the original public key generated in the certificate request will be converted to be in RSA format and remove the original file. Once the certificate request has been generated place the in a location which may be accessible for the submission to the certificate authority server.

cd /tmp
openssl req -new -nodes -out server1.dean.local.csr -keyout orig-server1.dean.local.key -config openssl.cnf
openssl rsa -in orig-server1.dean.local.key -out server1.dean.local.key
rm -f orig-server1.dean.local

In this example I am submitting my certificate request to a certificate authority running Active Directory Certificate Services on Windows Server 2012. The certificate request is submitted specifying the ‘WebServer’ certificate template and the certificate request file created previously. If prompted select the certificate authority which will now create certificate file (server1.dean.local.crt) and the certficate chain file (server1.dean.local.pfx).

cd %temp%
certreq -attrib &quot;CertificateTemplate:WebServer&quot; -submit server1.dean.local.csr server1.dean.local.crt server1.dean.local.pfx

The certificate files may now be placed on the server to which you configure encryption, depending on the certificate file requirements you should have the following files available.

server1.dean.local.crt # certificate file
server1.dean.local.pfx # certificate chain file in personal exchange file (.pfx) format.
server1.dean.local.key # private key file