Simplify working with Tableau via Telegram

Hello. Providing access to ready-made reports is often a separate issue. The issue of convenience and prompt access to the results of data processing for management largely determines the fate of their further use. Tableau system (or simply Tablo) is not in vain popular for such tasks, allowing you to quickly analyze data from many sources, publish online reports on the server, set up automatic distribution of PDF versions of reports and much more.

However, even when everything is set up, published and distributed, colleagues face problems:
')

Regular reports to the mail are lost in the flow of work letters and finding the right does not always work right away.
As a rule, online access to reports is protected by corporate VPNs. In some situations, this is a problem.
It is often required to receive a report without waiting for its regular mailing. For example, a plan-fact for a project for the current month may be required on any given day.
sometimes the password from your account on the server is trivially forgotten or the desired report is difficult to find among the other 100500 folders and reports.

One of the solutions to such problems is the Telegram bot, which can export PDF reports from the Scoreboard server at the touch of a button and send them to the user. In this article we will look at the code and examples of the work of the simplest version of such a bot.

We will proceed from the following simplifications:

The keyboard of the bot consists of the names of the reports that it will send to this user. The presence of nested menus in the current version is not implied.
The bot processes all messages independently of each other, i.e. it does not “remember” the user's previous messages.
The presence or absence of access to the report is determined by the chat_id parameter assigned to each user when starting work with the bot (pressing the START button).
The bot has one admin who will receive requests for access to reports.

For this article, we used the version of Tableau Server 10.1.3 installed on the Amazon Web Services virtual machine: Windows Server 2016 64-bit, RAM 16GB (yes, the Scoreboard server only works on Windows Server). Examples from the Tableau Samples project, which are installed by default with the Scoreboard server, were taken as exported reports. For example, such:

There are a lot of excellent examples of how to create a bot in Telegram, but you can just use the documentation . To work with the Telegram API, we use a ready-made Python library . We use Python 2.7 Anaconda .

B - Security

Using Telegram means that we are sending reports from the corporate server somewhere outside. Accordingly, we need to keep a registry of accesses so that everyone receives only reports submitted to him. Moreover, it would be necessary to add users / rights without restarting the script. Find out the user's chat_id at the beginning of work with the bot and immediately give him the necessary access.

When a user starts working with a bot, we need to find out his chat_id. To do this, in the handler of the / start command (the start function in the code), send it to the chat_id to the bot admin who can create a keyboard for the user (that is, chat_id) with reports.

When you click the START button, user Ivan Ivanov will receive the message:
Hi, at the moment you do not have access to reports. Your application has been accepted, access will be provided in the near future.

Admin bot will receive a message:

Application for access iivanov Ivan Ivanov 123456789

For such a procedure, we will get a YAML file, in which in the users section for each user we will compose a “keyboard” with the necessary reports. This format is then quite easy to read in the script and send to the Telegram as a keyboard:

User Access Configuration File

users: 123456789: keyboard: - #  ,      - buttonText: 'Global Temperatures' #  ,     reportName: 'Global Temperatures' #      report: '/views/Regional/GlobalTemperatures' #  ,      - buttonText: 'College Admissions' #  ,     reportName: 'College Admissions' #      report: '/views/Regional/College' - #  ,      - buttonText: 'Regional FlightDelays' #  ,     reportName: 'Regional FlightDelays' #      report: '/views/Regional/FlightDelays'

In this embodiment, the keyboard will contain two lines:

Converting such a keyboard entry into a Telegram format will be a special function:

Formation of the keyboard for Telegram

 def makeCustomKeyboard( userKeyboard ): """     .     keyboard    privacy.yaml. """ custom_keyboard = map( lambda z: [ telegram.KeyboardButton(x['buttonText']) for x in z ], userKeyboard ) return telegram.ReplyKeyboardMarkup(custom_keyboard, resize_keyboard='True')

The address of the configuration file in the script code is specified by the CONFIG_FILE variable. To update these parameters during bot operation, use the updateSettings () function.

The processing functions of the / start command and messages will update the access registry each time.

 def updateSettings(): """     """ return load( file( CONFIG_FILE ) )

In addition to the file with access for working with the Tablo server, we will need the following parameters:

Bot token and server settings

 config: #   botToken: '111222333:AAO4L0Ttt1tt01-1TtT1t_t22tT' #   tabcmd.exe   tabcmdLocation: 'C:\Program Files\Tableau\Tableau Server\10.1\bin\' #     reportsLocation: 'C:\Users\Administrator\Documents\bot\reports\' #    logsLocation: 'C:\Users\Administrator\Documents\bot\logs\' admin: #   chat id      id: 198765432

Everything is ready, you can define the processing function of the / start command. We also need the function logs, which simply writes a user’s action with the bot to a text file with the user's chat_id. Such a simple option in case of an error will prompt on which line it occurred.

Processing the start command

 def start(bot, update): """   /start  .        ( privacy.yaml,  users).  ,       .         """ # chat_id  chat_id = update.message.chat_id #      config = updateSettings() #        if chat_id in config['users']: logs(update, 'Received /start command from user in privacy.yaml') #           reply_markup = makeCustomKeyboard(config['users'][int(chat_id)]['keyboard']) bot.sendMessage(chat_id=chat_id, text=',    ', reply_markup=reply_markup) logs(update, 'List of available reports sent') #       else: logs(update, 'Received /start command from user NOT IN privacy.yaml') #    bot.sendMessage(chat_id=chat_id, text=',         . ' + \ '  ,      ') #         u = ast.literal_eval(str(update)) bot.sendMessage(chat_id=config['admin']['id'], text='   ' + str(u['message']['from']['username'].encode('utf-8')) + ' ' + str(u['message']['from']['first_name'].encode('utf-8')) + ' ' + str(u['message']['from']['last_name'].encode('utf-8')) + ' ' + str(chat_id)) logs(update, 'Request for access sent')

Ok, we sent the user a keyboard with reports available to him. Now, in our variant, we simply need to compare the incoming messages with the report names in the privacy.yaml access file for this chat_id. If the received message matches one of the reports, then we send the Tablo server a command to export this report. And then we send the generated report to the user.

The command to export reports from the server in PDF format can be sent via the command line using "get" or "export" ( link to the documentation).

To do this, first open a session with the Tablo server (instead of localhost, admin and adminPassword, specify the Tablo server domain, your login and password)

Before exporting a “heavy” report, it would be nice to warn the user:

 bot.sendMessage(chat_id=chat_id, text=' ,  ...', reply_markup=reply_markup) executeFile = os.path.join( tabcmdLocation, 'tabcmd.exe' ) subprocess.call([ executeFile, 'login', '-s', 'localhost', '-u', 'admin', '-p', 'adminPassword '])

Export the report to a folder on disk

 subprocess.call([ executeFile, 'get', reportAddress, '-f', reportsLocation + reportName + '.pdf'])

We close the session on the server

 subprocess.call([ executeFile, 'logout'])

It remains only to send the generated report to the user.

 bot.send_document(chat_id=chat_id, document=open(reportsLocation + reportName.encode('utf-8') + '.pdf', 'rb'))

Check how it works:

Exported PDF report from server:

That's all, I hope this approach will help you to simplify access to reports and improve work efficiency. Full version of the code:

Configuration file

 users: 123456789: keyboard: - #  ,      - buttonText: 'Global Temperatures' #  ,     reportName: 'Global Temperatures' #      report: '/views/Regional/GlobalTemperatures' #  ,      - buttonText: 'College Admissions' #  ,     reportName: 'College Admissions' #      report: '/views/Regional/College' - #  ,      - buttonText: 'Regional FlightDelays' #  ,     reportName: 'Regional FlightDelays' #      report: '/views/Regional/FlightDelays' config: #   botToken: '111222333:AAO4L0Ttt1tt01-1TtT1t_t22tT' #   tabcmd.exe   tabcmdLocation: 'C:\Program Files\Tableau\Tableau Server\10.1\bin\' #     reportsLocation: 'C:\Users\Administrator\Documents\bot\reports\' #    logsLocation: 'C:\Users\Administrator\Documents\bot\logs\' admin: #   chat id      id: 198765432

Script code

 # -*- coding: utf-8 -*- CONFIG_FILE = r'C:\Users\Administrator\Documents\bot\privacy.yaml' #     API  import telegram from telegram.ext import Updater from telegram.ext import MessageHandler, Filters from telegram.ext import CommandHandler import sys import subprocess from yaml import load import ast import os.path def updateSettings(): """     """ return load( file( CONFIG_FILE ) ) #     config = updateSettings() #   token = config['config']['botToken'] #   tabcmd.exe   tabcmdLocation = config['config']['tabcmdLocation'] #     reportsLocation = config['config']['reportsLocation'] #    logsLocation = config['config']['logsLocation'] def makeCustomKeyboard( userKeyboard ): """     .     keyboard    privacy.yaml. """ custom_keyboard = map( lambda z: [ telegram.KeyboardButton(x['buttonText']) for x in z ], userKeyboard ) return telegram.ReplyKeyboardMarkup(custom_keyboard, resize_keyboard='True') def logs(update, comment): """   .  comment    logs  ,   chat id . """ u = ast.literal_eval(str(update)) with open( os.path.join(logsLocation, str(update.message.chat_id) + '.txt'), 'a') as f: f.write( str(u['message']['from']['username'].encode('utf-8')) + '\t' + str(u['message']['from']['first_name'].encode('utf-8')) + '\t' + str(u['message']['from']['last_name'].encode('utf-8')) + '\t' + str(u['message']['text'].encode('utf-8')) + '\t' + str(comment) + '\n') def start(bot, update): """   /start  .        ( privacy.yaml,  users).  ,       .         """ # chat_id  chat_id = update.message.chat_id #      config = updateSettings() #        if chat_id in config['users']: logs(update, 'Received /start command from user in privacy.yaml') #           reply_markup = makeCustomKeyboard(config['users'][int(chat_id)]['keyboard']) bot.sendMessage(chat_id=chat_id, text=',    ', reply_markup=reply_markup) logs(update, 'List of available reports sent') #       else: logs(update, 'Received /start command from user NOT IN privacy.yaml') #    bot.sendMessage(chat_id=chat_id, text=',         . ' + \ '  ,      ') #         u = ast.literal_eval(str(update)) bot.sendMessage(chat_id=config['admin']['id'], text='   ' + str(u['message']['from']['username'].encode('utf-8')) + ' ' + str(u['message']['from']['first_name'].encode('utf-8')) + ' ' + str(u['message']['from']['last_name'].encode('utf-8')) + ' ' + str(chat_id)) logs(update, 'Request for access sent') def echo(bot, update): """    ,  .            ,     """ # chat_id   chat_id=update.message.chat_id #    response = update.message.text #      config = updateSettings() #     (    greenLight = True) greenLight = False #         for line in config['users'][chat_id]['keyboard']: #        for button in line: if button['buttonText'] == response: logs(update, 'Menu found, generating current keyboard and reports') #        reply_markup = makeCustomKeyboard( config['users'][chat_id]['keyboard'] ) logs(update, 'Current keyboard generated') #    greenLight = True #    reportName = button['reportName'] reportAddress = button['report'] + '?:refresh=yes' if greenLight: logs(update, 'Starting to generate report') #       (   ) bot.sendMessage(chat_id=chat_id, text=' ,  ...', reply_markup=reply_markup) logs(update, 'Message about report generation sent') #       executeFile = os.path.join( tabcmdLocation, 'tabcmd.exe' ) #     subprocess.call([ executeFile, 'login', '-s', 'localhost', '-u', 'admin', '-p', 'adminPassword']) #     PDF subprocess.call([ executeFile, 'get', reportAddress, '-f', reportsLocation + reportName + '.pdf']) #   subprocess.call([ executeFile, 'logout']) logs(update, 'Report generated, sending document') #    bot.send_document(chat_id=chat_id, document=open(reportsLocation + reportName.encode('utf-8') + '.pdf', 'rb')) logs(update, 'Report sent') elif response == token: updater.stop() #  ,     else: logs(update, 'Bad message, no access to this report') #          reply_markup = makeCustomKeyboard(config['users'][chat_id]['keyboard']) bot.sendMessage(chat_id=chat_id, text='       . ' + \ ',  admin@tableau.mycompany.ru   ', reply_markup=reply_markup) logs(update, 'Main menu keyboard sent after bad message') #     updater = Updater(token=token) dispatcher = updater.dispatcher updater.start_polling() #   /start start_handler = CommandHandler('start', start) dispatcher.add_handler(start_handler) #    echo_handler = MessageHandler([Filters.text], echo) dispatcher.add_handler(echo_handler)

Source: https://habr.com/ru/post/319690/

All Articles

Simplify working with Tableau via Telegram

More articles: