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.

Author: Jeroen W.

blogger

blogger

Curious to know more about this topic?

Working at i8c

i8c is a system integrator that strives for an informal atmosphere between its employees, who have an average age of approx 30 years old. We invest a lot of effort in the professional development of each individual, through a direct connection between the consultants and the management (no multiple layers of middle management). We are based in Kontich, near Antwerp, but our customers are mainly located in the triangle Ghent-Antwerp-Brussels and belong to the top 500 companies in Belgium (Securex, Electrabel, UCB, etc…).

Quality Assurance

i8c is committed to delivering quality services and providing customer satisfaction. That’s why we invested in the introduction of a Quality Management System, which resulted in our ISO9001:2000 certification. This guarantees that we will meet your expectations, as a reliable, efficient and mature partner for your SOA & integration projects.

i8c - ISO9001-2015

Also worth reading

AWS AppFlow: Streamlining SaaS Integrations with AWS Services

In today’s digital world, organizations are constantly looking for ways to streamline their workflows and improve their data management processes. One of the key challenges that organizations face is integrating their various software as a service (SaaS) applications with their data management systems. This is

Read More »

Apigee Scope Validation using OpenAPI Specification

In API security and management, we often use a lot of different security mechanisms to protect the requested resource behind the API Gateway. One of these mechanisms is the validation of scopes to authorize a client on a specific sub-resource of the API. Most of

Read More »