System Center Orchestrator 2012 : Data Manipulation

In some of your Runbooks, you will have to read text files, csv files, SQL results, and you have to extract, to manipulate these data for using it into other activities.

Orchestrator 2012 enables you to manipulate string data from text files, Published Data items, or other sources, and convert it into a usable form. You can also perform arithmetic operations, such as calculating sums and differences, and performing division and multiplication operations. For example, you can extract text from a text file using a text file management object, trim leading and trailing spaces from the text, and then retrieve specific parts of the text that you can pass to other objects as Published Data items.

I will demonstrate all that by using the following activities :
image

The Initialize Data activity is just use here as the start command of the Runbook, no need of any configuration.
image

The Read Line activity will return the raw data that we want to manipulate. We simple read the first line of a text file.
image

The Send Platform Event activity will be use for manipulating the data and showing the result.
image

For using the Orchestrator embedded data manipulations functions, we have to enclosed in square brackets (‘[‘ and ‘]’). Keep also in mind that the functions are case-sensitive.

Upper/Lower – converts text functions

The contain of our text file :
image

In the details part of the Send Platform Event properties, we call the use the Upper function into brackets :
image

Now just run the Activity and check the result in the Events pane:
image

Our text is now well completely in Upper case. Of course the otherwise function, Lower(‘Text’), also exist.

Field – returns text in a specific position

One other useful function is the Field function, this allow you to extract a part of a line base on a delimiter.

Field(‘Text’, ‘Delimiter’, Field Number)

Our text file is now compose of information about users, separate by a comma, you could imagine a csv file that contains the list of your collaborators :
image

In the Send Platform Event, we use the Field function for extracting the information from the line that we just read in the previous activity :
image

Just have to Run the Runbook and here the result :
image

Nested Functions

You have also the capability to use data manipulation function within another function, here a example :

Upper(Field(‘Text’, ‘Delimiter’, Field Number))

We keep the same text file than the previous case and we just want to have the email address in Upper case :
image

We just have to nested the Upper function with the Field function, like below :
image

We could now run our Runbook and here the expected result, the email address is well in upper case :
image

I hope this help Smile This Runbook is available for download here : DONWLOAD
Below you have a description of all the available functions and the System Center Orchestrator 2012 Resources.

All the available functions

Function and Definition
Usage Parameters Example
Upper – converts text to upper case. Upper(‘Text’) Text – the text that is being converted to upper case. Upper(‘this will be converted to uppercase’) returns ‘THIS WILL BE CONVERTED TO UPPERCASE’
Lower – converts text to lower case Lower(‘Text’) Text – the text that is being converted to lower case. Lower(‘This Will Be Converted To Lowercase’) returns ‘this will be converted to lowercase’
Field – returns text in a specific position Field(‘Text’, ‘Delimiter’, Field Number) Text – the text that is being searched Field(‘John;Smith;9055552211’, ‘;’, 2) returns ‘Smith’.
Delimiter – the character that separates each field
Field Number – the position of the field that is being returned (starting at 1)
Sum – returns the sum of a set of numbers Sum(firstNumber, secondNumber, thirdNumber, …) Number – the number being added, you can put any set of numbers each separated by a comma (,). Sum(2,3,4,5) returns ’14’
Diff – returns the difference of two numbers Diff(Number1, Number2, <Precision>) Number1 – the number that will be subtracted from Diff(9, 7) returns ‘2’
Number2 – the number that will be subtracted from Number1 Diff(9.3, 2.1, 2) returns ‘7.20’
Precision <Optional> – the number of decimal places that the result will be rounded to  
Mult – returns the product of a set of numbers Mult(firstNumber, secondNumber, thirdNumber, …) Number – the number being multiplied, you can put any set of numbers each separated by a comma (,). Mult(2, 3, 4) returns ’24’
Div – returns the quotient of two numbers Div(Number1, Number2, <Precision>) Number1 – the number that will be divided Div(8, 4) returns ‘2’
Number2 – the number that will divide Number1 Div(9, 2, 2) returns ‘4.50’
Precision <Optional> – the number of decimal places that the result will be rounded to  
Instr – returns the position of first occurrence of text within another text Instr (‘SearchText’, ‘TextToFind’) SearchText – the text that is being searched Instr(‘This is a string that is searched’, ‘string’) returns 11
TextToFind – the text that you are searching for
Right – returns a subset of the text from the right side of the full text Right(‘Text’, Length) Text – the full text Right(‘Take from the right’, 9) returns ‘the right’
Length – the number of characters from the right side that will be returned
Left – returns a subset of the text from the left side of the full text Left(‘Text’, Length) Text – the full text Left(‘Take from the left’, 4) returns ‘Take’
Length – the number of characters from the left side that will be returned
Mid – returns a subset of the text from the middle of the full text Mid(‘Text’, Start, Length) Text – the full text Mid(‘Take from the middle’, 5, 4) returns ‘from’
Start – the starting position in the text where you want to begin returning characters
Length – the number of characters starting from the Start position that will be returned
LTrim – Trims leading spaces from text LTrim(‘Text’) Text – the text being trimmed of leading spaces LTrim(‘ Remove the leading spaces only. ‘) returns ‘Remove the leading spaces only. ‘
RTrim – Trims the trailing spaces from text RTrim(‘Text’) Text – the text being trimmed of trailing spaces RTrim(‘ Remove the trailing spaces only. ‘) returns ‘ Remove the trailing spaces only.’
Trim – Trims leading and trailing spaces from text Trim(‘Text’) Text – the text being trimmed Trim(‘ Remove leading and trailing spaces. ‘) returns ‘Remove leading and trailing spaces.’
Len – returns the length of text Len(‘Text’) Text – the text that is being measured Len(‘Measure this text’) returns 17

Microsoft System Center Orchestrator 2012 Resources :
Microsoft System Center Orchestrator 2012 beta is available as public beta at the Microsoft Download Center : http://www.microsoft.com/download/en/details.aspx?id=26503

Microsoft System Center Orchestrator 2012 Team Blog : http://blogs.technet.com/b/scorch/

TechNet Forums for System Center Cross Platform and Interoperability :http://social.technet.microsoft.com/Forums/en-US/category/scinterop

TechNet Library – Opalis 6.3 Data Manipulation : http://technet.microsoft.com/en-us/library/gg440683.aspx

Cheers
Christopher KEYAERT
http://twitter.com/keyaertc

Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Email this to someoneShare on TumblrPin on PinterestDigg thisShare on RedditFlattr the authorBuffer this pageShare on StumbleUpon

About Christopher Keyaert

Christopher Keyaert is a Consultant, focused on helping partners to leverage the System Center and Microsoft Azure cloud platform. He is also a Microsoft Most Valuable Professional (MVP) for Cloud and Data Center Management and a Microsoft Certified Trainer (MCT).
This entry was posted in Uncategorized. Bookmark the permalink.

0 Responses to System Center Orchestrator 2012 : Data Manipulation

  1. Alex Khassanov says:

    Hi chris,

    nice article. One more thing to add about nested functions (as per http://technet.microsoft.com/en-us/library/hh440537.aspx):
    “…Nested Functions

    If you want to use a data manipulation function within another function, you do not have to enclose the nested function in square brackets. For example, to nest the Field function, type:

    [Field(Field(‘username=jsmith@abcompany.com’,’=’,2),’@’,1)]
    …”

    Thanks!
    AK

Leave a Reply

Your email address will not be published. Required fields are marked *