Cosmos DB Server-Side Programming with TypeScript – Part 3: Stored Procedures

Stored procedures, the second type of server-side code that can run within Cosmos DB, provide the ability to execute blocks of functionality from inside the database engine. Typically we use stored procedures for discrete tasks that can be encapsulated within a single invocation. In this post, we will discuss some situations where stored procedures can be used and the actions and queries that they can perform. We’ll then start to work through the server-side API model, and look at how we can work with the incoming stored procedure invocation’s request and response as well as the Cosmos DB collection itself. Then we’ll build a simple stored procedure using TypeScript.

This post is part of a series of posts about server-side programming for Cosmos DB:

  • Part 1 gives an overview of the server side programmability model, the reasons why you might want to consider server-side code in Cosmos DB, and some key things to watch out for.
  • Part 2 deals with user-defined functions, the simplest type of server-side programming, which allow for adding simple computation to queries.
  • Part 3 (this post) talks about stored procedures. These provide a lot of powerful features for creating, modifying, deleting, and querying across documents – including in a transactional way.
  • Part 4 (coming soon) introduces triggers. Triggers come in two types – pre-triggers and post-triggers – and allow for behaviour like validating and modifying documents as they are inserted or updated, and creating secondary effects as a result of changes to documents in a collection.
  • Part 5 (coming soon) discusses unit testing your server-side scripts. Unit testing is a key part of building a production-grade application, and even though some of your code runs inside Cosmos DB, your business logic can still be tested.
  • Finally, part 6 (coming soon) explains how server-side scripts can be built and deployed into a Cosmos DB collection within an automated build and release pipeline, using Microsoft Visual Studio Team Services (VSTS).

Stored Procedures

Stored procedures let us encapsulate blocks of functionality, and then later invoke them with a single call. As with the other server-side code types, the code inside the stored procedure run inside the Cosmos DB database engine. Within a stored procedure we can perform a range of different actions, including querying documents as well as creating, updating, and deleting documents. These actions are done within the collection that the stored procedure is installed in. Of course, we can augment these collection-based actions with custom logic that we write ourselves in JavaScript or – as is the case in this series – TypeScript.

Stored procedures are extremely versatile, and can be used for a number of different tasks including:

  • Encapsulating a complex set of queries and executing them as one logical operation – we will work with this example below.
  • Retrieving data for a report using a complex set of queries, and combining the results into a single response that can be bound to a report UI.
  • Generating mock data and inserting it into the collection.
  • Doing a batch insert, update, upsert, or delete of multiple documents, taking advantage of the transactional processing of stored procedures.

Of course, if you are building a simple application without the need for complex queries, you may be able to achieve everything you need with just the Cosmos DB client-side SDKs. However, stored procedures do give us some power and flexibility that is not possible with purely client-based development, including transaction semantics.

Transactions and Error Handling

Cosmos DB’s client programming model does not provide for transactional consistency. However, stored procedures do run within an implicit transaction. This means that if you modify multiple documents within a stored procedure, then either all of those changes will be saved or – in the event of an error – none of them will be saved. Transactions provide four guarantees (atomicity, consistency, isolation, and durability, also known as ACID). More information on Cosmos DB transactions is available here.

The Cosmos DB engine handles committing and rolling back transactions automatically. If a stored procedure completes without any errors being thrown then the transaction will be committed. However, if even one unhandled error is thrown, the transaction will be rolled back and no changes will be made.

Working with the Context

Unlike user-defined functions, which we discussed in part 1 of this series, stored procedures allow us to access and make changes to the collection that they run within. We can also return results from stored procedures. Both of these types of actions require that we work with the context object.

Within a stored procedure, we can make a call to the getContext() function. This returns back an object with three functions.

  • getContext().getRequest() is used to access the request details. This is mostly helpful for triggers, and we will discuss this in part 4 of this series.
  • getContext().getResponse() lets us set the response that we should send back. For stored procedures, this is the way that we will return data back to the client if the stored procedure has something to return.
  • getContext().getCollection() gives us access to the Cosmos DB collection that the stored procedure runs within. In turn, this will let us read and write documents.

Each of the calls above corresponds to a type – Context, Request, Response, and Collection, respectively. Each of these types, in turn, provide a set of functions for interacting with the object. For example, getContext().getCollection().queryDocuments() lets us run a query against documents in the collection, and getContext().getResponse().setBody() lets us specify the output that we want the stored procedure to return. We’ll see more of these functions as we go through this series.

Also note that the double-underscore (__) is automatically mapped to the getContext().getCollection() function. In this series we won’t use this shortcut because I want to be more explicit, especially to help with testing when we get to part 5.

Type Definitions

Human-readable documentation for the types and their members is provided by Microsoft here. Of course, one of the main reasons we’re using TypeScript in this series is so that we get type checking and strong typing against the Cosmos DB object model, so a human-readable description isn’t really sufficient. In TypeScript, this is done through type definitions – descriptions of types and their members that TypeScript can use to power its type system.

While Microsoft doesn’t provide first-party TypeScript type definitions for Cosmos DB, an open-source project named DefinitelyTyped provides and publishes these definitions here. (Note that the type definitions use the old name for Cosmos DB – DocumentDB – but they are still valid and being updated.)

Queries

One of the main things we’ll frequently do from inside stored procedures is execute queries against Cosmos DB. This is how we can retrieve data and perform custom logic on it within the stored procedure. Cosmos DB provides an integrated JavaScript query syntax for executing queries. The syntax is documented here and lets us write queries like this:

 

 

 

 

 

 

 

 

 

which will map to the following SQL query:

 

 

 

 

However, there are some limitations to this query syntax. We can’t perform aggregations, and we can’t do queries using user-defined functions. These limitations may be lifted in future, but for now, in this series we will use the SQL syntax instead so that we can get the full power of Cosmos DB’s query engine. We can use this type of syntax to make a SQL-based query:

 

 

 

 

 

 

 

 

In your own stored procedures and triggers you can decide which approach – integrated JavaScriptor SQL – makes more sense.

We can also request a document by ID if we want to as well:

 

 

 

 

 

 

Another important consideration when executing queries is that Cosmos DB limits the amount of time that a stored procedure can run for. We can test whether our stored procedure is approaching that time limit by inspecting the return value we receive when we submit the query. If we receive a false response, it means the query wasn’t accepted – and that it’s probably time to wrap up our logic before we get forcibly shut off. In some stored procedures, receiving a false like this may mean that you simply throw an error and consider the whole stored procedure execution to have failed.

Parameterised Queries

In the last section, we discussed using the collection.queryDocuments function to execute a SQL query against the collection. While this technically works, once we start including parameters in our queries then we’d need to concatenate them into the query string. This is a very, very bad idea – it opens us up to a class of security vulnerabilities called SQL injection attacks.

When we’re writing SQL queries with parameters, we should instead the overload of the collection.queryDocuments function that accepts an IParameterizedQuery instead of a string. By doing this, we pass our parameters explicitly and ensure that they are handled and escaped appropriately by the database engine. Here’s an example of how we can do this from our TypeScript code:

 

 

 

 

 

Updating Documents

We can also make changes to documents within the collection,. There are several functions on the Collection type to help with this, including:

  • createDocument inserts a new document into the collection.
  • replaceDocument updates an existing document in the collection. You must provide the document link to use this function.
  • deleteDocument deletes a document from the collection.
  • upsertDocumentThere are also functions to deal with attachments to documents, but we won’t work with those in this series.

These functions that work with existing documents also take an optional parameter to specify the etag of the document. This allows for us to take advantage of optimistic concurrency. Optimistic concurrency is very useful, but is outside the scope of this series.

Structuring Your Stored Procedure Code

Stored procedures will often become more complex than UDFs, and may incorporate business logic as well as interaction with the Cosmos DB collection inside the code. When we’re writing a production-grade application it is important that we structure our code correctly so that each part is testable, and has a clearly defined responsibility and interactions with other components. In Cosmos DB, we are interacting with the collection in order to execute queries and update documents, and these side effects are important to test as well. We’ll discuss testing in more detail in part 5, but even before we worry about testing, it’s a good idea to structure our code properly.

When I write Cosmos DB stored procedures, I find it’s helpful to have a simple ‘entry point’ function. This entry point does the interaction with the getContext() function and retrieves the Collection, Request, and Response objects as required. These, along with any other parameters, are then passed into an internal implementation function, which in turn may invoke other functions to do other parts of the logic. By structuring the functions in this way we can ensure that each function has a clear purpose, and that the external components can be mocked and/or spied upon during our tests.

Writing our stored procedure in TypeScript also gives us the ability to store our functions in different .ts files if we want. This is helpful when we have long and complicated stored procedures, or if we want to keep interfaces and functions in separate files. This is largely a choice of style, since TypeScript’s compiler will simply combine all of the functions together at compilation time and will emit a single JavaScript output file (because we have set the outFile property in our tsconfig.json file). One important note on this though – if you have functions spread across multiple files, it is important to pay attention to the order in which the functions get emitted. The stored procedure’s ‘entry point’ function must appear first in the output JavaScript file. TypeScript can be instructed to do this by explicitly listing the entry point function’s file first in the include directive within the tsconfig.json file, and then having a wildcard * to catch the remaining files, like this:

 

 

 

 

 

 

 

Calling Stored Procedures

Once a stored procedure is written, we need to call it in order to check that it’s working, and then to use it in our real applications. There are several ways we can call our stored procedure and pass in the arguments it expects.

  • The Azure Portal provides a test interface for invoking stored procedures. This is how we will test the stored procedure we write below.
  • The client SDKs provide platform-specific features for invoking stored procedures. For example, the .NET client library for Cosmos DB provides the DocumentClient.ExecuteStoredProcedureAsync function, which accepts the ID of a stored procedure and any arguments that it might be expecting.
  • The Cosmos DB REST API also allows for invoking stored procedures directly.

Again, the exact way you call stored procedures may depend on the Cosmos DB API you are targeting – in this series we are using the SQL API, and the invocation mechanisms for MongoDB, Gremlin, and the other APIs may be different.

Now that we have talked about the different aspects of writing stored procedures, we can think about how we might use a stored procedure in our sample scenario.

Defining our Stored Procedure

In our hypothetical ordering application, we have a Cosmos DB collection of documents representing orders. Each order document contains a customer’s ID (as we saw in part 2 of this series), and also contains a set of items that the customer ordered. An order item consists of the product ID the customer ordered and the quantity of that product.

Because Cosmos DB is a schemaless database, our order documents may coexist with many other documents of different types. Therefore, we also include a type parameter on the document to indicate that it is an order. This type discriminator pattern is quite common in schemaless databases.

An example of an order document in our collection is:

For this stored procedure we want to pass in a set of product IDs, and get back a grouped list of IDs for customers who have ordered any of those products. This is similar to doing a GROUP BY in a relational database – but currently Cosmos DB doesn’t provide this sort of grouping feature, and so we are using a stored procedure to fill in the gap. Doing this from the client SDK would require multiple queries against the collection, but by using a stored procedure we can just make one call.

At a high level, our stored procedure logic looks like this:

  1. Accept a list of product IDs as an argument.
  2. Iterate through the product IDs.
  3. For each product ID, run a query to retrieve the customer IDs for the customers that have ordered that product, filtering to only query on order documents. The query we’ll run looks like this:

 

 

  1. Once we have all of the customer IDs for each product in our list, create a JSON object to represent the results like this:

Preparing a Folder

Now we can start writing our stored procedure. If you want to compare against my completed stored procedure, you can access it on GitHub.

In part 2 of this series we covered how to set up the Cosmos DB account and collection, so I won’t go through that again. We also will reuse the same folder structure as we did in part 2, so you can refer to that post if you’re following along.

There’s one major difference this time though. In our package.json file, we need to add a second entry into the devDependencies list to tell NPM that we want to include the TypeScript type definitions for Cosmos DB. Our package.json file will look like this:

 

 

 

Open a command prompt or terminal window, and run npm install within this folder. This will initialise TypeScript and the type definitions.

We’ll also adjust the tsconfig.json file to emit a file with the name sp-getGroupedOrders.js:

Writing the Stored Procedure

Now let’s create a file named src/getGroupedOrders.ts. This is going to contain our stored procedure code. Let’s start with adding a basic function that will act as our entry point:

As discussed above, this is a pattern I try to follow when I write Cosmos DB server-side code. It helps to keep the interaction with the getContext() function isolated to this one place, and then all subsequent functions will work with explicit objects that we’ll pass around. This will help when we come to test this code later. You can see that this function calls the getGroupedOrdersImpl function, which does the actual work we need done – we’ll write this shortly.

Before then, though, let’s write a basic interface that will represent our response objects:

 

Our getGroupedOrdersImpl function will accept an array of product IDs and the collection in which to query them, and it will return an array of these CustomersGroupedByProducts. Of course, since CustomersGroupedByProduct is a TypeScript interface, we can use it within our functions for type safety, but it will be stripped out when we compile the code into JavaScript.

Now we can add a basic shell of an implementation for our getGroupedOrdersImpl function. As you type this, notice that (if you’re in an editor that supports it, like Visual Studio Code) you get IntelliSense and statement completion thanks to the TypeScript definitions:

This function prepares a variable called outputArray, which will contain all of our product/customer groupings. Then we have some placeholder code to perform our actual queries, which we’ll fill in shortly. Finally, this function returns the output array.

Now we can fill in the placeholder code. Where we have REPLACEME in the function, replace it with this:

There’s a lot going on here, so let’s break it down:

  • The first part (lines 1-6) sets up a new IParameterizedQuery, which lets us execute a SQL query using parameters. As discussed above, this is a much more secure way to handle parameters than string concatenation. The query will find all orders containing the product ID we’re looking for, and will return back the customer ID.
  • Next, the query callback function is prepared (lines 7-18). This is what will be called when the query results are available. In this function we pull out the results and push them onto our outputArray, ready to return to the calling function.
  • Then we try to execute the query against the collection by using the collection.queryDocuments() function (line 19). This function returns a boolean to indicate whether the query was accepted (line 20). If it wasn’t, we consider this to be an error and immediately throw an error ourselves (line 22).

That’s it! The full stored procedure file looks like this:

Here’s what the your folder structure should now look like:

  • /
    • package.json
    • tsconfig.json
    • src/
      • getGroupedOrders.ts

Compiling the Stored Procedure

As in part 2, we can now compile our function to JavaScript. Open up a command prompt or terminal, and enter npm run build. You should see that a new output folder has been created, and inside that is a file named sp-getGroupedOrders.js. If you open this, you’ll see the JavaScript version of our function, ready to submit to Cosmos DB. This has all of the type information removed, but the core logic remains the same. Here’s what it should look like:

Deploying the Stored Procedure

Now let’s deploy the stored procedure to our Cosmos DB collection. Open the Azure Portal, browse to the Cosmos DB account, and then go to Script Explorer. Click Create Stored Procedure.

cosmos-sp-1

Enter getGroupedOrders as the ID, and then paste the contents of the compiled sp-getGroupedOrder.js JavaScript file into the body.

cosmos-sp-2

Click Save to install the stored procedure to Cosmos DB. (Once again, this isn’t the best way to install a stored procedure – we’ll look at better ways in part 6 of this series.)

Testing the Stored Procedure

Now let’s insert some sample data so that we can try the stored procedure out. Let’s insert these sample documents using Document Explorer, as described in part 2.

Here are the three sample documents we’ll use:

Now go back into Script Explorer, open the stored procedure, and notice that there is a test panel below the script body textbox. We can enter our stored procedures parameters into the Inputs field. Let’s do that now. Enter [["P1", "P2", "P10"]] – be careful to include the double square brackets around the array. Then click the Save & Execute button, and you should see the results.

cosmos-sp-3

If we reformat them, our results look like the following. We can see that we have an array containing an object for each product ID we passed into the query, and each object has a list of customer IDs who ordered that product:

So our stored procedure works! We’ve now successfully encapsulated the logic involved in querying for customers that have ordered any of a set of products.

Summary

Stored procedures give us a way to encapsulate queries and operations to be performed on a Cosmos DB collection, and to invoke them as a single unit. Stored procedures run within an implicit transaction, so any unhandled errors will result in the changes being rolled back. Unlike in UDFs, we are also able to access the collection within a stored procedure by using the getContext() function, and by retrieving the Response and Collection objects. This allows us to return rich data, including objects and arrays, as well as to interact with the collection and its documents. In the next part of this series we will discuss triggers, the third type of server-side programming available in Cosmos DB, which allow us to intercept changes happening to documents within the collection.

Key Takeaways

  • Stored procedures encapsulate logic, queries, and actions upon documents within the collection.
  • Stored procedures provide transactional isolation, and all stored procedures run within a transaction scope.
  • The getContext() function allows us to access the Response and Collection objects.
  • TypeScript definitions are available to help when writing code against these objects.
  • Cosmos DB provides an integrated query syntax, which is great for simple queries, but doesn’t cover all possible queries that can be executed against the collection.
  • Arbitrary SQL queries can also be executed. If these contain parameters then the IParameterizedQuery interface should be used to ensure safe coding practices are adhered to.
  • The order of functions inside the stored procedure’s file matters. The first function will be the one that Cosmos DB treats as the entry point.
  • You can view the code for this post on GitHub.

Xamarin Application Architecture

In this post, I will talk about strategies for developing a cross-platform Xamarin application with focus on code sharing, increasing testability, and reducing overall development and maintenance efforts.

The application architecture is itself problem specific, but there are certain design patterns that can guide the overall structure of the application. The ones I mostly work with are Model-View-Controller, Model-View-Presenter, and Model-View-ViewModel.

MVC should be adopted for small applications or proof of concept. Since, Android and iOS both natively support MVC, it will mean less roadblocks, and faster implementation.

MVVM reduces platform specific code, and most of the logic is shared across platforms using PCLs. There are great MVVM frameworks out there that work really well with Xamarin, such as MVVM Cross, and the official Xamarin Forms.

MVVM Cross is a data driven pattern, and the presentation logic is quiet centralised. This means any upgrades to the library or to the system, can break custom navigation and screen transitions within the application. As Android and iOS platforms are constantly upgraded, with newer restrictions around security and permissions (background tasks / app permissions), and as user interface (gestures / notifications / newer design guidelines) are greatly improved, support for these can be costly in both time and effort. That being said, it is still a very powerful platform, and most applications can be written with it, with great results.

Xamarin.Forms is a UI first framework, and allows .Net developers to use their existing XAML skills for developing mobile applications. However, this platform is still maturing and needs to reach a more stable stage. Even though native controls can now be used in XAML, but one looses advantage of data binding, and the extra effort required to maintain proper navigation in the application, and catering for various screen types, out does the benefit for now.

MVP is another pattern that works really well with Xamarin cross-platform applications. It is a variant of the MVC pattern and allows full use of platform native capabilities, with great control over the UI of the application. It is the pattern of choice for native development and we will look more into it below.

MVP has 3 major components

Model: This is data that we want to show in our views. Apart from data classes, this carries responsibility for retrieving, storing, validating advanced data sets, and advanced functions such as syncing and conflict resolution, cache maintenance, offline capabilities.

View: This module is responsible for showing data to user and responding to user gestures and actions. It should only be dependent upon the presenter and native platform capabilities for styling.

Presenter: Presenter is the layer below the view and is responsible for providing data the view can use. It relies on the model layer to retrieve data, and can also publish other events to UI, such as loading data state, timeout and error state.

The golden rule of mobile development is to keep the UI layer as dumb as possible. That means UI only needs to know about its data, view transitions and animations, and publishing interaction events, such as gestures, button clicks. As mentioned view is completely dependent upon presenter, and presenter is dependent upon the view for human interaction. However, to promote unit testing for each of the modules, presenter and view need to be decoupled.

To achieve this, we can define a contract between presenter and the view. For a login screen the view’s contract can be:

and the presenter contract can be

The presenter should be platform independent so that it can be shared across different mobile platforms. This means, it does not respond to UI life-cycle methods, unless explicitly told so.

The controllers in iOS and Activities in Android are never directly created using constructor, which means that we cannot use dependency injection to provide a presenter to our view. In this scenario I like to use an anti-pattern called ‘Service Locator’.

Service locator is considered anti-pattern because of the complexities of managing dependencies, and dependency’s child dependencies. This is highly error prone, but in case of multi-threaded programs, where start of the application can run many different initialisation threads, double instance of a service can be created. This pattern works well in simple scenarios and this is exactly what we are trying to solve. A dependency service can be used to locate the presenter implementation inside the view.

If the scenario was anymore complex, it certainly means, that View has been assigned more work than it needs to perform and is against MVP principles. A sample of this is shown below

As shown above the AppDelegate in the application loads all the presenters in the application. When the view loads, it uses the service locator instance to retrieve its presenter.

By abstracting the presenter from the view gives us many advantages.

  • One presenter will not work on all devices. Such as finger print functionality for login can only be used on devices that have the required hardware. In this case inheritance can be used to create versions that can handle extra features, while having no functionality in base presenters.
  • By abstracting the creation of the presenter outside the view, allows to make our UI really passive, and thus we are able to test UI independent of any other module.
  • This method allows to create presenters that can help automate UI testing, performing common testing scenarios automatically. This can also be used for automatic product demos.

 

The above implementation of MVP suffers from one disadvantage. It ignores the asynchronous nature of the mobile applications. Both Android and iOS are quite capable devices. Even a simple Notes application performs a lot of activities when loading. These can be logging in the user, loading the notes from the cache, syncing down any notes created on other device, syncing up any notes created during offline mode, resolving conflicts, showing progress to the user, restoring state to what it was when the application was last closed. These activities can take from few milliseconds to few seconds. The longer the application takes to boot up, the higher the chance that the user will stop using the application, and will eventually remove it from the device.

Enterprise applications are much more complex. In a perfect world, they are developed using mobile-first approach. However, not all companies can follow this and instead of the application interacting with a unified set of APIs, it ends up interacting with multiple legacy systems, combine the result and then provide it to the UI layer. This means that the application load time can be really high especially if there is stale data on the device.

In such a scenario, instead of defining contracts between the presenter and view, a more reactive approach can be used. This allows us to build application with a user centric approach. This means we think of how the user is going to interact with application, and what does the user see while waiting for data to be available in the application. The code below shows this for a login screen.

The above code shows the asynchronous nature of the View, and it follows a push approach, reacting to events when raised by the presenter. This further decouple the bond between the view and the presenter allowing for easier testing, and also helps to identify real life scenarios within the app, such as data being delayed or no connectivity.

Thus, we see how MVP aims at building a very user centric app, with a very passive and light UI layer. Both iOS and Android platforms are competing to enhance user experience. In future, we can see apps that will react and adapt to user habits using machine learning. This also means that they will have to work in a stricter environment, like stricter guidelines for UI, background tasks, app permissions, reduced background tasks, and change in application notification patterns. MVP will definitely allow to handle these changes, without affecting user experience.

Easy Filtering of IoT Data Streams with Azure Stream Analytics and JSON reference data

siliconvalve

I am currently working on an next-gen widget dispenser solution that is gradually being rolled out to trial sites across Australia. The dispenser hardware is a modern platform that provides telemetry data that can be used for various purposes by the locations at which the dispenser is deployed and potentially by other third parties.

In addition to these next-gen dispensers we already have existing dispenser hardware at the locations that emits telemetry that we already use for other purposes in our solution. To our benefit both the new and existing hardware emits the same format telemetry data 🙂

A sample telemetry entry is shown below.

We take all of the telemetry data from new and old hardware at all our sites and feed it into an Azure Event Hub which allows us to perform multiple actions, such as archival of the data to Blob Storage using Azure Event Hub Capture

View original post 468 more words

Update FSTAB on multiple EC2 instances using Run Commands

Scenario:

  • Customer Running multiple Linux Ec2 instance in AWS.
  • Customer reports that Instances are loosing mount points after a reboot.

Solution :

The resolution requires to update the fstab file on all the instances.

fstab is a system configuration file on Linux and other Unix-like operating systems that contains information about major filesystems on the system. It takes its name from file systems table, and it is located in the /etc directory ( ref : http://www.linfo.org/etc_fstab.html)

In order to update files on multiple servers we will utilize the following

  • ECHO command with append parameter (>>) to update the text file through shell
  • SSM Run Command to execute the command on multiple machines.

Note : All the concerned EC2 instances should have SSM manager configured.

Step 1 : Login to the AWS Console and click  EC2

click on ec2

 Step 2: Click on Run Command on the Systems Manager Services section

click on Run command

Step 3: Click on Run Command in the main panel

click-on-run-command-2.png

Step 4: Select Run-Shell Script

select run-shell script

Step 5: Select Targets 

Note : Targets can be selected manually or we can use Tags to perform the same activity on multiple instances with the matching tag.

select targets and stuff

Step 6:

Enter the following information :

  • Execute on : Specifies the number of target the commands can be executed concurrently. Concurrently running commands save time in execution.
  • Stop After : 1 errors
  • Timeout ( seconds) : leave the default 600 seconds

Step 7: Select the commands section and paste the command

echo '10.x.x.x:/ /share2 nfs4 nfsvers=4.1,rsize=1048576,wsize=1048576,hard,timeo=600,retrans=2,_netdev 0 0' >> /etc/fstab
echo '10.x.x.x:/ /share1 nfs4 nfsvers=4.1,rsize=1048576,wsize=1048576,hard,timeo=600,retrans=2,_netdev 0 0' >> /etc/fstab
echo '192.x.x.x:/ /backup1 nfs4 nfsvers=4.1,rsize=1048576,wsize=1048576,hard,timeo=600,retrans=2,_netdev 0 0' >> /etc/fstab
echo '172.x.x.x:/ /backup2 nfs4 nfsvers=4.1,rsize=1048576,wsize=1048576,hard,timeo=600,retrans=2,_netdev 0 0' >> /etc/fstab            

 

Step 8 : Click on Run click on run

Step 9: Click on command id to get update regarding the execution success of failure

click on command id to check the status of the coomand

Cosmos DB Server-Side Programming with TypeScript – Part 2: User-Defined Functions

User-defined functions (UDFs) in Cosmos DB allow for simple calculations and computations to be performed on values, entities, and documents. In this post I will introduce UDFs, and then provide detailed steps to set up a basic UDF written in TypeScript. Many of these same steps will be applicable to stored procedures and triggers, which we’ll look at in future posts.

This is the second part of a series of blog posts on server-side development using Cosmos DB with TypeScript.

  • Part 1 gives an overview of the server side programmability model, the reasons why you might want to consider server-side code in Cosmos DB, and some key things to watch out for.
  • Part 2 (this post) deals with user-defined functions, the simplest type of server-side programming, which allow for adding simple computation to queries.
  • Part 3 talks about stored procedures. These provide a lot of powerful features for creating, modifying, deleting, and querying across documents – including in a transactional way.
  • Part 4 (coming soon) introduces triggers. Triggers come in two types – pre-triggers and post-triggers – and allow for behaviour like validating and modifying documents as they are inserted or updated, and creating secondary effects as a result of changes to documents in a collection.
  • Part 5 (coming soon) discusses unit testing your server-side scripts. Unit testing is a key part of building a production-grade application, and even though some of your code runs inside Cosmos DB, your business logic can still be tested.
  • Finally, part 6 (coming soon) explains how server-side scripts can be built and deployed into a Cosmos DB collection within an automated build and release pipeline, using Microsoft Visual Studio Team Services (VSTS).

User-Defined Functions

UDFs are the simplest type of server-side development available for Cosmos DB. UDFs generally accept one or more parameters and return a value. They cannot access Cosmos DB’s internal resources, and cannot read or write documents from the collection, so they are really only intended for simple types of computation. They can be used within queries, including in the SELECT and WHERE clauses.

UDFs are simple enough that types are almost not necessary, but for consistency we will use TypeScript for these too. This will also allow us to work through the setup of a TypeScript project, which we’ll reuse for the next parts of this series.

One note on terminology: the word function can get somewhat overloaded here, since it can refer to the Cosmos DB concept of a UDF, or to the TypeScript and JavaScript concept of a function. This can get confusing, especially since a UDF can contain multiple JavaScript functions within its definition. For consistency I will use UDF when I’m referring to the Cosmos DB concept, and function when referring to the JavaScript or TypeScript concept.

Parameters

UDFs can accept zero or more parameters. Realistically, though, most UDFs will accept at least one parameter, since UDFs almost always operate on a piece of data of some kind. The UDF parameters can be of any type, and since we are running within Cosmos DB, they will likely be either a primitive type (e.g. a single string, number, array, etc), a complex type (e.g. a custom JavaScript object, itself comprised of primitive types and other complex types), or even an entire document (which is really just a complex type). This gives us a lot of flexibility. We can have UDFs that do all sorts of things, including:

  • Accept a single string as a parameter. Do some string parsing on it, then return the parsed value.
  • Accept a single string as well as another parameter. Based on the value of the second parameter, change the parsing behaviour, then return the parsed value.
  • Accept an array of values as a parameter. Combine the values using some custom logic that you define, then return the combined value.
  • Accept no parameters. Return a piece of custom data based on the current date and time.
  • Accept a complex type as a parameter. Do some parsing of the document and then return a single output.

Invoking a UDF

A UDF can be invoked from within the SELECT and WHERE clauses of a SQL query. To invoke a UDF, you need to include the prefix udf. before the function name, like this:

SELECT udf.parseString(c.stringField) FROM c

In this example, udf. is a prefix indicating that we want to call a UDF, and parseString is the name of the UDF we want to call. Note that this is identifier that Cosmos DB uses for the UDF, and is not necessarily the name of the JavaScript function that implements the UDF. (However, I strongly recommend that you keep these consistent, and will do so throughout this series.)

You can pass in multiple parameters to the UDF by comma delimiting them, like this:

SELECT udf.parseString(c.stringField, 1234) FROM c
SELECT udf.parseString(c.stringField1, c.stringField2) FROM C

To pass a hard-coded array into a UDF, you can simply use square brackets, like this:

SELECT udf.parseArray(["arrayValue1", "arrayValue2", "arrayValue3"])

Now that we’ve talked through some of the key things to know about UDFs let’s try writing one, using our sample scenario from part 1.

Defining our UDF

Let’s imagine that our order system was built several years ago, and our business has now evolved significantly. As a result, we are in the middle of changing our order schema to represent customer IDs in different ways. Cosmos DB makes this easy by not enforcing a schema, so we can simply switch to the new schema when we’re ready.

Our old way of representing a customer ID was like this:

Now, though, we are representing customers with additional metadata, like this:

However, we still want to be able to easily use a customer’s ID within our queries. We need a way to dynamically figure out the customer’s ID for an order, and this needs to work across our old and new schemas. This is a great candidate for a UDF. Let’s deploy a Cosmos DB account and set up this UDF.

Setting Up a Cosmos DB Account and Collection

First, we’ll deploy a Cosmos DB account and set up a database and collection using the Azure Portal. (Later in this series, we will discuss how this can be made more automatable.) Log into the Azure Portal and click New, then choose Cosmos DB. Click Create.

cosmos-udf-1

We need to specify a globally unique name for our Cosmos DB account – I have used johnorders, but you can use whatever you want. Make sure to select the SQL option in the API drop-down list. You can specify any subscription, resource group, and location that you want. Click Create, and Cosmos DB will provision the account – this takes around 5-10 minutes.

Once the account is created, open it in the Portal. Click Add Collection to add a new collection.

cosmos-udf-2

Let’s name the collection Orders, and it can go into a database also named Orders. Provision it with a fixed (10GB) capacity, and 400 RU/s throughput (the minimum).

cosmos-udf-3

Note that this collection will cost money to run, so you’ll want to remember to delete the collection when you’re finished. You can leave an empty Cosmo DB account for no charge, though.

Preparing a Folder

TypeScript requires that we provide it with some instructions on how to compile our code. We’ll also use Node Package Manager (NPM) to tie all of our steps together, and so need to prepare a few things before we can write our UDF’s code.

Note that in parts 2, 3, and 4 of this series, we will write each server-side component as if it was its own independent application. This is to keep each of these posts easy to follow in a standalone way. However, in parts 5 and 6, we will combine these into a single folder structure. This will more accurately represent a real-world application, in which you are likely to have more than one server-side component.

Create a new folder on your local machine and add a file named package.json into it. This contains the NPM configuration we need. The contents of the file should be as follows:

The project.json file does the following:

  • It defines the package dependencies we have when we develop our code. Currently we only have one – typescript.
  • It also defines a script that we can execute from within NPM. Currently we only have one – build, which will build our UDF into JavaScript using TypeScript’s tsc command.

At a command prompt, open the folder we’ve been working in. Run npm install, which will find and install the TypeScript compiler. If you don’t have NPM installed, install it by following the instructions here.

Next, create a file named tsconfig.json. This is the TypeScript project configuration. This should contain the following:

The tsconfig.json instructs TypeScript to do the following:

  • Find and compile all of the files with the .ts extension inside the src folder (we haven’t created this yet!).
  • Target ECMAScript 2015, which is the version of JavaScript that Cosmos DB supports. If we use more modern features of TypeScript, it will handle the details of how to emit these as ECMAScript 2015-compatible code.
  • Save the output JavaScript to a single file named output/udf-getCustomerId.js. This filename is arbitrary and it could be any name we want, but I find it helpful to use the convention of  {kind}-{objectName}.js, especially as we add more code in later parts of this series.
    Note that the outFile directive means that, even if we included multiple source TypeScript files, TypeScript will save the complete compiled script into a single output file. This is in keeping with the requirement that Cosmos DB imposes that a server-side component has to be specified in a single file.

Writing the UDF

Now we can write our actual UDF code! If you want to compare against my completed UDF, you can access it on GitHub.

Create a folder named src, and within that, create a file named getCustomerId.ts. (Once again, this file doesn’t need to be named this way, but I find it helpful to use the UDF’s name for the filename.) Here’s the contents of this file:

Briefly, here’s an explanation of what this file does:
  • It declares a function named getCustomerId, which accepts a single parameter of type OrderDocument and returns a string. This is the function that represents our UDF.
  • The function inspects the document provided, and depending on which version of the schema it follows, it pulls the customer ID out of the appropriate field.
  • If the customer ID isn’t in either of the places it expects to find them, it throws an error. This will be further thrown up to the client by Cosmos DB.
  • Finally, it declares an interface named OrderDocument. This represents the shape of the data we’re expecting to store in our collection, and it has both of the ways of representing customer IDs.
    Note that we are using an interface and not a class, because this data type has no meaning to Cosmos DB – it’s only for use at development and build time.
    Also note that we could put this into its own orderDocument.ts file if wanted to keep things separated out.

At the end of this, your folder should look something like this:

  • /
    • package.json
    • tsconfig.json
    •  src/
      • getCustomerId.ts

You can access a copy of this as a GitHub repository here.

We have now written our first UDF! We’re almost ready to run it – but before then, we need to compile it.

Compiling the UDF

At the command line run npm run build. This will run the build script we defined inside the package.json file, which in turn simply runs the tsc (TypeScript compiler) command-line application. tsc will find the tsconfig.json file and knows what to do with it.

Once it’s finished, you should see a new output folder containing a file named udf-getCustomerId.js. This is our fully compiled UDF! It should look like the following:

If you compare this to the code we wrote in TypeScript, you’ll see that it is almost the same – except all of the type information (variable types and the interface) have been stripped away. This means that we get the type safety benefits of TypeScript at authoring and compilation time, but the file we provide to Cosmos DB is just a regular JavaScript file.

Deploying the UDF

Now we can deploy the UDF. Back in the Azure Portal, open Script Explorer under the Collections section, and then click Create User Defined Function.

cosmos-udf-4.png

Enter getCustomerId in the ID field. This will be the name we address the UDF by when we start to call it from our queries. Note that you don’t have to use the same ID field here as the JavaScript function name – in fact, the JavaScript function can be named anything you like. For clarify, though, I find it helpful to keep everything in sync.

Now we can copy and paste the contents of the udf-getCustomerId.js file into the large script text box.

cosmos-udf-5

Click Save to install the UDF to Cosmos DB.

Testing the UDF

Finally, let’s test the UDF! We’ll need to add a couple of pieces of sample data. Click Document Explorer under the Collections section, and then click the Create button. Paste in this sample document:

Click Save, and then close the blade and create a second document with the following contents:

This gives us enough to test with. Now click Query Explorer under the Collections section. Enter the following query:

SELECT c.id, udf.getCustomerId(c) AS customerId FROM c

This query does the following:

  • Refers to each document within the current collection (c).
  • Runs the getCustomerId UDF, passing in the document contents. Note that to refer to a UDF, you must prefix the name of the UDF with udf..
  • Projects out the document ID (id) and the customerId as customerId.

You should see the following output:

That’s exactly what we wanted to see – the UDF has pulled out the correct field for each document.

As a point of interest, notice the Request Charge on the query results. Try running the query a few times, and you should see that it fluctuates a little – but is generally around 3.5 RUs.

Now let’s try passing in an invalid input into our UDF. Run this query:

SELECT udf.getCustomerId('123') FROM c

Cosmos DB will give you back the error that our UDF threw because the input data (123) didn’t match either of the schemas it expected:

Encountered exception while executing Javascript.
  Exception = Error: Document with id undefined does not contain customer ID in recognised format.
  Stack trace: Error: Document with id undefined does not contain customer ID in recognised format.
    at getCustomerId (getCustomerId.js:11:5)
    at __docDbMain (getCustomerId.js:15:5)
    at Global code (getCustomerId.js:1:2)

So we’ve now tested out the old customer ID format, the new customer ID format, and some invalid input, and the UDF behaves as we expect.

Summary

UDFs provide us with a way to encapsulate simple computational logic, and to expose this within queries. Although we can’t refer to other documents or external data sources, UDFs are a good way to expose certain types of custom business logic. In this post, we’ve created a simple UDF for Cosmos DB, and tested it using a couple of simple documents. In the next part of this series we’ll move on to stored procedures, which allow for considerably more complexity in our server-side code.

Key Takeaways

  • UDFs are intended for simple computation.
  • They can be used within queries, including in the SELECT and WHERE clauses.
  • UDFs cannot access anything within the Cosmos DB collection, nor can they access any external resources.
  • They can accept one or more parameters, which must be provided when calling the UDF.
  • The name of the JavaScript function does not have to match the name of the UDF, but to keep your sanity, I highly recommend keeping them consistent.
  • UDFs can be invoked from within a query by using the udf. prefix, e.g. SELECT udf.getCustomerId(c) FROM c.
  • You can view the code for this post on GitHub.

 

Automating the generation of Microsoft Identity Manager Configuration Documentation

Introduction

Last year Microsoft released the Microsoft Identity Manager Configuration Documenter which is available here. It is a fantastic little tool from Microsoft that supersedes its predecessor from the Microsoft Identity Manager 2003 Resource Toolkit (which only documented the Sync Server Configuration).

Running the tool (a PowerShell Module) against a base out-of-the-box reference configuration for FIM/MIM Servers reconciled against an exported configuration from the MIM Sync and Service Servers from an implementation, generates an HTML Report document that details the existing configuration of the MIM Service and MIM Sync.

Overview

Last year I wrote this post based on an automated solution I implemented to perform nightly backups of a FIM/MIM environment during development.

This post details how I’ve automated another daily task for a large development environment where a number of changes are going on and I wanted to have documentation generated that detailed the configuration for each day. Partly to quickly be able to work out what has changed when needing to roll back/re-validate changes, and also to have the individual configs from each day so they could also be used if we need to rollback.

The process uses an Azure Function App that uses Remote PowerShell into MIM to;

  1. Leverage a modified (stream lined version) of my nightly backup Azure Function to generate the Schema.xml and Policy.xml MIM Service configuration files and the Lithnet MIIS Automation PowerShell Module installed on the MIM Sync Server to export of the MIM Sync Server Configuration
  2. Create a sub-directory for each day under the MIM Documenter Tool to hold the daily configs
  3. Execute the generation of the Report and have the Report copied to the daily config/documented solution

Obtaining and configuring the MIM Configuration Documenter

Download the MIM Configuration Documenter from here and extract it to somewhere like c:\FIMDoco on your FIM/MIM Sync Server. In this example in my Dev environment I have the MIM Sync and Service/Portal all on a single server.

Then update the Invoke-Documenter-Contoso.ps1 (or whatever you’ve renamed the script to) to make the following changes;

  • Update the following lines for your version and include the new variable $schedulePath and add it to the $pilotConfig variable. Create the C:\FIMDoco\Customer and C:\FIMDoco\Customer\Dev directories (replace Customer with something appropriate.
######## Edit as appropriate ####################################
$schedulePath = Get-Date -format dd-MM-yyyy
$pilotConfig = "Customer\Dev\$($schedulePath)" # the path of the Pilot / Target config export files relative to the MIM Configuration Documenter "Data" folder.
$productionConfig = "MIM-SP1-Base_4.4.1302.0" # the path of the Production / Baseline config export files relative to the MIM Configuration Documenter "Data" folder.
$reportType = "SyncAndService" # "SyncOnly" # "ServiceOnly"
#################################################################
  • Remark out the Host Settings as these won’t work via a WebJob/Azure Function
#$hostSettings = (Get-Host).PrivateData
#$hostSettings.WarningBackgroundColor = "red"
#$hostSettings.WarningForegroundColor = "white"
  • Remark out the last line as this will be executed as part of the automation and we want it to complete silently at the end.
# Read-Host "Press any key to exit"

It should then look something like this;

Azure Function to Automate execution of the Documenter

As per my nightly backup process;

  • I configured my MIM Sync Server to accept Remote PowerShell Sessions. That involved enabling WinRM, creating a certificate, creating the listener, opening the firewall port and enabling the incoming port on the NSG . You can easily do all that by following my instructions here. From the same post I setup up the encrypted password file and uploaded it to my Function App and set the Function App Application Settings for MIMSyncCredUser and MIMSyncCredPassword.
  • I created an Azure PowerShell Timer Function App. Pretty much the same as I show in this post, except choose Timer.
    • I configured my Schedule for 6am every morning using the following CRON configuration
0 0 6 * * *
  • I also needed to increase the timeout for the Azure Function as generation of the files to execute the report and the time to execute the report exceed the default timeout of 5 mins in my environment (19 Management Agents). I increased the timeout to the maximum of 10 mins as detailed here. Essentially added the following to the host.json file in the wwwroot directory of my Function App.
{
 "functionTimeout": "00:10:00"
}

Azure Function PowerShell Timer Script (Run.ps1)

This is the Function App PowerShell Script that uses Remote PowerShell into the MIM Sync/Service Server to export the configuration using the Lithnet MIIS Automation and Microsoft FIM Automation PowerShell modules.

Note: If your MIM Service is on a different host you will need to install the Microsoft FIM Automation PowerShell Module on your MIM Sync Server and update the script below to change references to http://localhost:5725 to whatever your MIM Service host is.

Testing the Function App

With everything configured, manually running the Function App and checking the output window if you’ve configured everything correct will show success in the Logs as shown below. In this environment with 19 Management Agents it takes 7 minutes to run.

Running the Azure Function.PNG

The Report

The outcome everyday just after 6am is I have (via automation);

  • an Export of the Policy and Schema Configuration from my MIM Service
  • an Export of the MIM Sync Server Configuration (the Metaverse and all Management Agents)
  • I have the MIM Configuration Documenter Report generated
  • If I need to rollback changes I have the ability to do that on a daily interval (either for a MIM Service change or an individual Management Agent change

Under the c:\FIMDoco\Data\Customer\Dev\Report directory is the HTML Configuration Report.

Report Output.PNG

Opening the report in a browser we have the configuration of the MIM Sync and MIM Service.

Report

 

Replacing the service desk with bots using Amazon Lex and Amazon Connect (Part 3)

Hopefully you’ve had the chance to follow along in parts 1 and 2 where we set up our Lex chatbot to take and validate input. In this blog, we’ll interface with our Active Directory environment to perform the password reset function. To do this, we need to create a Lambda function that will be used as the logic to fulfil the user’s intent. The Lambda function will be packaged with the python LDAP library to modify the AD password attribute for the user. Below are the components that need to be configured.

Active Directory Service Account

To begin, we need to start by creating a service account in Active Directory that has permissions to modify the password attribute for all users. Our Lambda function will then use this service account to perform password resets. To do this, create a service account in your Active Directory domain and perform the following to delegate the required permissions:

  1. Open Active Directory Users and Computers.
  2. Right click the OU or container that contains organisational users and click Delegate Control
  3. Click Next on the Welcome Wizard.
  4. Click Add and enter the service account that will be granted the reset password permission.
  5. Click OK once you’ve made your selection, followed by Next.
  6. Ensure that Delegate the following common tasks is enabled, and select Reset user passwords and force password change at next logon.
  7. Click Next, and Finish.

KMS Key for the AD Service Account

Our Lambda function will need to use the credentials for the service account to perform password resets. We want to avoid storing credentials within our Lambda function, so we’ll store the password as an encrypted Lambda variable and allow our Lambda function to decrypt it using Amazon’s Key Management Service (KMS). To create the KMS encryption key, perform the following steps:

  1. In the Amazon Console, navigate to IAM
  2. Select Encryption Keys, then Create key
  3. Provide an Alias (e.g. resetpw) then select Next
  4. Select Next Step for all subsequent steps then Finish on step 5 to create the key.

IAM Role for the Lambda Function

Because our Lambda function will need access to several AWS services such as SNS to notify the user of their new password and KMS to decrypt the service account password, we need to provide our function with an IAM role that has the relevant permissions. To do this, perform the following steps:

  1. In the Amazon Console, navigate to IAM
  2. Select Policies then Create policy
  3. Switch to the JSON editor, then copy and paste the following policy, replacing the KMS resource with the KMS ARN created above
    {
       "Version": "2012-10-17",
       "Statement": [
         {
           "Effect": "Allow",
           "Action": "sns:Publish",
           "Resource": "*"
         },
         {
           "Effect": "Allow",
           "Action": "kms:Decrypt",
           "Resource": "<KMS ARN>"
         }
       ]
    }
  4. Provide a name for the policy (e.g. resetpw), then select Create policy
  5. After the policy has been created, select Roles, then Create Role
  6. For the AWS Service, select Lambda, then click Next:Permissions
  7. Search for and select the policy you created in step 5, as well as the AWSLambdaVPCAccessExecutionRole and AWSLambdaBasicExecutionRole policies then click Next: Review
  8. Provide a name for the role (e.g. resetpw) then click Create role

Network Configuration for the Lambda Function

To access our Active Directory environment, the Lambda function will need to run within a VPC or a peered VPC that hosts an Active Directory domain controller. Additionally, we need the function to access the internet to be able to access the KMS and SNS services. Lambda functions provisioned in a VPC are assigned an Elastic network Interface with a private IP address from the subnet it’s deployed in. Because the ENI doesn’t have a public IP address, it cannot simply leverage an internet gateway attached to the VPC for internet connectivity and as a result, must have traffic routed through a NAT Gateway similar to the diagram below.

Password Reset Lambda Function

Now that we’ve performed all the preliminary steps, we can start building the Lambda function. Because the Lambda execution environment uses Amazon Linux, I prefer to build my Lambda deployment package on a local Amazon Linux docker container to ensure library compatibility. Alternatively, you could deploy a small Amazon Linux EC2 instance for this purpose, which can assist with troubleshooting if it’s deployed in the same VPC as your AD environment.

Okay, so let’s get started on building the lambda function. Log in to your Amazon Linux instance/container, and perform the following:

  • Create a project directory and install python-ldap dependencies, gcc, python-devel and openldap-devel
    Mkdir ~/resetpw
    sudo yum install python-devel openldap-devel gcc
  • Next, we’re going to download the python-ldap library to the directory we created
    Pip install python-ldap -t ~/resetpw
  • In the resetpw directory, create a file called reset_function.py and copy and paste the following script

  • Now, we need to create the Lambda deployment package. As the package size is correlated with the speed of Lambda function cold starts, we need to filter out anything that’s not necessary to reduce the package size. The following zip’s the script and LDAP library:
    Cd ~/resetpw
    zip -r ~/resetpw.zip . -x "setuptools*/*" "pkg_resources/*" "easy_install*"
  • We need to deploy this package as a Lambda function. I’ve got AWSCLI installed in my Amazon Linux container, so I’m using the following CLI to create the Lambda function. Alternatively, you can download the zip file and manually create the Lambda function in the AWS console using the same parameters specified in the CLI below.
    aws lambda create-function --function-name reset_function --region us-east-1 --zip-file fileb://root/resetpw.zip --role resetpw --handler reset_function.lambda_handler --runtime python2.7 --timeout 30 --vpc-config SubnetIds=subnet-a12b3cde,SecurityGroupIds=sg-0ab12c30 --memory-size 128
  • For the script to run in your environment, a number of Lambda variables need to be set which will be used at runtime. In the AWS Console, navigate to Lambda then click on your newly created Lambda function. In the environment variables section, create the following variables:
    • Url – This is the LDAPS URL for your domain controller. Note that it must be LDAP over SSL.
    • Domain_base_dn – The base distinguished name used to search for the user
    • User – The service account that has permissions to change the user password
    • Pw – The password of the service account
  • Finally, we need to encrypt the Pw variable in the Lambda console. Navigate to the Encryption configuration and select Enable helpers for encryption in transit. Select your KMS key for both encryption in transit and at reset, then select the Encrypt button next to the pw variable. This will encrypt and mask the value.

  • Hit Save in the top right-hand corner to save the environment variables.

That’s it! The Lambda function is now configured. A summary of the Lambda function’s logic is as follows:

  1. Collect the Lambda environment variables and decrypt the service account password
  2. Perform a secure AD bind but don’t verify the certificate (I’m using a Self-Signed Cert in my lab)
  3. Collect the user’s birthday, start month, telephone number, and DN from AD
  4. Check the user’s verification input
  5. If the input is correct, reset the password and send it to the user’s telephone number, otherwise exit the function.

Update and test Lex bot fulfillment

The final step is to add the newly created Lambda function to the Lex bot so it’s invoked after input is validated. To do this, perform the following:

  1. In the Amazon Console, navigate to Amazon Lex and select your bot
  2. Select Fulfillment for your password reset intent, then select AWS Lambda function
  3. Choose the recently created Lambda function from the drop down box, then select Save Intent

That should be it! You’ll need to build your bot again before testing…

My phone buzzes with a new SMS…

Success! A few final things worth noting:

  • All Lambda execution logs will be written to CloudWatch logs, so this is the best place to begin troubleshooting if required
  • Modification to the AD password attribute is not possible without a secure bind and will result in an error.
  • The interrogated fields (month started and date of birth) are custom AD attributes in my lab.
  • Password complexity in my lab is relaxed. If you’re using the default password complexity in AD, the randomly generated password in the lambda function may not meet complexity requirements every time.

Hope to see you in part 4, where we bolt on Amazon Connect to field phone calls!

Enable Cost Allocation Tags to differentiate project based billing

When running in an AWS public cloud environment, many times there is a need to dissect the billing across different projects for accounting and accrual purposes. AWS provides a mechanism to aggregate related platform costs using a feature known as Cost Allocation Tags. With this feature you can designate Tags on your AWS resources to track costs on a detailed level.

From the AWS Documentation:

Activating tags for cost allocation tells AWS that the associated cost data for these tags should be made available throughout the billing pipeline. Once activated, cost allocation tags can be used as a dimension of grouping and filtering in Cost Explorer, as well as for refining AWS budget criteria.

 

For example, to view cost allocation based on various project resources in your AWS account, you can tag these resources (EC2 instances, S3 buckets, etc) with a tag named “Project”. Next the Project tag can be activated as a Cost Allocation Tag. From then on AWS will include this tag in associated cost data to allow for filtering based in the tag in Cost Explorer reports.

 

Let’s walk through the steps of setting this up:

  1. Log in to your AWS Management Console
  2. Tag all the resources with a Tag Key as Project and Value as per your various projects. Understand that this may not be possible for every resource type.
  3. Navigate to My Billing Dashboard > Cost Allocation Tags
  4. Under User-Defined Cost Allocation Tags section, select the tag “Project” and click the “Activate” button.

 

Fig-1

Once a tag is activated it will take around 24 hours for billing data to appear under this tag.

 

Next, to view the costs under a project, do the following:

  1. Log in to your AWS Management Console
  2. Navigate to My Billing Dashboard > Cost Explorer
  3. Click “Launch Cost Explorer”
  4. On the right side of the page under Filters section, click the Tag filter and select the Project tag, then the Tag Value to filter cost by the Project

2018-01-05_150042

As you can see from the screenshot below, now we can see exactly how much each project is costing per day (or month, if selected)

2018-01-05_145028

Some important points to consider:

  • Cost allocation tagging is “managed” via the master billing account at the root of the AWS organization. If your account is part of an organization you will have to contact this account administrator to enable the cost allocation tags.2018-01-05_145000
  • The error message in the previous screenshot will always appear in tenancies not allocated the management permission.
  • Some resources notably bandwidth charges cannot be tagged and thus cannot be accounted under cost allocation tagging. A common pattern in such cases is to calculate percentage cost on each project and cost the unaccounted charges based on this percentage.

 

 

Checking and patching your Microsoft Windows computer for Meltdown and Spectre

Overview

A Google team named Project Zero in mid 2017 identified vulnerabilities with many Intel, AMD and ARM CPU’s that allow speculative pre-processing of code to be abused. Speculative pre-processing aids performance which is why it exists. However when used maliciously it would allow an attacker to use JavaScript in a webpage to access memory that could contain information present in a users environment such as key strokes, passwords and personal sensitive information.

A very good overview on the how (and a little of the why) is summarised in a series of tweets by Graham Sutherland here.

Mitigation/Patching

In the January Security updates Microsoft have provided updates to protect its operating systems (Windows 7 SP1 and later). More on this below. They have also provided a PowerShell Module to inspect and report on the status of a Windows operating system.

What you are going to need to do is patch your Windows Operating System and update your computers firmware (BIOS).

Using an Administrative PowerShell session on a Windows workstation with Windows Management Framework 5.x installed the following three lines will download and install the PowerShell module, import it and execute it to report on the status.

Install-Module SpeculationControl
Import-Module SpeculationControl
Get-SpeculationControlSettings

The output below shows that the operating system does not contain the updates for the vulnerability.

PowerShell Check.PNG

Obtaining the Windows Security Updates

Microsoft included updates for its operating systems (Windows 7 SP1 and newer) on January 3 2018 in the January update as shown below.  They can be obtained from the Microsoft Security Portal here. Search for CVE-2017-5715 to get the details.

Patch1.PNG

Go to the Microsoft Update Catalog to obtain the update individually.

The quickest and easiest though is to press your Windows Key, select the Gear (settings) icon, Update & Security, Windows Update.

Update & Security.PNG

Check status, install the updates, and restart your Windows computer.

Windows Update.PNG

Speculation Control Status

After installing the updates and restarting the computer we can run the check again. It now shows we are partially protected. Protected for Meltdown but partially protected for Spectre. A BIOS update is required to complete the mitigation for Spectre.

Rerun Powershell Check.PNG

For me I obtained the latest BIOS for my laptop from the manufacturers support website. If you are also on a Lenovo Yoga 910 that is here. However for me the latest Lenovo firmware doesn’t include updates for this vulnerability. And my particular model of laptop isn’t listed as being affected. I’ll keep checking to see if that changes.

Summary

In Microsoft environments your patching strategy will get you most of the way with the Microsoft January Security updates. BIOS updates to your fleet will take additional planning and effort to complete.

 

Cosmos DB Server-Side Programming with TypeScript – Part 1: Introduction

Cosmos DB is a NoSQL database provided as part of Microsoft’s Azure platform. Designed for very high performance and scalability, Cosmos DB is rapidly becoming one of the default data storage options I recommend for new green-field applications and microservices. It is a fairly opinionated database, with some guidelines that you need to follow to take full advantage of its scalability and performance, but it also provides a number of features to enable sophisticated and powerful applications to be built on top of its engine.

One such feature is its server-side programmability model. Cosmos DB allows for stored procedures, triggers, and user-defined functions to run within its database engine. Interestingly, these are written using JavaScript and uploaded to the Cosmos DB collection in which they will run. Server-side programming gives a lot of extra power to a Cosmos DB-based application, including the ability to run transactions across multiple documents within the collection. In fact, server-side programming is the only way to get transaction semantics within Cosmos DB.

In this series of blog posts, we will explore server-side programming in Cosmos DB, and we will use TypeScript to write the server-side code. I will focus on how to build real-world applications, including adding unit tests to ensure the code behaves as expected, and incorporating the build and deployment of Cosmos DB server-side code into your CI/CD process. The series is split into six parts:

  • Part 1 (this post) gives an overview of the server side programmability model, the reasons why you might want to consider server-side code in Cosmos DB, and some key things to watch out for.
  • Part 2 deals with user-defined functions, the simplest type of server-side programming, which allow for adding simple computation to queries.
  • Part 3 talks about stored procedures. These provide a lot of powerful features for creating, modifying, deleting, and querying across documents – including in a transactional way.
  • Part 4 (coming soon) introduces triggers. Triggers come in two types – pre-triggers and post-triggers – and allow for behaviour like validating and modifying documents as they are inserted or updated, and creating secondary effects as a result of changes to documents in a collection.
  • Part 5 (coming soon) discusses unit testing your server-side scripts. Unit testing is a key part of building a production-grade application, and even though some of your code runs inside Cosmos DB, your business logic can still be tested.
  • Finally, part 6 (coming soon) explains how server-side scripts can be built and deployed into a Cosmos DB collection within an automated build and release pipeline, using Microsoft Visual Studio Team Services (VSTS).

In this series I presume some basic knowledge of Cosmos DB. If you’re completely new to Cosmos DB then I recommend reading Microsoft’s overview, and following along with one of the quick starts. A passing familiarity with TypeScript will also be helpful, but even if you don’t know how to use TypeScript, I’ll try to cover the key points you need to know to get started.

Using TypeScript

TypeScript is a language that compiles (or, technically, transpiles) into JavaScript. It provides a number of nice features and improvements over JavaScript, the main one being type safety. This means that the TypeScript compiler can check that your code is accessing the correct types and members as you write it. Writing code in TypeScript allows for a better level of certainty that your code is actually going to work, as well as providing some very nice development-time features such as IntelliSense. It also helps to have strong typing when unit testing, and particularly when mocking out external interfaces and classes within tests.

Because TypeScript compiles into JavaScript code, any JavaScript runtime will be able to run code that had been written in TypeScript. This includes Cosmos DB’s JavaScript engine, Chakra. Even though Cosmos DB doesn’t know about TypeScript or support it directly, we can still take advantage of many of the features that TypeScript provides, and then compile our script into JavaScript before handing it over to Cosmos DB for execution.

TypeScript also lets us separate out our code into multiple .ts files, keeping it tidy and well-organised. Cosmos DB requires that our code be in a single .js file, though – but thankfully, TypeScript can be configured to combine our code when it compiles it.

When working with external libraries and APIs within TypeScript, we need to use type definitions. These specify the details of the types we will use. While the Cosmos DB team doesn’t provide first-party type definitions for their server-side API, there are publicly accessible, open-source type definitions available from the DefinitelyTyped repository. We will use these later in this series.

Note that Cosmos DB supports the ECMAScript 2015 version of JavaScript. TypeScript can be configured to emit JavaScript in several different versions, including ECMAScript 2015 code, so this is not a problem for us. We’ll see how to do this in part 2 of this series.

Impact of Server-Side Programming on Request Units

When using Cosmos DB, we don’t provision CPU cores or disk speed or memory. Instead, Cosmos DB uses request units as its currency. A Cosmos DB collection is provisioned with a certain number of request units per second (RU/s), which can be scaled as necessary to cope with your application’s demands. The RU/s provisioned dictates the monetary cost of running the collection. For example, a simple collection with a light query load might be provisioned with 1000 RU/s, which (as of January 2018) costs approximately USD$60 per month. For more information on Cosmos DB’s request unit model see here, and for the latest pricing information, see here.

Server-side code running within Cosmos DB can easily consume a lot of request units, potentially exhausting your allowance for that second and forcing your application to have to retry operations against Cosmos DB. Furthermore, the cost of running queries server-side may sometimes be higher than the cost of running the equivalent query using the standard client-side APIs, due to the resources it takes to start up a stored procedure or function from JavaScript. Cosmos DB does have some optimisations to reduce the cost of running JavaScript code – for example, internally Cosmos DB compiles the JavaScript code to bytecode and then caches this bytecode so that it doesn’t need to recompile it on every invocation. However, running arbitrary code will usually be more expensive than just using the client-side query APIs, and this means that server-side code may not be appropriate if you don’t actually need the benefits it provides.

Additionally, the request unit usage for a given piece of server-side code is not fully predictable or consistent – in my own testing, I’ve seen the exact same piece of code, working on the same data set, take anywhere from 3.2 RUs through to 4.8 RUs to execute. This is in contrast to the rest of Cosmos DB, where request unit usage is very predictable.

Nevertheless for some scenarios, such as bulk inserts of multiple documents, or generating sample data, it may take fewer request units to run code server-side than client side. It is important to benchmark your code and compare the possible approaches to fully understand the best option for your requirements.

Consistency: Transactions and Indexes

Cosmos DB’s client-side programming model does not provide for transactional consistency across multiple documents. For example, you may have two documents to insert or update, and require that either both operations succeed or – if there is a problem with writing one of them – that both of them should fail. The Cosmos DB server-side programmability model allows for this behaviour to be implemented, because all server-side code runs within an implicit transaction. This means that we get ACID transaction semantics automatically whenever we execute a stored procedure or trigger. Cosmos DB runs stored procedures and triggers on the primary replica that is used to host the data, which allows it to give this level of transactional isolation while still allowing for high performance operations within the transaction.

Note, however, that transactions are not serialised. This means that other transactions may be happening simultaneously on other documents within the collection in parallel with your transaction. This is important because it means that functionality like real-time aggregation of data may not always be looking at a consistent view of the world, and you can get race conditions. This is simply due to the way Cosmos DB works, and is not something that we can easily program around.

A further nuance to be aware of is that, in Cosmos DB, indexes are updated asynchronously. This means that if you query the collection within a trigger, you may not see the document currently being inserted or updated. Again, this makes it challenging to do certain types of queries (such as aggregations), but is a byproduct of Cosmos DB’s emphasis on enabling high performance and throughput.

API Models

Cosmos DB provides several API models to access data in your databases: SQL to use a SQL-based syntax; MongoDB for using the MongoDB client libraries and tools; Table to use the Azure Storage table API; and Gremlin to use the Gremlin graph protocol. All of these ultimately store data in the same way though, and all of them allow for Cosmos DB’s server-side programmability model.

In this series I will focus purely on the SQL API, but most of the same concepts can be applied to the other API models.

Restrictions

Cosmos DB has placed some restrictions on the types of operations that can be run from within the server. This is mostly to optimise the performance and security of the service.

Time restriction: each server-side operation has a fixed amount of time that it must execute within. The exact amount of time is not documented, but the server-side API provides some features to indicate when your script is approaching its limit. We will discuss this more in later parts of the series. It is important to build in this restriction when designing your stored procedures and triggers, and to avoid writing server-side code that will make high volumes of queries. Instead, if you batch these up across multiple stored procedure calls, you are more likely to have all of your code execute successfully.

Limited set of functionality: although it executes arbitrary JavaScript, Cosmos DB’s server-side programming model is designed for basic computation and for interacting with the Cosmos DB collection itself. We cannot call external web APIs, communicate with other collections, or import any complex JavaScript libraries.

Limited fluent query capability: Cosmos DB’s server-side API has a fluent JavaScript-based query syntax to perform various types of queries, filters, projections, etc on the underlying collection. However, this API does not support all of the rich functionality that the SQL grammar provides, nor does it allow for the same types of queries as the other API models, such as Gremlin’s graph query capability.

One example feature that is missing from the server-side query API is aggregation. The Cosmos DB SQL dialect allows for queries such as SUM, MIN, MAX, and COUNT. These cannot be performed using the fluent server-side query API. However, SQL queries can be executed from within server-side code, so this is not a serious limitation and really just affects the way the code is written, not the functionality that is exposed.

Single JavaScript file: a single stored procedure, trigger, or user-defined function is represented by a JavaScript function, which in turn may call other functions. However, all of the code must be placed in a single file. JavaScript modules and other similar features are not supported. We will see how to split our functionality into multiple TypeScript files, while still emitting a single JavaScript file, later in this series.

Following Along

In this series, you will be able to follow along and create each type of server-side programming entity in Cosmos DB: a user-defined function, a stored procedure, and a trigger. We will build up a set of server-side code, and then in parts 5 and 6 of this series we will look at how to get these ready for a production deployment by testing and automatically building and deploying them to Cosmos DB.

You can follow along whether you use Windows, macOS, or Linux to develop. There are just a few prerequisites:

  • A good text editor: I use Visual Studio Code, which comes with the TypeScript programming extension, but you can use anything you like.
  • Node Package Manager (NPM): you can install this here if you don’t already have it.
  • An Azure subscription. Alternatively, you can use the Cosmos DB emulator to run this locally and at no charge, but you will need to adapt the instructions slightly.

Sample Scenario

A common use for a database is to store information about orders that customers make for products. Orders typically contain some basic overall information, such as an order ID, date, customer ID, and a set of order items – references to products and the quantities ordered. In this series we will work with a simple hypothetical order database implemented in Cosmos DB.

We will use the SQL API, and we will use a non-partitioned collection. Note that partitioned collections behave the same way as non-partitioned collections when it comes to server-side programmability, but they also have a few nuances in their behaviour that we won’t go through here.

Summary

Server-side programming in Cosmos DB is extremely powerful. It gives us the ability to write functions, stored procedures, and triggers that execute within the database engine, and allow for features that are simply not possible through the client-side programming model. However, there are limitations and things to be aware of, including the potentially high cost of running some types of operations from the server. The features also do not provide the same degree of flexibility and power as their counterparts in SQL Server and other relational databases. Nevertheless, the server-side programming model in Cosmos DB is enormously useful for certain types of situations.

By using TypeScript to add type safety, and by adding unit tests and good continuous integration and continuous deployment practices, we can build advanced behaviour into our production-grade applications – all while taking advantage of the high performance and scale capabilities of Cosmos DB.

In the next part of this blog series, we will start writing some server-side code – first by building a user-defined function.