A Question was posed on the Opalis forums that I thought I would take a quick second to answer as it is one that I struggled with myself initially
I’m running some SQL query. I get few line results …
There is a way that I could use these results as “for each” for the next object in the workflow ?
for example if I got 5 line results of computer names and I want to get the Computer Status for each result… it can be done ?
The answer to this actual question is rather easy. Because the object (Query Database) is set to spawn a new policy instance for each returned result you simply follow your query database with a get computer status object and subscribe to its output.
But what if it didn’t? What if instead is just returned that had each field separated by a ; and each record separated by a newline character? We can simulate this by changing the Query Database’s run behavior and telling it to ‘flatten.’ Flatten simply means do not spawn separate policy instances for each returned result, instead separate each result with something (for this example we will use the newline character).
Now when I run the policy I get a failure in the “Get Computer Status” object (expected because the input string now is a mashup of all four of my action servers)
So, what we want to be able to do is take a string input, split it apart based on some criteria and then spawn an individual instance of the policy for each substring that was returned. If we split on the newline character in the example above we would get an individual instance for each server returned. One of the Opalis guys made an integration pack to do just this sort of thing, it is called the ‘Data Manipulation’ integration pack http://opalis.codeplex.com/releases/view/46827. It has an ‘expand fields’ object that, if I could figure out how to form the input, should do the trick for this.
The problem here is that the object does not work with that as the newline character. As they have not released the source code for how this object actually works I stopped trying to make it work for this purpose (splitting on the newline character, it works wonderfully for splitting on say, the ; character) and made my own. It is available in the Opalis Utilities Integration pack (http://opalisutilities.codeplex.com/) and is called ‘Split Lines’. So, you now modify the previous workflow and add the ‘split lines’ object between the ‘Query Database’ object and the ‘Get Computer Status’ object. Set the ‘Split Lines’ object to take the output of the Query Database Object as input and the ‘Get Computer Status’ to take the output of the split lines object as input.
If you then want to return to a ‘single thread’ for you policy you can put a ‘Junction’ object from workflow control after the Get Computer/IP Status object, otherwise the whole policy will continue on being multithreaded which may cause some un-intended results.