Using NS to Create Reports for DS

Do you have a Deployment Server that people just need to see status information on? We had the need for our Desktop Technicians to check the status of certain jobs in Deployment Server.

I came up with a way to use reporting in Notification Server (which they are all used to using for other reports) to give them all a view into the Deployment Server without giving them access to the Console. This process has many uses and below I will show how to set it up and the reports we built for our technicians.

The Problem

We have a process in place that joins new computers to the domain and runs custom scripts on them as needed (see the Juice article Auto Configuring New Computers for the details on this process). There became a need to have a view into this process by a large number of people. Since we did not want to have all of these people looking at the Deployment Server directly we looked for a reporting option.

All of these people were already used to using the reports in the Notification Server for all of their other daily activities. So I set out on a path with the goal of giving them the deployment status they needed to see in a reporting format that they were used to. I also wanted to make this easy on myself and not have to maintain a second reporting tool.

Since the Notification Server and Deployment Server both use a Microsoft SQL database I realized that I could use a feature of SQL called linked Servers. This feature enables SQL Server to execute commands against olE DB data sources on remote servers.

There are a large number of data sources that you can use as a linked server. Sources ranging from Excel spreadsheets to Oracle and SQL databases are all candidates (although I wouldn' t suggest trying to store much data in Excel). Just about anything that will accept an ODBC connection will work as a data source or linked server.

The benefit to using a linked server is that you can create queries and reports against both data sources without having to replicate the information between the two databases.

This reduces the size of the data you are storing and still allows you to access both datasets at the same time and join them together in the same query. For a full definition and details on using linked Servers in SQL server see the Microsoft Technet or MSDN articles (http://msdn.microsoft.com/en-us/library/ms188279.aspx).

The Solution

In our environment all of our Altiris database servers are running on Microsoft SQL Server 2005 so all my examples will be with the toolsets provided in that environment. This same process can be done with previous versions of SQL Server but the settings will be slightly different.

The first step that needs to be done is establishing the link between the two SQL servers. The steps below will walk you through the basics of creating this link. Some steps may be slightly different based on the security settings you have in place for your environment.

1. Open up Microsoft SQL Server Management Studio.
2. Connect to your Notification Server' s Altiris database.
3. Expand Server Objects > linked Servers
   
   

   Click to view.

4. Right click on linked Servers and choose New linked Server
5. For the linked Server name enter in the name of your Deployment Server
6. Under Server Type select the radio box SQL Server
   
   

   Click to view.

7. Click on Security in the left hand column.
8. Click on the Add button to add a new remote server login mapping.
9. In the Local Login column, choose the Domain Account that your Notification Server is using for its Service account (this might vary based on how you have your SQL Security setup).
10. For the Remote User and Remote Password column enter in a user account that has access to the Deployment Servers database. This can be a local SQL account.
11. Select the radio box at the bottom of the screen called "Be made using the login' s current security context."
    
    

    Click to view.

12. Click the Ok button and the linked server is now created.

You should now have a link connecting the two database servers. Now let's test out the connection and verify that it is working.

1. Click on the New Query button in Management Studio
2. In the Query window enter in the below SQL code:
   

   SELECT * FROM servername.express.dbo.computer
   
   

3. Execute the query and if everything is setup correctly you will get the contents of the Computer table in the Deployment Server db.

Our connection is now setup and we have verified that it is able to connect to the Deployment Server db. We can now begin to create our reports using information in both the Notification Server db and the Deployment Server db. Below are some examples of reports that we have created that use information from both databases. These are just a sample of what we have done and how to get started. No doubt you will find many other uses for these reports as well.

Here is a simple example of using a report in Notification Server that uses information from both databases. This will pull the computer name from Notification Server and the computer name and serial number from Deployment Server. While this is not much information it does show how to combine the two databases.

select vc.name, c.name, c.serial_num
from vComputer vc
join DeploymentServer.express.dbo.computer c on vc.name = c.name

The key to connecting to this remote Deployment Server db is to use the full name of the database object. In this example we used the Server name, db name, object owner, and the table name. All of these are required to be able to be able to make this remote connection.

I mentioned earlier about a Juice article entitled Auto Configuring New Computers. This is the report that we use as part of that process. Since we use Deployment Server to configure new computers that are coming onto the network we needed a way to show their status.

The below code uses a parameter for Service tag or serial number allowing for PCs to be searched on based on their serial number. It will also show the status of their assigned Configuration job and what computer name that job is going to assign to the PC.

If you want to see the status of all jobs then remove the "e.name like Reconfigure%" from the Where clause.

The Case statement is in there to clean it up a bit since the "Sending Wake-On-Lan signal" seems to just confuse people about what is going on.

SELECT c.Name As 'Current Name', c.[Domain_Name] As 'Domain', c.serial_num As 'Serial Number', ci.Computer_Name As 'Assigned Name',   
	CASE es.status  
		WHEN 'Sending Wake-On-Lan signal...' Then ''  
		ELSE es.status  
	End As Status  
 FROM DeploymentServer.express.dbo.computer c   
join DeploymentServer.express.dbo.config_item ci on c.computer_id = ci.target_computer_id  
join DeploymentServer.express.dbo.config_task ct on ci.config_set_id = ct.config_set_id  
join DeploymentServer.express.dbo.event_schedule es on c.computer_id = es.computer_id and ct.event_id = es.event_id  
join DeploymentServer.express.dbo.event e on ct.event_id = e.event_id  
WHERE e.name like 'Reconfigure%' and c.[serial_num] liKE '%SERVICETAG%'   
order by c.name  

In Summary

These are just some of the examples that show what can be done with SQL Server. I' m sure you will also find many other uses for this type of reporting. This is a great way to have a single interface into all of your Altiris servers.

Some other ideas might be to have a report that shows jobs that are scheduled to run on any of your Deployment Servers and also ties back into your Asset db to group the list of computers by their location. For environments that have multiple Deployment Servers this is also a great solution to have a single view into what all of them are doing.

While it was not discussed here there is also a plug-in from Altiris called Deployment Solution. It is installed onto the Notification Server and gives you a limited management view into all of your Deployment Servers.

By installing this solution some of this same information is replicated directly into the Notification Server db. Depending on what type of reports you need and what your management needs are that may provide you with enough information. In our case it was not enough and this linked Server solution has proved to be much more valuable.

Have fun writing some of your own reports and I would love to hear back on what type of reports or other similar solutions you are using in your company.