Connecting Altiris Reports to SEP 11 Data

Symantec Endpoint Protection version 11 is a very powerful system, with nice reporting capabilities.  Altiris Notification Server 6 is also a very powerful system, with excellent reporting capabilities.  Those of us who are familiar with Altiris reporting know that getting data from the NS is a breeze once you have your data sets down.  This documentation will go through creating a SQL connection from the Altiris database to the SEP11 database (via Linked Servers).   This will allow SEP data to be reported on through the NS console.  This document assumes both Altiris 6 and SEP 11 are running on Microsoft SQL Server 2005 (earlier versions may not work; newer versions may have different steps).  NS7 can probably query other databases with Linked Servers, but I have not tried it yet.  You’ll need to be familiar with Microsoft SQL Server Management Studio (and have proper SQL permission to make dbo changes).  If you do not have SQL permissions, open a ticket with your DBA’s to make the changes ;) .  Be aware that making changes to your SQL database could seriously destroy your data.  Make sure you test first in your development environment, then backup production prior to making changes. 

FYI - click the thumbnails to open the original screenshots in a new window.
 

1.       First, create a new login on the SEP server database – Open Microsoft SQL Server Management Studio and connect to the main SEP management server database.  Expand Security, right-click Logins > New Login...
 

2.       In the “Login – New” window, on the General page, enter your Altiris service account credentials (domain\ID).  This will ensure that by default, the service account used to run Altiris will have read-only access to SEP data.
 

3.       Click the Server Roles page, and make sure “public” is checked

 

4.       Click the User Mapping page. Put a check next to the “sem5” database (or whatever your default SEP database is named), and type “dbo” in the Default Schema.  Make sure “public” is checked in the role membership at the bottom:

 

5.       Click the Status page, and make sure Permission is set to “Grant,” and Login is set to “Enabled.”  Then click OK at the bottom.  Your Altiris service account should now have read-only access to the SEP database.
 

6.       Now you have to connect the Altiris database to the SEP database.  In SQL Server Management Studio, disconnect from the SEP server database, and connect to your Altiris server database.  Once connected, expand Server Objects, right-click Linked Servers > New Linked Server

 

7.       Under Linked Server, enter the SEP server name. Choose “SQL Native Client” for Provider.  Type “SQLServer OLEDB Provider” for Product Name.  Type the SEP server name under Data source, and type “sem5” for catalog (or whatever your default SEP database is named)

 

8.       Click the Security page, then click the “Be made using the login’s current security context” option

(Since you granted read rights on the SEP database for the Altiris service account, checking this option means when a report is run from Altiris on SEP, it will pass the service account credentials.)
 

9.       Click OK to save the connection
 

10.   Now you can test the connection. Open the Altiris NS console and create a new report.  Click the option to “Enter SQL Directly.”
 

11.   Paste this line in the SQL statement window (edit your server name):

SELECT TOP(10) * FROM [SERVER_NAME].[SEM5].[dbo].[VIRUS]
 

12.   Click the “Test” button to see if the connection works
 

13.   If you see 10 populated lines, then your connection works and Altiris can read data from the SEP database!

 

14.   If you’re familiar with writing SQL queries, you can write reports and tie SEP data to Altiris data. Just FYI, when pulling data from SEP tables, you’ll have to prefix them in a way so SQL knows to connect via Linked Servers. Take the above query we used to test...the prefix is broken down like this:

 

The next article will be on creating basic SEP queries and report examples.