This post goes through the steps of exposing Windows commans and PowerShell scripts as remote Zato API services that can be invoked by REST clients.

This lets one access a fleet of Windows systems from a single place and makes it possible for Zato services to participate in Windows management processes.

Note that Zato servers always run on Linux and no installation of any kind of software under Windows is necessary for Zato to connect to remote systems.

Prerequisites

Start by installing a library that implements the remote Windows connectivity:

$ cd /opt/zato/current
$ ./bin/pip install pywinrm

Next, stop and start again any servers running, e.g.:

$ zato stop /path/to/server
$ zato start /path/to/server

Python code

Deploy the following service to your Zato cluster - note its name, windows.remote.management.

# -*- coding: utf-8 -*-

from __future__ import absolute_import, division, print_function, unicode_literals

# stdlib
from traceback import format_exc

# pywinrm
import winrm

# Zato
from zato.server.service import Service

class MyService(Service):
    name = 'windows.remote.management'

    class SimpleIO:
        input_required = 'type', 'data'
        output_required = 'exec_code'
        output_optional = 'status_code', 'stdout', 'stderr', 'details'
        response_elem = None
        skip_empty_keys = True

    def handle(self):

        # Local aliases
        input_type = self.request.input.type
        input_data = self.request.input.data

        # Validate input - we support either regular commands
        # or PowerShell scripts on input.
        if input_type not in ('cmd', 'ps'):
            self.response.payload.exec_code = 'error'
            self.response.payload.details = 'Invalid type'

        # Input was valid, we can try to execute the command now
        else:

            try:

                # Remote server details
                host = '10.151.139.17'

                # Credentials
                username = 'myuser'
                password = 'NZaIhMezvK00Y'

                # Establish a connection to the remote host
                session = winrm.Session(host, (username, password))

                # Dynamically select a function to run,
                # either for commands or PowerShell
                func = session.run_cmd if input_type == 'cmd' else session.run_ps

                # Run the function with input data given
                result = func(input_data)

                # Status code is always available
                self.response.payload.status_code = result.status_code
                self.response.payload.stdout = result.std_out
                self.response.payload.stderr = result.std_err

            except Exception:
                self.response.payload.exec_code = 'error'
                self.response.payload.details = format_exc()

            # Everything went fine
            else:
                self.response.payload.exec_code = 'ok'

REST channel

Create a new REST channel in web-admin and mount service windows.remote.management on it. Make sure to set data format to JSON.

Usage

Let us invoke the service from command line, using curl. For clarity, the output of commands below is limited to a few lines.

First, we will run a regular command to get a directory listing of drive C:

$ curl http://zz:zz@localhost:11223/windows -d '{"type":"cmd", "data":"dir c:"}'
{"status_code": 0,
 "exec_code": "ok",
 "stdout": " Volume in drive C has no label.\r\n
 Volume Serial Number is 1F76-3AB6\r\n\r\n
 Directory of C:\\Users\\Administrator\r\n\r\n07/22/2019  11:53 PM    <DIR>"}

What if we provide an invalid drive name?

$ curl http://zz:zz@localhost:11223/windows -d '{"type":"cmd", "data":"dir z:"}'
{"status_code": 1,
 "exec_code": "ok",
 "stderr": "The system cannot find the path specified.\r\n"}

Now, invoke a PowerShell script, which in this case is a single-line one to check connection from the remote Windows system to example.com, but it could be much more complex, there are no limitations:

$ curl http://zz:zz@localhost:11223/windows -d \
  '{"type":"ps", "data":"Test-Connection example.com"}'

This time, both stdout and stderr are returned but because the overall status_code is 0, we know that the invocation was successful.

{"status_code": 0,
 "exec_code": "ok",
  "stdout": "WIN-A3I92B... example.com     93.184.216.34",
  "stderr": "#< CLIXML\r\n<Objs Version=\"1.1.0.1\" ...",

In conclusion

The service is just a starting point and there are a couple ways to extend it:

  • Details of remote servers, including credentials, should be kept separately
  • Permissions, including ACLs, can be added to allow or disallow access to particular commands to selected REST users only

Yet, even in this simple form, it already shows how easy it is to connect to Windows servers and turn remote commands into REST APIs microservices.

On top of it, REST is but one of many formats that Zato supports - one could just as well design workflows around AMQP, ZeroMQ, IBM MQ, FTP or other protocols in addition to REST with no changes to Python code required.

This article will show you how to invoke MS SQL stored procedures from Zato services - a feature new in the just released version 3.1 of the Python-based integration platform.

In web-admin

Start off by installing the latest updates.

Next, the first thing needed is creation of a new outgoing SQL connection - make sure to choose the MS SQL (Direct) type, as below.

It is considered a direct one because, even though it is based on SQLAlchemy, it does not make use of the most of SQLAlchemy's functionality and lets one invoke stored procedures alone, i.e. it is not possible to use this type of connections with ORM or anything else - only stored procedures are supported.

Make sure to change the password after creating a connection - the default one is a randomly generated string.

Python code

In most cases, to invoke a stored procedure, use the code below:

# -*- coding: utf-8 -*-


# Zato
from zato.server.service import Service

class MyService(Service):
    def handle(self):

        # Connection to use
        name = 'My MS SQL Connection'

        conn = self.outgoing.sql.get(name)
        session = conn.session()

        # Procedure to invoke
        proc_name = 'get_current_user'

        # Arguments it has on input
        args = ['my.user.id']

        data = session.callproc(proc_name, args)

        # Data is a list of dictionaries, each of which
        # represents a single row of data returned by the procedure.
        for row in data:
            ...

Lazy evaluation

The usage example above will work in many cases but, supposing a procedure returns many thousands of rows, it may not be efficient to read them in all in a single call.

This would potentially create a big list of row elements - if all them are indeed required in a single place then this is not a concern. But if they should be processed one by one then it may be better to explicitly fetch and process a single row at a time.

To achieve it, use_yield=True can be applied, as in the code below. Now, each iteration of the for loop will return a new row, without ever accumulating all of them in RAM.

# -*- coding: utf-8 -*-


# Zato
from zato.server.service import Service

class MyService(Service):
    def handle(self):

        # Connection to use
        name = 'My MS SQL Connection'

        conn = self.outgoing.sql.get(name)
        session = conn.session()

        # Procedure to invoke
        proc_name = 'get_current_user'

        # Arguments it has on input
        args = ['my.user.id']

        data = session.callproc(proc_name, args, use_yield=True)

        # Data is a Python generator now and each iteration
        # of the loop returns a new row from the stored procedure.
        for row in data:
            ...

Wrapping up

Ability to use MS SQL is a feature new in Zato 3.1 - it works in a way similar to other SQL connection types with the notable exception that only stored procedures can be invoked from Python code.

There are two ways to invoke stored procedures - either by reading the whole output into a service or processing rows one by one. The latter is recommended if a large number of rows is to be processed by the service.

The newest version of Zato, the open-source Python-based enterprise API integrations platform and backend application server, is out with a lot of interesting features, changes and additions

The full changelog is here and below is a summary of what is new in 3.1:

  • Greatly enhanced support for Docker, including Quickstart, Swarm and Kubernetes
  • Python 3 is now fully supported in addition to Python 2.7
  • New connectors and adapters: MongoDB, LDAP (Active Directory), Apache Kafka, SFTP, Slack, Telegram and JSON-RPC
  • Extensions to Single Sign-On: two-factor authentication and multi-credentials accounts
  • Rate-limiting and IP white-listing, including hierarchical definitions
  • Extensions to WebSockets: outgoing connections and broadcasts
  • A range of security enhancements, including TOTP two-factor authentication in web-admin
  • General performance boosts - both run-time and server startup

What is Zato?

Zato is an open-source API integrations platform and backend application server composed of several major blocks of functionality:

  • Online request/response integrations using a wide range of protocols, including SAP, Odoo, IBM MQ, REST, AMQP, Search, Email and many more

  • Publish/subscribe message topics with queues and guaranteed delivery

  • Single Sign-On for REST and Python applications

Its HA architecture is highly-scalable and everything comes with a web-based GUI along with a command-line interface and admin APIs.

If you are looking for a highly productive Python-based open-source platform designed specifically to integrate systems or expose APIs in many protocols or data formats, to be used by other servers, frontends or mobile, Zato is the choice.

Quick links:

Invoking individual WebSocket connections has been supported since Zato 3.0 and Zato 3.1 adds new functionality on top of it - message broadcasting - which lets one notify all the clients connected to a particular channel. Here is how to use it.

Web-admin

Let's say that there is a WebSocket channel such as the one here:

In the context of broadcast messages, the most important part of this definition is its name - below, we will be sending messages to all clients connected to that particular channel by its name.

Python code

# -*- coding: utf-8 -*-

from __future__ import absolute_import, division, print_function, unicode_literals


# Zato
from zato.server.service import Service

class WSXBroadcast(Service):

    def handle(self):

        # Channel to invoke
        name = 'My WSX API Channel'

        # Get a handle to the channel object
        channel = self.wsx.channel[name]

        # Message to send
        data = 'Hello from Zato'

        # Broadcast this message to all clients connected to that channel
        channel.broadcast(data)

And this is literally it - you have just broadcast a message to all WebSocket connections of that channel. It does not matter if clients are in JavaScript, Python, if they are other Zato servers or browsers - all of them receive the notification.

Note that the messages are always sent in background - they are treated as asynchronouous messages and Zato does not wait for any potential response from the clients.

Employing JSON Schema is an easy and straightforward way to validate input to your APIs - learn below how it can be enabled in Zato 3.1+ services in one line of code.

Preparing a schema

Supposing that our service needs two parameters:

  • name (string)
  • pub_year (integer)

This is how a schema representing them may look like - let's save it in a file called myschema.json.

{
  "type": "object",
  "required": ["name", "pub_year"],
  "properties": {
    "name":  {"type" : "string"},
    "pub_year": {"type" : "number"}
  }
}

This is as simple as it can get - we list our parameters and for each one, we also say of what datatype they are.

Note that a JSON Schema may be of arbitrary complexity to express any kind of business needs. It is also possible to split bigger schemas into reusable pieces, ready for inclusion in more than one place.

Uploading the schema

Each server has a directory called config/repo/schema/json - this is where the schema needs to be uploaded.

For instance, if path to a server is /home/zato/env/server1 then the full path will be /home/zato/env/server1/config/repo/schema/json.

Save myschema.json to this directory and restart your server before continuing to the next step.

Configuring a service

To make use of the prepared schema, a service uses an attribute appropriately called schema, as below.

# -*- coding: utf-8 -*-

from __future__ import absolute_import, division, print_function, unicode_literals

# Zato
from zato.server.service import Service

class MyService(Service):

    # Require JSON Schema validation
    schema = 'myschema.json'

    def handle(self):

        # If we are here, it means that the input schema validation succeeded
        self.logger.info("My request: %s", self.request.payload)

Note that the value of this attribute can be either relative or absolute. If it is relative, it is in relation to the main directory (config/repo/schema/json). If it is an absolute one, it must be a full path to the schema file in the filesystem.

Schema validation in runtime

You do not need to do anything else for schema validation to work, let's observe it.

As expected, on invalid input we get an error:

$ curl localhost:17010/schema/check -d '{"user_id":123}' ; echo
{"zato_env":
  {"result": "ZATO_ERROR",
   "cid": "0ee199609d5020d7cb8ca924",
   "details":
     {"is_ok": false,
      "cid": "0ee199609d5020d7cb8ca924",
      "message": "Invalid request"
  }}}
$

Whereas with correct data on input a message is stored in a log file:

INFO - My request: {'name':'The Garden of Cyrus', 'pub_year':1658}

Web-admin options

Sometimes it is handy to be able to disable input validation on demand, without redeployment of code. On the other hand, it is at times convenient to return more details than "Invalid request" alone.

This is exactly what can be done in web-admin, for each service separately:

Summary

That is all - you have just created a schema and enabled it for your service. Zato picked it up, started to enforce its definitions in runtime, and the whole of it took just a single line of code. Moreover, you can dynamically change the way it is used without making any updates to the source code.