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.
