If your Opalis database is filled up with log entries the console performance will be affected. Navigating between folders in the console, viewing workflows or console “hangs” in general can be a result of to much log data in the database.By default the log purging feature is not enable in Opalis. The result of that is that no historical data is ever removed from the Opalis database, unless it is manually configured and then either executed by clicking on the “Purge Now” button, or scheduled by selecting the “Schedule Log Purge”. In this post I will share some common practices around log purging and database maintenance for the Opalis database. The Log Purge Configuration dialog box can be accessed from the Opalis Integration Server Client by right-click and selecting Log Purge on server name in the navigation pane.

The purge feature will purge data from following three tables in the Opalis database

• Policyinstances
• Objectinstances
• Objectinstancedata

When working with the Opalis Integration Server Client you can review historical data in a couple of places

• The log tab, show you running policy instances
• The log history tab, will show you information on policy instances that have completed
• The Audit History tab will show changes made to a policy grouped by each time you click “check—in”
• The Events tab will show you events from Opalis, for example if you use the Send Platform Event activity within a policy

Both the Log tab and the Log History tab show you data from these three tables.

Both views, Log and Log History, show selected data from the POLCIINSTANCES table. When you click on a log entry, data for the objects in that policy instance are shown from the OBJECTINSTANCES table. When selecting an object, data for that object is read from the OBJECTINSTANCEDATA table.

The following query can be used to list Policy ID, Policy Name, SeqNumber, Action Server and Number of Instances. The Number of Instances is the indicator how often the Action Server executed the policy since the last log purge. This query is very helpful to see what policy is filling up your tables with historical data.
SELECT
[p].[UniqueID] AS [Policy ID],
[p].[Name] AS [Policy Name],
[prq].[SeqNumber],
[a].[Computer] AS [Action Server],
COUNT([pi].[UniqueID]) AS [Number Of Instances]
FROM [POLICY_REQUEST_HISTORY] AS prq
INNER JOIN [POLICIES] AS p ON [prq].[PolicyID] = [p].[UniqueID]
INNER JOIN [POLICYINSTANCES] AS [pi] ON [prq].[SeqNumber] = [pi].[SeqNumber]
INNER JOIN [ACTIONSERVERS] AS a ON [pi].[ActionServer] = [a].[UniqueID]
WHERE
[prq].[Active] = 1 AND
[pi].[TimeEnded] IS NOT NULL
GROUP BY
[p].[UniqueID],
[p].[Name],
[prq].[SeqNumber],
[a].[Computer]
ORDER BY
[Number Of Instances] DESC

Regarding backup of the Opalis database I have seen a couple of different solutions. The first solution is to export all policies and global settings. This will give you a simple way to restore, re-import the export files. You can do a quick re-install of the Opalis environment and import the files in case of a disaster. You can include the export part in your change management process; before you import a new policy into Opalis you also make a backup copy.

The second solution is to do a backup of the database; an advantage with this solution is that you keep all the historical data. But if you restore the database from a backup when there were policies running, you can run into some problem with state data. But you could execute the sp_StopAllRequests stored procedure to clear out any active requests and instances that aren’t desired. The restore process gets a little bit more complicated with the second solution.

With the first backup solution I recommend you, and that is a general recommendation, to keep state data/historical data in an external data store. For example we could use Service Manager to track what is happening. Opalis executes and Service Manager remembers. As soon as Opalis is done, it will drop everything, it only keeps it in memory during execution (and maybe some logs). Working with Service Manager will give us a great way to track everything. It could of course be any system that we can integrate with, for example an extra SQL database.

Thanks to my colleague Robert Riedmaier for sharing his deep knowledge around Opalis log purge, backup and database maintenance. Thanks Robert!

Every time I need to check something around the Exchange management pack I realize there there is not much mail traffic in my sandbox. The main reason is of course because there are no users. The result is that there is not much to look at with the management pack. I have solved this with a number of Opalis workflows. In this blogpost I will show you the solution

The Exchange Demo policy is actually six policies,

1. 3.1 Internal – Create folder and file. Creates a temporary folder to store the address file in.
2. 3.2 Generate address list. Export e-mail addresses from Active directory to a address file in the folder created by 3.1.
3. 3.3 Generate TO and FROM addresses. Random pick a FROM and TO address from the address file
4. 3.4 Send Internal Mail. The “main” policy that trigger the others, based on a interval
5. 3.5 Generate subject. Generate a random subject
6. 3.6 Multiple Internal mails. Used to send a fixed number of e-mails

The solution starts with 3.4 – Send Internal Mail.

This policy run based on a interval, think in my sandbox it is every 30 seconds. It first trigger policy 3.3 and then 3.5. It will wait until each of the policies are done, before it sends a e-mail and writes to a logfile. The e-mail that is sent is based on the data that policy 3.5 and 3.3 sends back on the data bus. Lets look at policy 3.2 and 3.1 first.

Policy 3.2 will first check if a temporary file exists, this file path is set by a variable. If the file exists it is deleted. The policy then list all enable users from the Active Directory, and writes each e-mail address to a temporary file. The file is created in a folder that policy 3.1 generate. These two policies only needs to be run once, or if you want to generate a new address list from your Active Directory. The address file is named CCemails.txt.

Lets move on to policy 3.3. This policy start with get number of lines in the address file that policy 3.2 generated. Depending on number of lines it will go one of two different directions from the “Compare Number of Lines” activity.  The yellow part is just a reminder that I have only built the “less then 99 lines” path, so if you have more then 99 lines in your address file, make sure you build that path of the policy too. It should look the same as the one I have built, just it should generate random numbers with three digits instead of two. It is actually not really about 99, more about number of characters in the number. The result is that the first break point is 99 and next 999. Number of characters is important in the next activity because the “Generate random text” activity can generate a random number based on number of digits, not between 0 and 25, instead that will be 0 and 99 or 0 and 999.

Next we will fin these four activities. The purpose of those is to get a FROM address and a TO address from the address file generated in policy 3.2. We can use the “Generate Random Text” activity to create a number with 2 digits, but what if we only have 20 lines in the address file? That is why we also use a Compare Values activity, to see it the random number generated is equals to a line in the address text file.

1. Will generate a two character long random number
2. Will see if the number from step 1 is equal to a line in the address file, if it is it will continue to 3, else it will loop and go back to 1 again to generate a new number.
3. Will generate a two character long random number
4. Will see if the number from step 3 is equal to a line in the address file, if it is it will continue else it will loop back to 3 again to generate a new number

The last three activities will read the e-mail address used as FROM address and the line use as TO address from the address file. It will then publish the data to the data bus, and back to policy 3.4. Policy 3.4 generate two random numbers, and then maps them to a e-mail subject and a e-mail abbreviation. The result of this is then published on the data bus and used by policy 3.4

Policy 3.4 will then send a e-mail based on the SUBJECT, TO and FROM information that has been generated. It will also write to a logfile. As you can see I start policy 3.4 every 30 seconds. That is a interval you should adapt to your own environment. These policies use a number of variables that also need to be adapted to the current environment

There is one more policy, “3.6 Multiple Internal Mails”. This policy is used when you want to send a fixed number of e-mails, not based on time interval. You can configure the number of mails to send with a counter and the policy will then loop until the number is reached, for example if you want to send 150 random e-mails. The policy use the same policies to generate FROM, TO and SUBJECT as policy 3.4 do.

After some time you will see nice demo data in Operations Manager. Both performance views and reports will have data in them. I once forgot about the policy a couple of days, and the Exchange management pack started generate alerts about mail queues, mailbox stores out of space and a lot of other interesting things 🙂 The following picture is showing the Exchange message tracking tool, showing all the e-mails Opalis is sending.

You can download my policies here, ExchangeDemoMails_v2, please note that this is provided “as is” with no warranties at all. Also please read this blog post about export and import of policies.