Thursday, March 22, 2012

Daily or Weekly statistics on what has been processed in webMethods


This arctivle will describe how we can get some daily or weekly statistics from the database (MsSql or Oracle) of webMethods.
Let us start with the Dynamic SQL Adapters. These are needed to run a query which will return us the statistics of a specific period.

Input


 Output

 Query to enter in the Dynamic Adapter.

Oracle
SELECT a.servicename,
sum(decode(a.status,1,1,0)) as TotalStarted,
sum(decode(a.status,2,1,0)) as SuccessCount,
sum(decode(a.status,4,1,0)) as OutstandingFailures,
sum(decode(a.status,32768,1,0)) as Resubmitted,
round(avg(a.duration)/1000,3) as AvgRunTime,
max(a.duration)/1000 as MaxRunTime ,
min(a.duration)/1000 as MinRunTime ,
         round(AVG(dbms_lob.getlength(a.pipeline)),2) as AvgPipeSize
    FROM wmiscoreaudit.wmservice a
    where a.audittimestamp > ?
    and a.audittimestamp < ?
    and a.status in (1, 2, 4, 32768)
  group by a.servicename

SQL
SELECT a.servicename,
sum(cast(CASE WHEN a.status = 1 THEN 1  ELSE 0 END as bigint)) as TotalStarted,
sum(cast(CASE WHEN a.status = 2 THEN 1  ELSE 0 END as bigint)) as SuccessCount,
sum(cast(CASE WHEN a.status = 4 THEN 1  ELSE 0 END as bigint)) as OutstandingFailures,
sum(cast(CASE WHEN a.status = 32768 THEN 1  ELSE 0 END as bigint)) as Resubmitted,
round(avg(a.duration)/1000,3) as AvgRunTime,
max(a.duration)/1000 as MaxRunTime ,
min(a.duration)/1000 as MinRunTime ,
round(AVG(cast(DATALENGTH(a.pipeline)as bigint)),2) as AvgPipeSize
    FROM wmiscoreaudit.dbo.wmservice a
    where a.audittimestamp > ?
    and a.audittimestamp < ?
    and a.status in (1, 2, 4, 32768)
    group by a.servicename

 
Output can be formatted as HTML with following XSLT service.

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" encoding="UTF-8"/>
<xsl:template match="/">
<xsl:param name="reportheader"/>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
<STYLE type="text/css">

<!—some CSS styles à
</STYLE>

<title>Webmethods System Status Report</title>
</head>
<body>
<i class="checkstyle-data">(This Email is formatted to be viewed in a Html enabled Email Client)</i>
<br></br>
<br></br>
<br></br>
<b class="checkstyle-sectionheader"><u>Webmethods Service Runtime Stats</u></b>
<br></br>
<br></br>

<i class="compile-data"><b>Note: The day/week of the data in this report is in GMT timezone</b></i><br></br>
<br></br>
<i class="compile-data"><b>Started</b> - Total number of times the Service started. Partial data from current day is not included.</i><br></br>
<i class="compile-data"><b>Completed</b> - Total number of times the Service completed successfully. Partial data from current day is not included.</i><br></br>
<i class="compile-data"><b>Failed</b> - Total number of times the Service failed due to an error. Partial data from current day is not included.</i><br></br>
<i class="compile-data"><b>Resubmitted</b> - Total number of times the Service was resubmitted. This will subsequently result in addition to above counts. Partial data from current day is not included.</i><br></br>
<table>
  <TBODY>
                                <TR class="compile-sectionheader" colSpan="200">
                                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>ServiceName</B></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>Started</B></TH>
                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>Completed</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>Failed</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>Resubmitted</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>AvgRuntime</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>AvgPipelineSize</B></TH>
                  
                                </TR>
                <xsl:for-each select="Statistics/wmServiceStats">
                                <xsl:choose>
                                <xsl:when test="position() mod 25 = 0">
                                <TR class="compile-sectionheader" colSpan="200">
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>ServiceName</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>Started</B></TH>
                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>Completed</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>Failed</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>Resubmitted</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>AvgRuntime</B></TH>
                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><B>AvgPipelineSize</B></TH>
                               
                                </TR>
                                </xsl:when>
                                </xsl:choose>
                                <xsl:choose>
                                <xsl:when test="position() mod 2 = 1">
                                                <TR class="checkstyle-oddrow" colSpan="200">
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="ServiceName" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="TotalStarted" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="SuccessCount" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="Failed" /></TH>            
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="Resubmitted" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="AvgRunTime" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="AvgPipeSize" /></TH>
                                               
                                                </TR>
                                </xsl:when>
                                <xsl:otherwise>
                                                <TR class="checkstyle-evenrow" colSpan="200">
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="ServiceName" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="TotalStarted" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="SuccessCount" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="Failed" /></TH>            
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="Resubmitted" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="AvgRunTime" /></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><xsl:value-of select="AvgPipeSize" /></TH>
                                               
                                                </TR>
                                </xsl:otherwise>
                                </xsl:choose>
                </xsl:for-each>
                                 <TR class="compile-sectionfooter" colSpan="200">
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><b>Grand Total</b></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><b><xsl:value-of select="sum(Statistics/wmServiceStats/TotalStarted)" /></b></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><b><xsl:value-of select="sum(Statistics/wmServiceStats/SuccessCount)" /></b></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><b><xsl:value-of select="sum(Statistics/wmServiceStats/Failed)" /></b></TH>
                                    <TH ALIGN="LEFT" NOWRAP="NOWRAP"><b><xsl:value-of select="sum(Statistics/wmServiceStats/Resubmitted)" /></b></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><b>N/A</b></TH>
                                                <TH ALIGN="LEFT" NOWRAP="NOWRAP"><b>N/A</b></TH>
                                 </TR>
  </TBODY>
</table>
<br></br>
<hr></hr>
<hr></hr>
<br></br>

<br></br>
<i class="checkstyle-data">Daily/Weekly System Runtime Reports will automatically be generated from WebMethods Environment.</i><br></br>
<i class="checkstyle-data">Please contact <b>the webMethods Administrator</b> for any questions or concerns about the report.</i><br></br>
</body>
</html>
</xsl:template>
</xsl:stylesheet>

Now once this is done a service can be created to enter a number of days (e.g.: 1 for daily reports, 7 for a week). You can take the current day and set is as the toTime input parameter of the query and for the fromTime you can subtract the number of days from today’s date.
Format of those parameters should be “yyyy-MM-dd 00:00:00”.

E.g.  for daily statistics: now = 2012-03-08 13:48:35
toTime will be 2012-03-08 00:00:00
fromTime will be 2012-03-07 00:00:00
Which will generate the statistics results for everything processed yesterday

Once this is done and you can execute the adapters we created above and with that result you can generate the html(after conversion to XML)and send it out via mail if you want.

This has been tested on webMethods v8.

Authoer : Jeroen W.

Wednesday, March 14, 2012

Software AG Roadmap status update Q1 2012

Me and my colleagues attended the Software AG (SAG) Partner Update event in Brussels a couple of weeks ago and learned about some interesting new tools and features that are coming up. In this blog post I'll try to give a short overview of what was announced.

Let me start with the most surprising announcement and one that has been high on my personal wish list since a long time already: SAG will complete its middleware portfolio with an application platform! The existing webMethods middleware platform has mainly been focused on integration and orchestration of existing business logic until now and didn't contain a component for development and deployment of new business logic. This will change in the future, but exact details on what the application platform will look like were not revealed. I picked up some rumors that the solution will probably exist out of a number of open source components such as Tomcat and Spring, but more details will hopefully be published soon.

Another interesting and hot topic these days is mobile and SAG also has plans in that domain. In version 2.2 of MashZone, dashboards will be available on mobile devices such as iPad, Android,...  The mobile app development platform from Metismo, which was acquired by SAG last year, will be rebranded to webMethods Mobile Designer and will be expanded with new functionality such as Windows Mobile support, updated widget libraries, ...

Cloud can not be missing in any IT-talk these days, so besides the continued integration of the Terracotta distributed cache solution in the whole SAG platform, a new offering was announced called webMethods Cloudstreams. This solution is intended to solve SaaS application integration challenges, such has handling the complex to use SaaS APIs, abstract SaaS providers, provide greater visibility into cloud services, ... Looks like this solution will extend the existing webMethods Mediator solution by providing SaaS service governance, centralized security, SLA enforcement, monitoring dashboards and service mediation interfaces for the cloud.



For existing webMethods customers, SAG is investing in webMethods Command Central, a solution that will lower the TCO of a webMethods landscape from an operational perspective by providing a centralized management solution that allows to administer multiple instances simultaneously, improves configuration, fix/SP installation, provisioning,...

Looks like we'll have plenty of interesting new stuff to play around with the next months and years :-)

Author: Kristof Lievens

Monday, March 12, 2012

Concept : suspending triggers automatically when target system is down

With this question we came up with a concept to automatically detect if there is one of the target systems down and then suspend the triggers used for this connection. 
Once the system detects that this target system is back online, we will enable the triggers again and the normal process can continue.

 Why did we start this concept? 
We’ve seen that we cannot guarantee the 24/7 uptime of the target systems and we wanted to have some dynamic tool which will take care of these downtimes (scheduled or not) so we shouldn’t have to worry about any failed transactions and resubmit them. This in some cases can be a hard task if there are thousand failed messages.

Concept  requirements:

1. Database

We need to identify the relationship between the trigger and the connections. Why? One connection can be used in different packages and also one package can have multiple triggers pointing to different target systems.
We will store this data in a database which can be easily implemented and cached as well.
  • Info required for the database
  • Full path name of the trigger
  • Package name of the trigger
  • Type of connection (e.g. : JDBC, SAP,…)
  • Full path name of the connection
  • identifier to make it unique if we have multiple target systems for 1 trigger. So we can identify all triggers to one target system.
2. Monitoring Job

We also need to have a job running every x time to check if all connections are still active and responding. We will create a dummy query per type of connection so we can see if the target system is still responding, if not there is an issue and we should not send any data towards that IZ.
Of course we also need to build in some security checks like, what if there was a network blips why the dummy query didn’t ran successfully. So we will have to execute this a 2nd time to make sure that there is definitely an issue, but we have to delay this request with some time (e.g. one minute) to know there are no one time issues.
Let’s say we’ve detected a target system which is down. We now have to start another thread which will suspend the triggers and connection for that target system. Why disabling the connection? It’s not required, but easier to monitor if a connection has been disabled. As in a good environment we won’t have any disabled connections.

3. Recycle Job

This is the job which will check if the target system is back online and responding. In this we can try to enable the connection again. If this doesn’t work that means the target system is still not responsive. If the connection get’s enabled again we can enable the triggers again and resume normal processing. If not, re-execute this job again. The purpose of this job is to check every X time if the connection is back online. Take in mind we will not execute this job every seconds which will be an overkill of the machines. But we can let it wait one minute and then try again.

4. Queue Monitor

If we suspend processing of documents they will remain in the broker, so we will have to monitor the broker so it will not grow to large and even in the worst case the broker can crash.
We can use Optimize for that of develop custom code to check the total size of the broker and alert if it reaches a predefined limit. We could even make it “smart” and release the trigger that is causing the broker to pill up and once the broker is back to a “safe” size, we can see if the target is back online or not, if not we can suspend this trigger again.

Now with this information in mind we can develop some concept. Will keep you posted on the progress and post some code samples as well.

Author : Jeroen W.

Tuesday, March 6, 2012

A first look at Windows Azure Service Bus EAI and EDI Labs

End of last year, Microsoft released a CTP of its Windows Azure Service Bus EAI and EDI solution. Cloud is hot these days and we see more and more cloud based integration solutions appear (SnapLogic, IBM Cast Iron, Dell Boomi,...) or traditional EAI and EDI vendors that release a cloud offering (Tibco Silver, Software AG Cloud Ready,...). Although Microsoft is also a traditional EAI and EDI vendor with its well-known and widespread BizTalk product, the Windows Azure EAI and EDI solution doesn't seem to have anything in common with the existing BizTalk code base. On the contrary, over time Windows Azure Service Bus EAI and EDI will be the future of BizTalk according to this talk. BizTalk will be rearchitected on top of Windows Azure Service Bus EAI and EDI and other Azure components, so how far are we from this new situation?

It's clear that the Windows Azure Service Bus EAI and EDI CTP is functionaly not as complete as BizTalk yet, but I expect some of the missing pieces to be added or completed in the final release. One feature I'm worried about though is logging, tracking and monitoring. This is probably one of the best features in BizTalk, existing out of multiple SQL Server databases (Health and Activity tracking, MessageBox, BAM) that are supplied out-of-the-box. This currently seems to be missing completely in the design of the Windows Azure Service Bus EAI and EDI Labs. The Windows Azure CTP Management Portal portal doesn't provide any monitoring GUI and the SDK doesn't provide an API to log messages for tracking/resubmission or to log errors to allow monitoring. According to this thread on the forum, Windows Azure Service Bus EAI solutions are not yet intended for asynchronous scenarios as supported by BizTalk, but only for synchronous scenarios in which the client is repsonsible to capture and handle an exception in Azure. Nevertheless, even in a synchronous scenario monitoring and tracking are vital, for example when using the XML one-way bridge in the Azure Service Bus EAI and EDI CTP, where no repsonse is sent back to the client which means there is no way to determine if a message was successfully routed to the targetted backend system. The same remark applies for the receive endpoints of EDI agreements. These are implemented as black-boxes and can be used as input for a Service Bus bridge to translate from EDI to XML, but when something goes wrong with the EDI - or XML message in the chain of process steps, there is no way to determine the cause of the problem.

I tried to work around the missing logging, tracking and monitoring functionality by using Azure Service Bus Queues for debugging purposes, but I encountered the same monitoring issue here, even though queues are already available for quite some time. There is no graphical interface available in the Azure portal to have a peek in the Azure Service Bus queues and there don't seem to exist any tools yet that cover this functionality.

This lack of logging, tracking and monitoring functionality is not only painful during development, I also wonder how you will be able to troubleshoot issues in a production environment. It seems that all software vendors forget about the "design for operations" aspect and add this in later releases, so I'm looking forward on how Microsoft will tackle this in the future.

Author: Kristof Lievens