Reporting Group

 View Only

Combining Data from SEP and DLP Into a Single View 

Feb 26, 2013 05:22 PM

This article will discuss how to combine SEP and DLP data within IT Analytics by leveraging Microsoft Report Builder to create and publish a SQL Server Reporting Services report. For the purposes of this exercise, we will utilize some of the out-of-the-box functions in Report Builder to link data from multiple cubes into one report.

Report Builder is a component of SQL Server Reporting Services that allows ad-hoc reporting functionality, enabling end users to build their own reports and charts. Users can then publish these reports into Reporting Services where they can be accessed, viewed and incorporated back into IT Analytics alongside existing reporting.

NOTE: To complete this exercise, you should have IT Analytics already installed and configured, with both the Symantec Endpoint Protection and Data Loss Prevention Content Packs. Also, note that while the output produced by Report Builder is integrated with IT Analytics, the tools and subsequent query language behind it are separate Microsoft entities and are thereby outside the default capabilities of the IT Analytics product itself.

Creating a Matrix Report 

  1. Within the Symantec Management Platform console, navigate to: Settings > Notification Server > IT Analytics, then click on Reports in the left menu tree.
  2.  Click the Report Builder tab and then the Launch Report Builder button. 

  1. Allow a few minutes for the application to load. Note that depending on which version of SQL Server you have, you may have a different version of Report Builder. This example covers Report Builder 3.0, which comes standard with SQL Server 2008 SP2 or higher. Note that while SQL Server 2005 meets the minimum prerequisites for installation of IT Analytics, it will only include Report Builder 1.0. If possible, Symantec strongly recommends using SQL Server 2008 SP2 or higher to take advantage of new features included in Report Builder 3.0 for a more robust custom report authoring experience. 

  1. From the Getting Started screen, select Table or Matrix Wizard to create a new report.

  1. In the next step you will be prompted to choose a dataset. Make sure the Create a dataset radio button is selected and click Next.

  1. The next step will prompt you to choose a connection to a data source. A data source is the repository where the data for the report is stored. In the case of IT Analytics, the data is stored in the Microsoft Analysis Services Database specified when IT Analytics was installed. To create a new data source, click the Browse button.

  1. Navigate to the ReportServer/IT Analytics folder on the server that houses SQL Reporting Services. Within that folder there will be a data source called ITAnalytics. Select this as the data source for the report and click Open.

  1. Verify that the data source you just browsed to is displayed on the next screen of the wizard.

  1. Ensure the connection to the data source is valid by clicking the Test Connection button in the lower right of the wizard. You should see a popup message that says the test succeeded.
  2. Click Next and you will be prompted to design a query, which will make up the data set for the report. 

  1. Click the  button toward the top of the window and select the DLP Agent Status cube.

  1. Expand Measures and Agents, then drag the Incidents Count into the main query window.

  1. Expand the DLP Agent attribute and drag Agent – Name and Agent - Version into the query window. Then expand the DLP Agent Last Connection Date attribute and drag DLP Agent Last Connection Date – Date into the window.

  1. Click Next to complete the creation of the data set.
  2. The next step will prompt you to arrange the fields to display properly in the table. Drag Incidents_Count to the Values window and drag Agent___Name, Agent___Version and DLP_Agent_Last_Connection_Date___Date to the Row Groups window. When completed, click Next.

  1. The next step will prompt you to choose the layout of the report. Uncheck the Show subtotals and grand totals box and click Next.

  1. The next step will prompt you to select a style for the report. Choose a color scheme you prefer and click Finish.

  1. You should see a sample table on the report canvas. The data source and data set that display on the left navigation have already been created for you via the wizard. Rename the title of the report to DLP Incidents and SEP Alerts by Version.

  1. Resize the font of the title so that it fits within the given area. Also, widen the columns of the table so that you can read the column headers. You can do this in the same way you would with Excel, simply click on the line between the columns, and when a grey bar appears at the top of the table, then expand by dragging the columns.

  1. Expand the Datasets folder and right-click on DataSet1 which was created automatically by the wizard (this displays in the Report Data pane on the far left) and then click on Dataset Properties.
  2. Rename the dataset as “DLPIncidents” which will help to differentiate it from the new data set we will create next to pull in the SEP Data. Also notice the query for the dataset which has been written entirely in the background by going through the wizard. Click OK to close the dataset properties window.

  1. We will now create a new dataset to pull in SEP data. Right-click on Datasets in the Report Data pane and select Add Dataset.

  1. In the Dataset Properties window, name the dataset “SEPVersion” and select to Use a dataset embedded in my report, then select ITAnalytics in the Data source dropdown.

  1. Click the Query Designer button and the query designer window will appear. Change the cube to create a query on by clicking the  button toward the top of the window and select the SEP Clients cube.

  1. Expand the Measures group and then the Client folder. Drag Client Count to the main window.

  1. Expand the Computer attribute and drag the Computer – Computer Name field into the query window. Then expand the Virus Definition attribute and drag the Virus Definition - Version field into the window. Finally, expand the Last Checkin Date attribute and drag the Last Checkin Date – Date field into the window.

  1. Click OK to close the Query Designer window and click OK again to close the Dataset Properties window. You should see both datasets listed in the Report Date pane.

  1. Place your cursor over the right edge of the report canvas and stretch the width of the white area out to about 8 inches.
  2. We now need to create two additional columns in our table to display the virus definition version and last checkin date for each computer. To add a column, right-click the grey column header that appears when you click into the DLP Agent Last Connection Date field, then select: Insert Column > Inside Group - Right.

  1. Click into the new column header and type “Virus Definition Version” then add another column to the right and label that one SEP Client Last Checkin Date.

  1. Right-click on the data cell below Virus Definition Version header and select Expression

  1. Report Builder has several pre-defined functions built into it that can be leveraged to form an expression and extend report functionality. This works much the same way functions work in Excel, where users need to understand the format of specific functions and the arguments expected to be able to use them accordingly. For this example, we will utilize the Lookup function to tie data from the two datasets together. In the Category column, expand Common Functions and click Miscellaneous, then in the Item column that appears select Lookup.

  1. Notice the description and example provided on the right hand side for the Lookup function. To tie the datasets together we need a common identifier that resides in both sets. In this example, “Agent - Name” (from the DLP Agents cube) and “Computer – Computer Name” (from the SEP Clients cube) will be used to signify the 1-to-1 relationship. Once that is established we can then add in the virus definition version for each computer to display in the report. To set the expression value for the Lookup function, type the following into the field above:

 

=Lookup(Fields!Agent___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Virus_Definition___Version.Value, "SEPVersion")

 

NOTE: The dashes “ - “ are replaced by “___” because spaces and dashes are not allowed in the expression. The expression window should now look consistent with the screenshot below. If it does, click OK to close the expression window.
 
  1. You should see an abbreviated place holder in that cell within the table, which represents the expression.

  1. We now need to add the next column for SEP client last checkin date. To do so, repeat steps 32 – 34 above. For the lookup expression value, we will match on computer name as we did in the previous column, and this time we will pull the Virus Definition – Version field. Once the expression window looks consistent with the string and screenshot below, click OK to close the expression window:

=Lookup(Fields!Agent___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Last_Checkin_Date___Date.Value, "SEPVersion")

  1. Before we run the report, we need to remove grouping that gets added by default to the matrix. If the grouping section does not already appear at the bottom of Report Builder, click the View tab and check the Grouping box.

  1. In the Row Groups section at the bottom, click on the downward facing triangle next to the DLP Agent Last Connection - Date field and select Delete Group, then opt to Delete group only. Do the same for Agent - Version.

  1. Finally, select all data cells (not headers) except for Agent Name and apply no color to them.

  1. We are now ready to preview the report and ensure the data has been tied together correctly. To preview the report, click the Run button at the top left.

  1. You should see virus definition version and SEP client last checkin date populated alongside the DLP data. Note that for computers where SEP information is missing, this reveals they do not have a SEP agent installed.

 

Adding a Measure and Linking the Report to the Console

  1. Select the Design button to go back to the Design view.

  1. Save this report in the IT Analytics folder and name it DLP Incidents and SEP Alerts by Version.
  2. We now need to add in SEP alert information, which resides in a different cube. Similarly to the previous topic, we will have to create another dataset for the alert information and add it to the matrix report. Repeat Steps 23 – 27 above to create another dataset called SEPAlerts. Use the SEP Alerts cube and drag in Alerts as the measure and Computer – Computer Name as the dimension.

  1. Click on the Incidents Count column header, then right-click on the grey bar that appears above and add a column to the right. Name that new column Alerts Count.
  2. Right-click on the data cell below the Alerts Count header and select Expression.
  3. We will once again use the Lookup function to tie the datasets together. For the common identifier use “Agent - Name” (from the DLP Agents cube) and “Computer – Computer Name” (from the SEP Alerts cube). To set the expression value for the Lookup function, type the following into the field above:

=Lookup(Fields!Agent___Name.Value,Fields!Computer___Computer_Name.Value,Fields!Alerts.Value, "SEPAlerts")

  1. Click the Run button again to view the report with live data.
  2. Verify that Alerts Count now displays with data. Note that some computers/agents may not have triggered SEP alerts, and therefore don’t display data.

  1. Go back into Design mode and click the Save button to finalize the report.
  2. To link this report into the Symantec Management Platform console open the console then navigate to the Reports > IT Analytics > Reports folder.
  3. Right-click on the Reports folder and select New > IT Analytics Report.

  1. In the Report Type dropdown box, select Report and then in the Report Name dropdown select the DLP Incidents and SEP Alerts by Version report. Then click the Add Report button.

  1. You should see a message saying that the report was added successfully.
  2. Refresh your browser and expand the Reports folder.
  3. Locate and select the report you just added and verify it displays as expected.

Statistics
0 Favorited
2 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Related Entries and Links

No Related Resource entered.