BizTalk获取各应用接收与发送消息计数的查询脚本求助

所有统计基于BizTalk自带的跟踪数据库BizTalkDTADb，执行前需确认环境已开启消息跟踪功能，否则会出现统计数据缺失的情况。

核心逻辑说明 #

BizTalk的应用基础信息存储在管理库BizTalkMgmtDb的bts_application表中，消息收发事件对应跟踪库dta_MessageInOutEvents表的nDirection字段：取值为1代表接收消息，取值为2代表发送消息，通过服务实例ID可关联到消息所属的应用ID，最终关联应用名称实现分组统计。

全时段按应用统计收发总量 #

USE BizTalkDTADb
GO

SELECT 
    app.nvcName AS BizTalk应用名称,
    SUM(CASE WHEN me.nDirection = 1 THEN 1 ELSE 0 END) AS 接收消息总数,
    SUM(CASE WHEN me.nDirection = 2 THEN 1 ELSE 0 END) AS 发送消息总数,
    COUNT(*) AS 收发消息总计
FROM dta_MessageInOutEvents me
JOIN dta_ServiceInstances si ON me.serviceInstanceID = si.serviceInstanceID
JOIN BizTalkMgmtDb.dbo.bts_application app ON si.applicationId = app.nID
-- 如需过滤指定时间范围可取消注释下方语句，替换为实际的时间区间
-- WHERE me.dtTimestamp >= '2024-01-01' AND me.dtTimestamp < '2024-02-01'
GROUP BY app.nvcName
ORDER BY 收发消息总计 DESC

按天+按应用分组统计收发量 #

对应你已实现的按天全量统计需求的扩展版本，可直接输出每天每个应用的收发明细：

USE BizTalkDTADb
GO

SELECT 
    CONVERT(VARCHAR(10), me.dtTimestamp, 23) AS 统计日期,
    app.nvcName AS BizTalk应用名称,
    SUM(CASE WHEN me.nDirection = 1 THEN 1 ELSE 0 END) AS 当日接收消息数,
    SUM(CASE WHEN me.nDirection = 2 THEN 1 ELSE 0 END) AS 当日发送消息数
FROM dta_MessageInOutEvents me
JOIN dta_ServiceInstances si ON me.serviceInstanceID = si.serviceInstanceID
JOIN BizTalkMgmtDb.dbo.bts_application app ON si.applicationId = app.nID
-- 如需统计最近30天数据可取消注释下方语句
-- WHERE me.dtTimestamp >= DATEADD(DAY, -30, GETDATE())
GROUP BY CONVERT(VARCHAR(10), me.dtTimestamp, 23), app.nvcName
ORDER BY 统计日期 DESC, 当日接收消息数 DESC

注意事项 #

• 执行查询的账号需要具备BizTalkDTADb和BizTalkMgmtDb的只读权限
• 若业务量较大，建议限制统计的时间范围，避免查询占用过多数据库资源影响业务运行
• 已被跟踪数据清理作业删除的历史消息不会被纳入统计，统计结果和实际消息量的差值取决于你配置的跟踪数据保留周期

内容的提问来源于stack exchange，提问作者madilgul