<Report ID="Trend.Micro.ScanMail.for.Microsoft.Exchange.Report.Anti.Spam.Report" Accessibility="Public" Visible="true">
<ParameterBlock xmlns="http://schemas.microsoft.com/mom/reporting/2007/ReportParameterSettings" columns="2">
<Controls>
<Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.RelativeDateTimePicker" rowSpan="3">
<ReportParameters>
<ReportParameter name="TimeZone" binding="TimeZone">
<Prompt>Trend.Micro.ScanMail.for.Microsoft.Exchange!Trend.Micro.ScanMail.for.Microsoft.Exchange.Report.ParameterPrompt.TimeZone</Prompt>
</ReportParameter>
<ReportParameter name="TimeZoneName" binding="TimeZoneName"/>
<ReportParameter name="StartDate_BaseType" binding="StartDate_BaseType"/>
<ReportParameter name="StartDate_BaseValue" binding="StartDate_BaseValue">
<Prompt>Trend.Micro.ScanMail.for.Microsoft.Exchange!Trend.Micro.ScanMail.for.Microsoft.Exchange.Report.ParameterPrompt.StartDateTime</Prompt>
</ReportParameter>
<ReportParameter name="StartDate_OffsetType" binding="StartDate_OffsetType"/>
<ReportParameter name="StartDate_OffsetValue" binding="StartDate_OffsetValue"/>
<ReportParameter name="EndDate_BaseType" binding="EndDate_BaseType"/>
<ReportParameter name="EndDate_BaseValue" binding="EndDate_BaseValue">
<Prompt>Trend.Micro.ScanMail.for.Microsoft.Exchange!Trend.Micro.ScanMail.for.Microsoft.Exchange.Report.ParameterPrompt.EndDateTime</Prompt>
</ReportParameter>
<ReportParameter name="EndDate_OffsetType" binding="EndDate_OffsetType"/>
<ReportParameter name="EndDate_OffsetValue" binding="EndDate_OffsetValue"/>
</ReportParameters>
</Control>
<Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.ComboBox" rowSpan="1">
<ReportParameters>
<ReportParameter name="Server" binding="Server">
<Prompt>Trend.Micro.ScanMail.for.Microsoft.Exchange!Trend.Micro.ScanMail.for.Microsoft.Exchange.Report.ParameterPrompt.Server</Prompt>
</ReportParameter>
</ReportParameters>
</Control>
<Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.ComboBox" rowSpan="1">
<ReportParameters>
<ReportParameter name="ChartScale" binding="ChartScale">
<Prompt>Trend.Micro.ScanMail.for.Microsoft.Exchange!Trend.Micro.ScanMail.for.Microsoft.Exchange.Report.ParameterPrompt.ChartScale</Prompt>
</ReportParameter>
</ReportParameters>
</Control>
</Controls>
</ParameterBlock>
<Definition>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataWarehouseMain">
<DataSourceReference>Data Warehouse Main</DataSourceReference>
<rd:DataSourceID>e4235c51-407f-4065-8519-a1e57374bc45</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<PageWidth>13in</PageWidth>
<ReportParameters>
<ReportParameter Name="Server">
<DataType>String</DataType>
<Prompt>Server</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>ComputerList</DataSetName>
<ValueField>Server</ValueField>
<LabelField>Server</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ChartScale">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Hourly</Value>
</Values>
</DefaultValue>
<Prompt>Chart Scale</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Hourly</Value>
</ParameterValue>
<ParameterValue>
<Value>Daily</Value>
</ParameterValue>
<ParameterValue>
<Value>Weekly</Value>
</ParameterValue>
<ParameterValue>
<Value>Monthly</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="StartDate_BaseType">
<DataType>String</DataType>
<Prompt>[Start Date Base]</Prompt>
</ReportParameter>
<ReportParameter Name="StartDate_BaseValue">
<DataType>DateTime</DataType>
<Prompt>[From]</Prompt>
</ReportParameter>
<ReportParameter Name="StartDate_OffsetType">
<DataType>String</DataType>
<Prompt>[Start Date Offset Type]</Prompt>
</ReportParameter>
<ReportParameter Name="StartDate_OffsetValue">
<DataType>Integer</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>[Start Date Offset]</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate_BaseType">
<DataType>String</DataType>
<Prompt>[End Date Base]</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate_BaseValue">
<DataType>DateTime</DataType>
<Prompt>[To]</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate_OffsetType">
<DataType>String</DataType>
<Prompt>[End Date Offset Type]</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate_OffsetValue">
<DataType>Integer</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>[End Date Offset]</Prompt>
</ReportParameter>
<ReportParameter Name="TimeZone">
<DataType>String</DataType>
<Prompt>[Time Zone]</Prompt>
</ReportParameter>
<ReportParameter Name="TimeZoneName">
<DataType>String</DataType>
<Nullable>true</Nullable>
<AllowBlank>true</AllowBlank>
<Prompt>[Time Zone Name]</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.05in</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body><Script>
<ColumnSpacing>0.25in</ColumnSpacing>
<ReportItems>
<Table Name="table3">
<NoRows>= "There is no data available for this report. This can occur when there is no relevant data available for collection, the reports dependent rules are not enabled or when none of the relevant data has been copied to the Reporting database." </NoRows>
<DataSetName>SMEXPerf</DataSetName>
<Top>1.6in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>2</ColSpan>
<ReportItems>
<Textbox Name="Server_1">
<ZIndex>29</ZIndex>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Server.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>28</ZIndex>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value/>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>27</ZIndex>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value/>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.34633in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table3_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Server.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ZIndex>26</ZIndex>
<Style></Style>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox39">
<rd:DefaultName>textbox39</rd:DefaultName>
<ZIndex>25</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Spam prevention summary</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox41">
<rd:DefaultName>textbox41</rd:DefaultName>
<ZIndex>24</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value/>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<rd:DefaultName>textbox14</rd:DefaultName>
<ZIndex>23</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value/>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
<Visibility>
<ToggleItem>Server_1</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle2">
<ZIndex>22</ZIndex>
<Style></Style>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>21</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Activity</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<ZIndex>20</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>130pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Frequency</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<rd:DefaultName>textbox18</rd:DefaultName>
<ZIndex>19</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Percentage</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
<Visibility>
<ToggleItem>Server_1</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle3">
<ZIndex>18</ZIndex>
<Style></Style>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox27">
<rd:DefaultName>textbox27</rd:DefaultName>
<ZIndex>17</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Total messages scanned</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox28">
<rd:DefaultName>textbox28</rd:DefaultName>
<ZIndex>16</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!TotalMessagesScanned.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<rd:DefaultName>textbox29</rd:DefaultName>
<ZIndex>15</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value/>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
<Visibility>
<ToggleItem>Server_1</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle4">
<ZIndex>14</ZIndex>
<Style></Style>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox34">
<rd:DefaultName>textbox34</rd:DefaultName>
<ZIndex>13</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>12pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Spam detected</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox35">
<rd:DefaultName>textbox35</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!SpamDetected.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox36">
<rd:DefaultName>textbox36</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Format(Iif (Sum(Fields!TotalMessagesScanned.Value) = 0, 0, Sum(Fields!SpamDetected.Value) / Sum(Fields!TotalMessagesScanned.Value)) * 100, "##0.00") & "% of total messages scanned"</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
<Visibility>
<ToggleItem>Server_1</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle5">
<ZIndex>10</ZIndex>
<Style></Style>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>12pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Phish detected</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!PhishDetected.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<rd:DefaultName>textbox13</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Format(Iif (Sum(Fields!TotalMessagesScanned.Value) = 0, 0, Sum(Fields!PhishDetected.Value) / Sum(Fields!TotalMessagesScanned.Value)) * 100, "##0.00") & "% of total messages scanned"</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
<Visibility>
<ToggleItem>Server_1</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle8">
<ZIndex>6</ZIndex>
<Style></Style>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>12pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Blocked connection</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!BlockedConnections.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Format(Iif (Sum(Fields!TotalMessagesScanned.Value) = 0, 0, Sum(Fields!BlockedConnections.Value) / Sum(Fields!TotalMessagesScanned.Value)) * 100, "##0.00") & "% of total messages scanned"</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
<Visibility>
<ToggleItem>Server_1</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>4</ColSpan>
<ReportItems>
<Rectangle Name="rectangle7">
<ZIndex>2</ZIndex>
<Style></Style>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
<Visibility>
<ToggleItem>Server_1</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Rectangle Name="rectangle6">
<ZIndex>1</ZIndex>
<Style></Style>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Rectangle Name="rectangle16">
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>1</ZIndex>
<Width>10.54374in</Width>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Spam prevention graph</Value>
</Textbox>
<Chart Name="chart2">
<Legend>
<Visible>true</Visible>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
</Style>
<Position>BottomCenter</Position>
<Layout>Row</Layout>
</Legend>
<Subtype>Plain</Subtype>
<Title/>
<Height>7.3in</Height>
<CategoryAxis>
<Axis>
<Title/>
<Style></Style>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>SMEXPerf</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Total messages scanned</Label>
</StaticMember>
<StaticMember>
<Label>Spam detected</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Top>0.25in</Top>
<PlotArea>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title/>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Line</Type>
<Width>10.54374in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart2_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!LocalDateTimeSampled.Value)</GroupExpression>
<GroupExpression>=Iif ((Parameters!ChartScale.Value = "Monthly") or (Parameters!ChartScale.Value = "Daily") or (Parameters!ChartScale.Value = "Hourly"), Month(Fields!LocalDateTimeSampled.Value), Nothing)</GroupExpression>
<GroupExpression>=Iif (Parameters!ChartScale.Value = "Weekly", DatePart("ww", Fields!LocalDateTimeSampled.Value), Nothing)</GroupExpression>
<GroupExpression>=Iif ((Parameters!ChartScale.Value = "Daily") or (Parameters!ChartScale.Value = "Hourly"), Day(Fields!LocalDateTimeSampled.Value), Nothing)</GroupExpression>
<GroupExpression>=Iif (Parameters!ChartScale.Value = "Hourly", Hour(Fields!LocalDateTimeSampled.Value), Nothing)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!LocalDateTimeSampled.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Iif (Parameters!ChartScale.Value = "Monthly", MonthName(Month(Fields!LocalDateTimeSampled.Value)) & ", " & Year(Fields!LocalDateTimeSampled.Value), Iif (Parameters!ChartScale.Value = "Weekly", WeekdayName(Weekday(Min(Fields!LocalDateTimeSampled.Value))) & ", " & MonthName(Month(Min(Fields!LocalDateTimeSampled.Value))) & " " & Day(Min(Fields!LocalDateTimeSampled.Value)) & ", " & Year(Min(Fields!LocalDateTimeSampled.Value)) & " - " & WeekdayName(Weekday(Max(Fields!LocalDateTimeSampled.Value))) & ", " & MonthName(Month(Max(Fields!LocalDateTimeSampled.Value))) & " " & Day(Max(Fields!LocalDateTimeSampled.Value)) & ", " & Year(Max(Fields!LocalDateTimeSampled.Value)), Iif (Parameters!ChartScale.Value = "Daily", WeekdayName(Weekday(Fields!LocalDateTimeSampled.Value)) & ", " & MonthName(Month(Fields!LocalDateTimeSampled.Value)) & " " & Day(Fields!LocalDateTimeSampled.Value) & ", " & Year(Fields!LocalDateTimeSampled.Value), Iif (Parameters!ChartScale.Value = "Hourly", WeekdayName(Weekday(Fields!LocalDateTimeSampled.Value)) & ", " & MonthName(Month(Fields!LocalDateTimeSampled.Value)) & " " & Day(Fields!LocalDateTimeSampled.Value) & ", " & Year(Fields!LocalDateTimeSampled.Value) & " " & Hour(Fields!LocalDateTimeSampled.Value) & ":00", Nothing))))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!TotalMessagesScanned.Value)</Value>
</DataValue>
</DataValues>
<DataLabel/>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!SpamDetected.Value)</Value>
</DataValue>
</DataValues>
<DataLabel/>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style></Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Style></Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>None</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
</Rectangle>
</ReportItems>
</TableCell>
</TableCells>
<Height>7.55in</Height>
<Visibility>
<ToggleItem>Server_1</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table3_Group2">
<GroupExpressions>
<GroupExpression>=Fields!Server.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>9</ZIndex>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
</Style>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>2</ColSpan>
<ReportItems>
<Textbox Name="textbox38">
<rd:DefaultName>textbox38</rd:DefaultName>
<ZIndex>32</ZIndex>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Server Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>31</ZIndex>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value/>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>30</ZIndex>
<Style></Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value/>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.34633in</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>0.45in</Width>
</TableColumn>
<TableColumn>
<Width>3.95in</Width>
</TableColumn>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>4.1in</Width>
</TableColumn>
</TableColumns>
<Height>9.99266in</Height>
</Table>
<Textbox Name="textboxRightLogo">
<Left>9in</Left>
<ZIndex>8</ZIndex>
<Style></Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.25pt</Default>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<Color>#000000</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<Height>0.5625in</Height>
<Value/>
</Textbox>
<Textbox Name="textboxReportTitle_tmp">
<ZIndex>7</ZIndex>
<Width>9in</Width>
<Style></Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.25pt</Default>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<Color>#000000</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.5625in</Height>
<Value>Spam Prevention Report</Value>
</Textbox>
<Textbox Name="textboxParameterValue_tmp1">
<Left>3in</Left>
<Top>1.375in</Top>
<ZIndex>6</ZIndex>
<Style></Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.125in</Height>
<Value>= Parameters!Server.Value</Value>
</Textbox>
<Textbox Name="textboxParameterValue_tmp0">
<Left>3in</Left>
<Top>1.25in</Top>
<ZIndex>5</ZIndex>
<Style></Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.125in</Height>
<Value>=Code.FormatDateTime("g", Code.GetReportStartDate()) & " - " & Code.FormatDateTime("g", Code.GetReportEndDate())</Value>
</Textbox>
<Textbox Name="textboxExecutionTime_tmp">
<Left>9in</Left>
<Top>0.5625in</Top>
<ZIndex>4</ZIndex>
<Style></Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<Color>#000000</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<Height>0.25in</Height>
<Value>=Globals.ExecutionTime</Value>
</Textbox>
<Textbox Name="textboxParameterName_tmp1">
<Top>1.375in</Top>
<ZIndex>3</ZIndex>
<Width>3in</Width>
<Style></Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.125in</Height>
<Value>Server :</Value>
</Textbox>
<Textbox Name="textboxParameterName_tmp0">
<Top>1.25in</Top>
<ZIndex>2</ZIndex>
<Width>3in</Width>
<Style></Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.125in</Height>
<Value>Date Range :</Value>
</Textbox>
<Textbox Name="textboxDescription_tmp">
<Top>0.8125in</Top>
<ZIndex>1</ZIndex>
<Visibility>
<ToggleItem>textboxDescriptionHeading_tmp</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<ToggleImage>
<InitialState>true</InitialState>
</ToggleImage>
<Style></Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>GhostWhite</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.375in</Height>
<Value>= "Spam Prevention Reports show information about number of spam and phish detected in the total amount of messages scanned. It includes a graph showing spam messages detected by ScanMail versus time, and the amount of false positive identifications during the same time. It lists the reported false positives according to sender and domain. It shows the spam detected according to the spam detection rate." & VbCrLf & "" </Value>
</Textbox>
<Textbox Name="textboxDescriptionHeading_tmp">
<Top>0.5625in</Top>
<Width>9in</Width>
<Style></Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>8pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Description</Value>
</Textbox>
</ReportItems>
<Height>13.55in</Height>
<Style></Style>
<BorderWidth>
<Default>0.5pt</Default>
</BorderWidth>
</Style> </Script></Body>
<rd:ReportID>212d782b-9718-4b8d-a1c1-2bd72afd7b82</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="SMEXPerf">
<Query>
<CommandText>
SELECT
dtPerfVirusesDetected1.Server AS Server,
CASE WHEN (dtPerfVirusesDetected1.VirusesDetected >= dtPerfVirusesDetected2.VirusesDetected) THEN (dtPerfVirusesDetected1.VirusesDetected - dtPerfVirusesDetected2.VirusesDetected) ELSE dtPerfVirusesDetected1.VirusesDetected END AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtPerfVirusesDetected2.DateTimeSampled, dtPerfVirusesDetected1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtPerfVirusesDetected1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS VirusesDetected,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total security risks' AND
ObjectName = 'SMEX Report Collection'
) dtPRVirusesDetected
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRVirusesDetected.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtPerfVirusesDetected1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS VirusesDetected,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total security risks' AND
ObjectName = 'SMEX Report Collection'
) dtPRVirusesDetected
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRVirusesDetected.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtPerfVirusesDetected2
ON
(dtPerfVirusesDetected1.Id - 1 = dtPerfVirusesDetected2.Id) AND
(dtPerfVirusesDetected1.Server = dtPerfVirusesDetected2.Server)
UNION
SELECT
dtPerfVirusUncleanable1.Server AS Server,
0 AS VirusesDetected,
CASE WHEN (dtPerfVirusUncleanable1.VirusUncleanable >= dtPerfVirusUncleanable2.VirusUncleanable) THEN (dtPerfVirusUncleanable1.VirusUncleanable - dtPerfVirusUncleanable2.VirusUncleanable) ELSE dtPerfVirusUncleanable1.VirusUncleanable END AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtPerfVirusUncleanable2.DateTimeSampled, dtPerfVirusUncleanable1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtPerfVirusUncleanable1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS VirusUncleanable,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total uncleanable viruses/malware' AND
ObjectName = 'SMEX Report Collection'
) dtPRVirusUncleanable
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRVirusUncleanable.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtPerfVirusUncleanable1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS VirusUncleanable,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total uncleanable viruses/malware' AND
ObjectName = 'SMEX Report Collection'
) dtPRVirusUncleanable
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRVirusUncleanable.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtPerfVirusUncleanable2
ON
(dtPerfVirusUncleanable1.Id - 1 = dtPerfVirusUncleanable2.Id) AND
(dtPerfVirusUncleanable1.Server = dtPerfVirusUncleanable2.Server)
UNION
SELECT
dtTotalAttachmentsScanned1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
CASE WHEN (dtTotalAttachmentsScanned1.TotalAttachmentsScanned >= dtTotalAttachmentsScanned2.TotalAttachmentsScanned) THEN (dtTotalAttachmentsScanned1.TotalAttachmentsScanned - dtTotalAttachmentsScanned2.TotalAttachmentsScanned) ELSE dtTotalAttachmentsScanned1.TotalAttachmentsScanned END AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtTotalAttachmentsScanned2.DateTimeSampled, dtTotalAttachmentsScanned1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtTotalAttachmentsScanned1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS TotalAttachmentsScanned,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total attachments scanned' AND
ObjectName = 'SMEX Report Collection'
) dtPRTotalAttachmentsScanned
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRTotalAttachmentsScanned.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtTotalAttachmentsScanned1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS TotalAttachmentsScanned,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total attachments scanned' AND
ObjectName = 'SMEX Report Collection'
) dtPRTotalAttachmentsScanned
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRTotalAttachmentsScanned.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtTotalAttachmentsScanned2
ON
(dtTotalAttachmentsScanned1.Id - 1 = dtTotalAttachmentsScanned2.Id) AND
(dtTotalAttachmentsScanned1.Server = dtTotalAttachmentsScanned2.Server)
UNION
SELECT
dtBlockedAttachments1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
CASE WHEN (dtBlockedAttachments1.BlockedAttachments >= dtBlockedAttachments2.BlockedAttachments) THEN (dtBlockedAttachments1.BlockedAttachments - dtBlockedAttachments2.BlockedAttachments) ELSE dtBlockedAttachments1.BlockedAttachments END AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtBlockedAttachments2.DateTimeSampled, dtBlockedAttachments1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtBlockedAttachments1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS BlockedAttachments,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total blocked attachments' AND
ObjectName = 'SMEX Report Collection'
) dtPRBlockedAttachments
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRBlockedAttachments.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtBlockedAttachments1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS BlockedAttachments,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total blocked attachments' AND
ObjectName = 'SMEX Report Collection'
) dtPRBlockedAttachments
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRBlockedAttachments.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtBlockedAttachments2
ON
(dtBlockedAttachments1.Id - 1 = dtBlockedAttachments2.Id) AND
(dtBlockedAttachments1.Server = dtBlockedAttachments2.Server)
UNION
SELECT
dtTotalMessagesScanned1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
CASE WHEN (dtTotalMessagesScanned1.TotalMessagesScanned >= dtTotalMessagesScanned2.TotalMessagesScanned) THEN (dtTotalMessagesScanned1.TotalMessagesScanned - dtTotalMessagesScanned2.TotalMessagesScanned) ELSE dtTotalMessagesScanned1.TotalMessagesScanned END AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtTotalMessagesScanned2.DateTimeSampled, dtTotalMessagesScanned1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtTotalMessagesScanned1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS TotalMessagesScanned,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total messages scanned' AND
ObjectName = 'SMEX Report Collection'
) dtPRTotalMessagesScanned
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRTotalMessagesScanned.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtTotalMessagesScanned1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS TotalMessagesScanned,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total messages scanned' AND
ObjectName = 'SMEX Report Collection'
) dtPRTotalMessagesScanned
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRTotalMessagesScanned.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtTotalMessagesScanned2
ON
(dtTotalMessagesScanned1.Id - 1 = dtTotalMessagesScanned2.Id) AND
(dtTotalMessagesScanned1.Server = dtTotalMessagesScanned2.Server)
UNION
SELECT
dtContentViolations1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
CASE WHEN (dtContentViolations1.ContentViolations >= dtContentViolations2.ContentViolations) THEN (dtContentViolations1.ContentViolations - dtContentViolations2.ContentViolations) ELSE dtContentViolations1.ContentViolations END AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtContentViolations2.DateTimeSampled, dtContentViolations1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtContentViolations1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS ContentViolations,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total content violations' AND
ObjectName = 'SMEX Report Collection'
) dtPRContentViolations
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRContentViolations.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtContentViolations1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS ContentViolations,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total content violations' AND
ObjectName = 'SMEX Report Collection'
) dtPRContentViolations
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRContentViolations.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtContentViolations2
ON
(dtContentViolations1.Id - 1 = dtContentViolations2.Id) AND
(dtContentViolations1.Server = dtContentViolations2.Server)
UNION
SELECT
dtDLPViolations1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
CASE WHEN (dtDLPViolations1.DLPViolations >= dtDLPViolations2.DLPViolations) THEN (dtDLPViolations1.DLPViolations - dtDLPViolations2.DLPViolations) ELSE dtDLPViolations1.DLPViolations END AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtDLPViolations2.DateTimeSampled, dtDLPViolations1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtDLPViolations1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS DLPViolations,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total DLP violations' AND
ObjectName = 'SMEX Report Collection'
) dtPRDLPViolations
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRDLPViolations.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtDLPViolations1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS DLPViolations,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total DLP violations' AND
ObjectName = 'SMEX Report Collection'
) dtPRDLPViolations
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRDLPViolations.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtDLPViolations2
ON
(dtDLPViolations1.Id - 1 = dtDLPViolations2.Id) AND
(dtDLPViolations1.Server = dtDLPViolations2.Server)
UNION
SELECT
dtSpamDetected1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
CASE WHEN (dtSpamDetected1.SpamDetected >= dtSpamDetected2.SpamDetected) THEN (dtSpamDetected1.SpamDetected - dtSpamDetected2.SpamDetected) ELSE dtSpamDetected1.SpamDetected END AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtSpamDetected2.DateTimeSampled, dtSpamDetected1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtSpamDetected1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS SpamDetected,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total spam messages' AND
ObjectName = 'SMEX Report Collection'
) dtPRSpamDetected
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRSpamDetected.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtSpamDetected1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS SpamDetected,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total spam messages' AND
ObjectName = 'SMEX Report Collection'
) dtPRSpamDetected
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRSpamDetected.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtSpamDetected2
ON
(dtSpamDetected1.Id - 1 = dtSpamDetected2.Id) AND
(dtSpamDetected1.Server = dtSpamDetected2.Server)
UNION
SELECT
dtPhishDetected1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
CASE WHEN (dtPhishDetected1.PhishDetected >= dtPhishDetected2.PhishDetected) THEN (dtPhishDetected1.PhishDetected - dtPhishDetected2.PhishDetected) ELSE dtPhishDetected1.PhishDetected END AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtPhishDetected2.DateTimeSampled, dtPhishDetected1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtPhishDetected1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS PhishDetected,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total phishing messages' AND
ObjectName = 'SMEX Report Collection'
) dtPRPhishDetected
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRPhishDetected.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtPhishDetected1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS PhishDetected,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total phishing messages' AND
ObjectName = 'SMEX Report Collection'
) dtPRPhishDetected
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRPhishDetected.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtPhishDetected2
ON
(dtPhishDetected1.Id - 1 = dtPhishDetected2.Id) AND
(dtPhishDetected1.Server = dtPhishDetected2.Server)
UNION
SELECT
dtBlockedConnections1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
CASE WHEN (dtBlockedConnections1.BlockedConnections >= dtBlockedConnections2.BlockedConnections) THEN (dtBlockedConnections1.BlockedConnections - dtBlockedConnections2.BlockedConnections) ELSE dtBlockedConnections1.BlockedConnections END AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtBlockedConnections2.DateTimeSampled, dtBlockedConnections1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtBlockedConnections1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS BlockedConnections,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total blocked connections' AND
ObjectName = 'SMEX Report Collection'
) dtPRBlockedConnections
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRBlockedConnections.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtBlockedConnections1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS BlockedConnections,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total blocked connections' AND
ObjectName = 'SMEX Report Collection'
) dtPRBlockedConnections
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRBlockedConnections.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtBlockedConnections2
ON
(dtBlockedConnections1.Id - 1 = dtBlockedConnections2.Id) AND
(dtBlockedConnections1.Server = dtBlockedConnections2.Server)
UNION
SELECT
dtTotalUnscannableMessageParts1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
CASE WHEN (dtTotalUnscannableMessageParts1.TotalUnscannableMessageParts >= dtTotalUnscannableMessageParts2.TotalUnscannableMessageParts) THEN (dtTotalUnscannableMessageParts1.TotalUnscannableMessageParts - dtTotalUnscannableMessageParts2.TotalUnscannableMessageParts) ELSE dtTotalUnscannableMessageParts1.TotalUnscannableMessageParts END AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtTotalUnscannableMessageParts2.DateTimeSampled, dtTotalUnscannableMessageParts1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtTotalUnscannableMessageParts1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS TotalUnscannableMessageParts,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total unscannable message parts' AND
ObjectName = 'SMEX Report Collection'
) dtPRTotalUnscannableMessageParts
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRTotalUnscannableMessageParts.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtTotalUnscannableMessageParts1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS TotalUnscannableMessageParts,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total unscannable message parts' AND
ObjectName = 'SMEX Report Collection'
) dtPRTotalUnscannableMessageParts
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRTotalUnscannableMessageParts.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtTotalUnscannableMessageParts2
ON
(dtTotalUnscannableMessageParts1.Id - 1 = dtTotalUnscannableMessageParts2.Id) AND
(dtTotalUnscannableMessageParts1.Server = dtTotalUnscannableMessageParts2.Server)
UNION
SELECT
dtWormTrojanVirusType1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
CASE WHEN (dtWormTrojanVirusType1.WormTrojanVirusType >= dtWormTrojanVirusType2.WormTrojanVirusType) THEN (dtWormTrojanVirusType1.WormTrojanVirusType - dtWormTrojanVirusType2.WormTrojanVirusType) ELSE dtWormTrojanVirusType1.WormTrojanVirusType END AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtWormTrojanVirusType2.DateTimeSampled, dtWormTrojanVirusType1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtWormTrojanVirusType1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS WormTrojanVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total worms/trojans' AND
ObjectName = 'SMEX Report Collection'
) dtPRWormTrojanVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRWormTrojanVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtWormTrojanVirusType1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS WormTrojanVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total worms/trojans' AND
ObjectName = 'SMEX Report Collection'
) dtPRWormTrojanVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRWormTrojanVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtWormTrojanVirusType2
ON
(dtWormTrojanVirusType1.Id - 1 = dtWormTrojanVirusType2.Id) AND
(dtWormTrojanVirusType1.Server = dtWormTrojanVirusType2.Server)
UNION
SELECT
dtPackedFileVirusType1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
CASE WHEN (dtPackedFileVirusType1.PackedFileVirusType >= dtPackedFileVirusType2.PackedFileVirusType) THEN (dtPackedFileVirusType1.PackedFileVirusType - dtPackedFileVirusType2.PackedFileVirusType) ELSE dtPackedFileVirusType1.PackedFileVirusType END AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtPackedFileVirusType2.DateTimeSampled, dtPackedFileVirusType1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtPackedFileVirusType1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS PackedFileVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total packed files' AND
ObjectName = 'SMEX Report Collection'
) dtPRPackedFileVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRPackedFileVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtPackedFileVirusType1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS PackedFileVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total packed files' AND
ObjectName = 'SMEX Report Collection'
) dtPRPackedFileVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRPackedFileVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtPackedFileVirusType2
ON
(dtPackedFileVirusType1.Id - 1 = dtPackedFileVirusType2.Id) AND
(dtPackedFileVirusType1.Server = dtPackedFileVirusType2.Server)
UNION
SELECT
dtGenericVirusType1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
CASE WHEN (dtGenericVirusType1.GenericVirusType >= dtGenericVirusType2.GenericVirusType) THEN (dtGenericVirusType1.GenericVirusType - dtGenericVirusType2.GenericVirusType) ELSE dtGenericVirusType1.GenericVirusType END AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtGenericVirusType2.DateTimeSampled, dtGenericVirusType1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtGenericVirusType1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS GenericVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total generic virus types' AND
ObjectName = 'SMEX Report Collection'
) dtPRGenericVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRGenericVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtGenericVirusType1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS GenericVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total generic virus types' AND
ObjectName = 'SMEX Report Collection'
) dtPRGenericVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRGenericVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtGenericVirusType2
ON
(dtGenericVirusType1.Id - 1 = dtGenericVirusType2.Id) AND
(dtGenericVirusType1.Server = dtGenericVirusType2.Server)
UNION
SELECT
dtVirusVirusType1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
CASE WHEN (dtVirusVirusType1.VirusVirusType >= dtVirusVirusType2.VirusVirusType) THEN (dtVirusVirusType1.VirusVirusType - dtVirusVirusType2.VirusVirusType) ELSE dtVirusVirusType1.VirusVirusType END AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtVirusVirusType2.DateTimeSampled, dtVirusVirusType1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtVirusVirusType1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS VirusVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total viruses' AND
ObjectName = 'SMEX Report Collection'
) dtPRVirusVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRVirusVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtVirusVirusType1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS VirusVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total viruses' AND
ObjectName = 'SMEX Report Collection'
) dtPRVirusVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRVirusVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtVirusVirusType2
ON
(dtVirusVirusType1.Id - 1 = dtVirusVirusType2.Id) AND
(dtVirusVirusType1.Server = dtVirusVirusType2.Server)
UNION
SELECT
dtOtherMaliciousVirusType1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
CASE WHEN (dtOtherMaliciousVirusType1.OtherMaliciousVirusType >= dtOtherMaliciousVirusType2.OtherMaliciousVirusType) THEN (dtOtherMaliciousVirusType1.OtherMaliciousVirusType - dtOtherMaliciousVirusType2.OtherMaliciousVirusType) ELSE dtOtherMaliciousVirusType1.OtherMaliciousVirusType END AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtOtherMaliciousVirusType2.DateTimeSampled, dtOtherMaliciousVirusType1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtOtherMaliciousVirusType1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS OtherMaliciousVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total other malicious code' AND
ObjectName = 'SMEX Report Collection'
) dtPROtherMaliciousVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPROtherMaliciousVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtOtherMaliciousVirusType1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS OtherMaliciousVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total other malicious code' AND
ObjectName = 'SMEX Report Collection'
) dtPROtherMaliciousVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPROtherMaliciousVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtOtherMaliciousVirusType2
ON
(dtOtherMaliciousVirusType1.Id - 1 = dtOtherMaliciousVirusType2.Id) AND
(dtOtherMaliciousVirusType1.Server = dtOtherMaliciousVirusType2.Server)
UNION
SELECT
dtAdditionalThreatVirusType1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
CASE WHEN (dtAdditionalThreatVirusType1.AdditionalThreatVirusType >= dtAdditionalThreatVirusType2.AdditionalThreatVirusType) THEN (dtAdditionalThreatVirusType1.AdditionalThreatVirusType - dtAdditionalThreatVirusType2.AdditionalThreatVirusType) ELSE dtAdditionalThreatVirusType1.AdditionalThreatVirusType END AS AdditionalThreatVirusType,
0 AS MaliciousURLCount,
DATEDIFF("hh", dtAdditionalThreatVirusType2.DateTimeSampled, dtAdditionalThreatVirusType1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtAdditionalThreatVirusType1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS AdditionalThreatVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total spyware/grayware' AND
ObjectName = 'SMEX Report Collection'
) dtPRAdditionalThreatVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRAdditionalThreatVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtAdditionalThreatVirusType1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS AdditionalThreatVirusType,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total spyware/grayware' AND
ObjectName = 'SMEX Report Collection'
) dtPRAdditionalThreatVirusType
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = dtPRAdditionalThreatVirusType.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtAdditionalThreatVirusType2
ON
(dtAdditionalThreatVirusType1.Id - 1 = dtAdditionalThreatVirusType2.Id) AND
(dtAdditionalThreatVirusType1.Server = dtAdditionalThreatVirusType2.Server)
UNION
SELECT
dtMaliciousURL1.Server AS Server,
0 AS VirusesDetected,
0 AS VirusUncleanable,
0 AS TotalAttachmentsScanned,
0 AS BlockedAttachments,
0 AS TotalMessagesScanned,
0 AS ContentViolations,
0 AS DLPViolations,
0 AS SpamDetected,
0 AS PhishDetected,
0 AS BlockedConnections,
0 AS TotalUnscannableMessageParts,
0 AS WormTrojanVirusType,
0 AS PackedFileVirusType,
0 AS GenericVirusType,
0 AS VirusVirusType,
0 AS OtherMaliciousVirusType,
0 AS AdditionalThreatVirusType,
CASE WHEN (dtMaliciousURL1.MaliciousURLCount >= dtMaliciousURL2.MaliciousURLCount) THEN (dtMaliciousURL1.MaliciousURLCount - dtMaliciousURL2.MaliciousURLCount) ELSE dtMaliciousURL1.MaliciousURLCount END AS MaliciousURLCount,
DATEDIFF("hh", dtMaliciousURL2.DateTimeSampled, dtMaliciousURL1.DateTimeSampled) AS DateTimeSampledDiff,
DATEADD("hh",-1,dtMaliciousURL1.DateTimeSampled) AS DateTimeSampled
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS MaliciousURLCount,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total malicious URLs' AND
ObjectName = 'SMEX Report Collection'
) PRMaliciousURL
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = PRMaliciousURL.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtMaliciousURL1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER(ORDER BY ME.Path,PPH.DateTime ASC) AS Id,
ME.Path AS Server,
PPH.MaxValue AS MaliciousURLCount,
PPH.DateTime AS DateTimeSampled
FROM
(
SELECT
ObjectName,
CounterName,
RuleRowID
FROM
vPerformanceRule
WHERE
CounterName = 'Number of total malicious URLs' AND
ObjectName = 'SMEX Report Collection'
) PRMaliciousURL
INNER JOIN
vPerformanceRuleInstance PRI
ON
PRI.RuleRowID = PRMaliciousURL.RuleRowID
INNER JOIN
Perf.vPerfHourly PPH
ON
PPH.PerformanceRuleInstanceRowId = PRI.PerformanceRuleInstanceRowId
INNER JOIN
ManagedEntity ME
ON
ME.ManagedEntityRowId = PPH.ManagedEntityRowId
WHERE
(@Server = '<ALL>' OR ME.Path = @Server) AND
(PPH.DateTime >= @BeginDate AND PPH.DateTime < DATEADD("ss",1,@EndDate)) AND
(PPH.MaxValue != 0)
) dtMaliciousURL2
ON
(dtMaliciousURL1.Id - 1 = dtMaliciousURL2.Id) AND
(dtMaliciousURL1.Server = dtMaliciousURL2.Server) </CommandText>
<QueryParameters>
<QueryParameter Name="@Server">
<Value>=Parameters!Server.Value</Value>
</QueryParameter>
<QueryParameter Name="@BeginDate">
<Value>=Code.ToDbDate(Code.GetReportStartDate())</Value>
</QueryParameter>
<QueryParameter Name="@EndDate">
<Value>=Code.ToDbDate(Code.GetReportEndDate())</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>DataWarehouseMain</DataSourceName>
</Query>
<Fields>
<Field Name="Server">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Server</DataField>
</Field>
<Field Name="VirusesDetected">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>VirusesDetected</DataField>
</Field>
<Field Name="VirusUncleanable">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>VirusUncleanable</DataField>
</Field>
<Field Name="TotalAttachmentsScanned">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>TotalAttachmentsScanned</DataField>
</Field>
<Field Name="BlockedAttachments">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>BlockedAttachments</DataField>
</Field>
<Field Name="TotalMessagesScanned">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>TotalMessagesScanned</DataField>
</Field>
<Field Name="ContentViolations">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ContentViolations</DataField>
</Field>
<Field Name="DLPViolations">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>DLPViolations</DataField>
</Field>
<Field Name="SpamDetected">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SpamDetected</DataField>
</Field>
<Field Name="PhishDetected">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>PhishDetected</DataField>
</Field>
<Field Name="BlockedConnections">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>BlockedConnections</DataField>
</Field>
<Field Name="TotalUnscannableMessageParts">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>TotalUnscannableMessageParts</DataField>
</Field>
<Field Name="WormTrojanVirusType">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>WormTrojanVirusType</DataField>
</Field>
<Field Name="PackedFileVirusType">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>PackedFileVirusType</DataField>
</Field>
<Field Name="GenericVirusType">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>GenericVirusType</DataField>
</Field>
<Field Name="VirusVirusType">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>VirusVirusType</DataField>
</Field>
<Field Name="OtherMaliciousVirusType">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OtherMaliciousVirusType</DataField>
</Field>
<Field Name="AdditionalThreatVirusType">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>AdditionalThreatVirusType</DataField>
</Field>
<Field Name="MaliciousURLCount">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>MaliciousURLCount</DataField>
</Field>
<Field Name="DateTimeSampledDiff">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>DateTimeSampledDiff</DataField>
</Field>
<Field Name="DateTimeSampled">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateTimeSampled</DataField>
</Field>
<Field Name="LocalDateTimeSampled">
<Value>=CDate(Code.ToReportDate(Fields!DateTimeSampled.Value))</Value>
</Field>
</Fields>
</DataSet>
<DataSet Name="ComputerList">
<Query>
<CommandText>
SELECT
Path AS Server
FROM
vManagedEntity
WHERE
FullName Like 'Trend.Micro.ScanMail.for.Microsoft.Exchange.Trend_Micro_ScanMail_for_Microsoft_Exchange_Installation%'
UNION
SELECT
'<ALL>'
ORDER BY
Server </CommandText>
<Timeout>3000000</Timeout>
<DataSourceName>DataWarehouseMain</DataSourceName>
</Query>
<Fields>
<Field Name="Server">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Server</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<CodeModules>
<CodeModule>Microsoft.EnterpriseManagement.Reporting.Code, Version=6.0.0.0, Culture=neutral</CodeModule>
</CodeModules>
<Code><Script>
Const LocTableStringQuery As String = "R/S[@N='{0}']/text()"
Const TimeZoneParameterName As String = "TimeZone"
Const SD_BaseTypeParameterName As String = "StartDate_BaseType"
Const SD_BaseValueParameterName As String = "StartDate_BaseValue"
Const SD_OffsetTypeParameterName As String = "StartDate_OffsetType"
Const SD_OffsetValueParameterName As String = "StartDate_OffsetValue"
Const ED_BaseTypeParameterName As String = "EndDate_BaseType"
Const ED_BaseValueParameterName As String = "EndDate_BaseValue"
Const ED_OffsetTypeParameterName As String = "EndDate_OffsetType"
Const ED_OffsetValueParameterName As String = "EndDate_OffsetValue"
Const IsRelativeTimeSupported As Boolean = False
Const TimeTypeParameterName As String = "TimeType"
Const TimeWeekMapParameterName As String = "TimeWeekMap"
Const DataAggregationParameterName As String = "DataAggregation"
Dim LocTables As System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)
Dim ReportTimeZone As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
Dim ReportStartDate As DateTime
Dim ReportEndDate As DateTime
Dim ReportTime As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
Dim ReportCulture As System.Globalization.CultureInfo
Dim ParameterProcessor As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor
Protected Overrides Sub OnInit()
LocTables = new System.Collections.Generic.Dictionary(Of String, Microsoft.EnterpriseManagement.Reporting.XmlStringTable)()
ReportTimeZone = Nothing
ReportStartDate = DateTime.MinValue
ReportEndDate = DateTime.MinValue
ReportTime = Nothing
ReportCulture = System.Globalization.CultureInfo.GetCultureInfo(Report.User("Language"))
ParameterProcessor = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor(ReportCulture)
End Sub
Public Function GetCallingManagementGroupId() As String
Return Microsoft.EnterpriseManagement.Reporting.ReportingConfiguration.ManagementGroupId
End Function
Public Function GetReportLocLanguageCode() As String
Return ReportCulture.ThreeLetterWindowsLanguageName
End Function
Public Function GetLocTable(Name As String) As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
Dim LocTable As Microsoft.EnterpriseManagement.Reporting.XmlStringTable
If Not LocTables.TryGetValue(Name, LocTable) Then
LocTable = New Microsoft.EnterpriseManagement.Reporting.XmlStringTable(LocTableStringQuery, Report.Parameters(Name).Value)
LocTables.Add(Name, LocTable)
End If
Return LocTable
End Function
Public Function GetReportTimeZone() As Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation
If IsNothing(ReportTimeZone) Then ReportTimeZone = Microsoft.EnterpriseManagement.Reporting.TimeZoneCoreInformation.FromValueString(Report.Parameters(TimeZoneParameterName).Value)
Return ReportTimeZone
End Function
Public Function ToDbDate(ByVal DateValue As DateTime) As DateTime
return GetReportTimeZone.ToUniversalTime(DateValue)
End Function
Public Function ToReportDate(ByVal DateValue As DateTime) As DateTime
return GetReportTimeZone.ToLocalTime(DateValue)
End Function
Public Function GetReportStartDate() As DateTime
If (ReportStartDate = DateTime.MinValue) Then
If (IsRelativeTimeSupported) Then
ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
Else
ReportStartDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(SD_BaseTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(SD_OffsetTypeParameterName).Value, Report.Parameters(SD_OffsetValueParameterName).Value)
End if
End If
return ReportStartDate
End Function
Public Function GetReportEndDate() As DateTime
If (ReportEndDate = DateTime.MinValue) Then
If (IsRelativeTimeSupported) Then
ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value, Report.Parameters(TimeTypeParameterName).Value)
Else
ReportEndDate = ParameterProcessor.GetDateTime(ToReportDate(DateTime.UtcNow), Report.Parameters(ED_BaseTypeParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, Report.Parameters(ED_OffsetTypeParameterName).Value, Report.Parameters(ED_OffsetValueParameterName).Value)
End if
End If
return ReportEndDate
End Function
Public Function GetReportTimeFilter() As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime
If IsNothing(ReportTime) Then
If (CInt(Report.Parameters(DataAggregationParameterName).Value) = 0)
ReportTime = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime(Report.Parameters(TimeTypeParameterName).Value, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, CStr(Join(Report.Parameters(TimeWeekMapParameterName).Value, ",")))
Else
ReportTime = New Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime(Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTimeType.Regular, Report.Parameters(SD_BaseValueParameterName).Value, Report.Parameters(ED_BaseValueParameterName).Value, CStr(Join(Report.Parameters(TimeWeekMapParameterName).Value, ",")))
End if
End If
return ReportTime
End Function
Public Function IsBusinessHours(Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As Boolean
return (Not IsNothing(Value)) And (Value.TimeType = Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTimeType.Business)
End Function
Public Function FormatDateTime(Format As String, Value As DateTime) As String
return Value.ToString(Format, ReportCulture)
End Function
Public Function FormatNumber(Format As String, Value As Decimal) As String
return Value.ToString(Format, ReportCulture)
End Function
Public Function FormatString(Format As String, ParamArray Values() as Object) As String
return String.Format(ReportCulture, Format, Values)
End Function
Public Function NullFormatString(Format As String, Value as String) As String
return IIF(String.IsNullOrEmpty(Value), String.Empty, String.Format(ReportCulture, Format, Value))
End Function
Public Function FormatBusinessHours(Format As String, Value As Microsoft.EnterpriseManagement.Reporting.ParameterProcessor.RelativeTime) As String
Dim result As String
If IsBusinessHours(Value) Then
Dim firstDay As DayOfWeek
Dim days As System.Collections.Generic.List(Of String)
firstDay = ReportCulture.DateTimeFormat.FirstDayOfWeek
days = new System.Collections.Generic.List(Of String)()
For loopDay As DayOfWeek = DayOfWeek.Sunday To DayOfWeek.Saturday
Dim day As DayOfWeek
day = CType((CInt(loopDay) + CInt(firstDay)) Mod 7, DayOfWeek)
If value.WeekMap.Contains(day) Then days.Add(ReportCulture.DateTimeFormat.GetAbbreviatedDayName(day))
Next loopDay
result = FormatString(Format, DateTime.Today.Add(Value.StartTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), DateTime.Today.Add(Value.EndTime).ToString(ReportCulture.DateTimeFormat.ShortTimePattern), String.Join(",", days.ToArray()))
Else
result = String.Empty
End if
return result
End Function
REM ---------------------------------
Public Enum DataAggregationType
Hourly = 0
Daily = 1
End Enum
REM ---------------------------------
Const DownTimeParameterName As String = "DownTime"
Dim DownTimeTable As System.Collections.Generic.List(Of Integer)
Public Enum StateIntervalType
Red = 0
Green = 1
Yellow = 2
White = 3
Gray = 4
Black = 5
Blue = 6
End Enum
Public Function IsDownTime(time As StateIntervalType)
Select Case time
Case StateIntervalType.Red
Return True
Case StateIntervalType.Green
Return False
Case Else
If IsNothing(DownTimeTable) Then
DownTimeTable = New System.Collections.Generic.List(Of Integer)()
Dim val As String
For Each val in Report.Parameters(DownTimeParameterName).Value
DownTimeTable.Add(CInt(val))
Next
End If
Return DownTimeTable.Contains(CInt(time))
End Select
End Function
REM ---------------------------------
Public Function FormatTimeInterval(ByVal TimeInSeconds As Integer) As String
Dim Hours, Minutes, Seconds As Integer
Hours = Math.Floor(TimeInSeconds / 3600)
Minutes = Math.Floor((TimeInSeconds Mod 3600) / 60)
Seconds = TimeInSeconds Mod 60
Return FormatString("{0}:{1}:{2}", Hours.ToString(), Minutes.ToString("00"), Seconds.ToString("00"))
End Function
REM ---------------------------------
Public Enum AggregationType
Hourly = 0
Daily = 1
Monthly = 2
End Enum
Public Function GetAggregationType(ByVal StartDate As DateTime, ByVal EndDate As DateTime) As AggregationType
Dim Result As AggregationType
Dim TimeDiff As TimeSpan
TimeDiff = EndDate - StartDate
If (TimeDiff.TotalDays > 32) Then
Result = AggregationType.Monthly
ElseIf (TimeDiff.TotalDays > 1) Then
Result = AggregationType.Daily
Else
If (CInt(Report.Parameters(DataAggregationParameterName).Value) = 0) Then
Result = AggregationType.Hourly
Else
Result = AggregationType.Daily
End If
End If
Return Result
End Function
Home