PowerBI从Exchange跟踪日志中分析数据和KPI展现

很多企业都有用Exchange Server来构建自己的企业邮箱，但做邮件统计的时候，自带的Exchange toolboxs总是不能很友好的展示出您想查询或统计的结果，要么就是借助的第三方的反垃圾网关设备来完成邮件的查询和统计，今天我将采用PowerBI Desktop来实现查询，统计和自己检索的功能，仅仅只是利用跟踪日志。

在这里我的环境是Exchange Server 2013，传输的日志默认在传输角色服务器Exchange安装所在盘符的如下位置

C:\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\MessageTracking

但这里只需要“MSGTRK+日期.log”文件

在这些日志文件中每一个字段表示什么含义，可以参考https://technet.microsoft.com/zh-cn/library/bb124375(v=exchg.160).aspx

如果您发现没有这些日志那么需要检查如下位置是否开启跟踪日志

接下来讲利用这些传输日志从多种维度来分析Exchange用户使用的情况，在这里我将MessageTracking目录复制到一个单独的位置，打开PowerBI获取文件夹为数据

在更多选项中选择文件——文件夹

选择文件夹所在位置

点击组合下的合并和编辑

点击确定

点击确定后会弹出一个编辑查询的对话框

勾选视图中的编辑栏

接下来在空白处右键新建查询——空查询，一个名为Var_LogFolder的文件化变量

设置E:\MessageTracking日志存放路径

并将这个查询1重命名为Var_LogFolder

回到MessageTracking表点击源进行设置，将文件获取路径改为空查询设定的变量名Var_LogFolder，这样变量名指向的跟踪日志所在目录位置，这样会自动加载跟踪日志所在目录的全部日志文件

接下来选择第一列，然后点击删除其他列

接下来合并二进制文件

然后双点合并好的二进制文件

接下来把后面的步骤都删除掉（点击红色的X删除）

接下来定义每一列的数据类型为文本

接下来删除最前面4行

输入4，确定

接下来将第一行用作标题

接下来选择第一列，将第一列的数据类型从文本改成日期/时间

接下来还是选择第一列，点击复制列

将复制到最后一列的标题重命名为Date

将total-bytes和recipient-count和Date这3列设置数据类型为整数

接下来将recipient-address设置为计数行

点击recipient-address旁边的符号，选择扩展到新行

选择第一列，执行删除错误

最后关闭并应用

等待加载完成

接下来就可以开始创建可视化视图了

先创建一个切片器，该切片器主要以日期为主

选择列表

在改切片器里开启全部和取消单选（允许多选）

接下来再创建一个堆积条形图，统计邮件总计数，message-id是计数（非重复）

选择为非重复

接下来再创建一个堆积柱形图，统计外发邮件总计

• event-id是邮件事件类型，这个类型选择DELIVER表示邮件已传递到本地邮箱

同样的方法创建一个接收邮件排行榜，source是计数

• source是负责事件的Exchange传输组件，这个类型选择STOREDRIVER表示事件源是来自本地邮箱服务器上提交的MAPI（如果您的单位Exchange绑定了多个邮件域名，那么sender-address这里是或关系）

接下来创建接收件总计大小，total-bytes是求和

• total-bytes：以字节为单位的邮件总大小，包括所有附件；

• event-id是邮件事件类型，这个类型选择DELIVER表示邮件已传递到本地邮箱

接下来创建内部用户发送邮件排行榜，message-id是计数（非重复）(如果您的单位Exchange绑定了多个邮件域名，那么sender-address这里是或关系)

• sender-address：Sender: 头字段中指定的电子邮件地址，如果 Sender: 字段不存在，则为 From: 头字段中指定的电子邮件地址；

• message-id：邮件头中 Message-Id: 头字段的值。如果 Message-Id: 头字段不存在或为空，则 Exchange 为其分配一个任意值。该值在邮件生存期内是常量。对于在 Exchange 中创建的邮件，该值的格式为 <GUID@ServerFQDN>，包括尖括号 (< >)。例如，<4867a3d78a50438bad95c0f6d072fca5@mailbox01.contoso.com>。其他邮件系统可能使用不同的语法或值；

• event-id是邮件事件类型，这个类型选择RECEIVE表示邮件由传输服务的 SMTP 接收组件接收或由分拣或重播目录发送（源：SMTP），或邮件已从邮箱提交至邮箱传输提交服务（源：STOREDRIVER）

接下来创建内部用户发送邮件总数(如果您的单位Exchange绑定了多个邮件域名，那么sender-address这里是或关系)

• event-id是邮件事件类型，这个类型选择RECEIVE表示邮件由传输服务的 SMTP 接收组件接收或由分拣或重播目录发送（源：SMTP），或邮件已从邮箱提交至邮箱传输提交服务（源：STOREDRIVER）

• message-id：邮件头中 Message-Id: 头字段的值。如果 Message-Id: 头字段不存在或为空，则 Exchange 为其分配一个任意值。该值在邮件生存期内是常量。对于在 Exchange 中创建的邮件，该值的格式为 <GUID@ServerFQDN>，包括尖括号 (< >)。例如，<4867a3d78a50438bad95c0f6d072fca5@mailbox01.contoso.com>。其他邮件系统可能使用不同的语法或值；

• sender-address：Sender: 头字段中指定的电子邮件地址，如果 Sender: 字段不存在，则为 From: 头字段中指定的电子邮件地址；

接下来创建外部用户对内发送邮件排行榜(如果您的单位Exchange绑定了多个邮件域名，那么sender-address这里是且关系)

• event-id是邮件事件类型，这个类型选择RECEIVE表示邮件由传输服务的 SMTP 接收组件接收或由分拣或重播目录发送（源：SMTP），或邮件已从邮箱提交至邮箱传输提交服务（源：STOREDRIVER）

• message-id：邮件头中 Message-Id: 头字段的值。如果 Message-Id: 头字段不存在或为空，则 Exchange 为其分配一个任意值。该值在邮件生存期内是常量。对于在 Exchange 中创建的邮件，该值的格式为 <GUID@ServerFQDN>，包括尖括号 (< >)。例如，<4867a3d78a50438bad95c0f6d072fca5@mailbox01.contoso.com>。其他邮件系统可能使用不同的语法或值；

• sender-address：Sender: 头字段中指定的电子邮件地址，如果 Sender: 字段不存在，则为 From: 头字段中指定的电子邮件地址；

接下来再创建一个外部用户对内发送邮件总数(如果您的单位Exchange绑定了多个邮件域名，那么sender-address这里是且关系)

• event-id是邮件事件类型，这个类型选择RECEIVE表示邮件由传输服务的 SMTP 接收组件接收或由分拣或重播目录发送（源：SMTP），或邮件已从邮箱提交至邮箱传输提交服务（源：STOREDRIVER）

• message-id：邮件头中 Message-Id: 头字段的值。如果 Message-Id: 头字段不存在或为空，则 Exchange 为其分配一个任意值。该值在邮件生存期内是常量。对于在 Exchange 中创建的邮件，该值的格式为 <GUID@ServerFQDN>，包括尖括号 (< >)。例如，<4867a3d78a50438bad95c0f6d072fca5@mailbox01.contoso.com>。其他邮件系统可能使用不同的语法或值；

• sender-address：Sender: 头字段中指定的电子邮件地址，如果 Sender: 字段不存在，则为 From: 头字段中指定的电子邮件地址；

那么这个页面要暂时的都基本OK了，接下来我进行下美化和排版

按照这样的方法再创建其他页，例如发送和接受统计

按月统计

查询功能，可以看到自己输入查询关键字来对日志进行检索查询到您想看到的信息

比如我输入一个收件人地址，那么改收件人的所有主题和发送给谁的查询结果就显示在下面了，非常简单

这样定制化的Exchange信息检索统计分析是不是很有用并且可以自己想怎么定制就怎么定制，神器啊~关键是只需要拿到跟踪日志即可分析。