Deployment Solution

Ever wish you could quickly tell what jobs you may have scheduled on your Deployment Solution (DS)? Or maybe how many WOL proxies you have.

We were in the same situation.

In the starting stages of using DS it was not too much of an issue, we had maybe 20 jobs. But as time went on the number of jobs grew. I imagine we now have well over 200 jobs to install software and perform administrative tasks on our desktop systems. We also have about 6 people who may schedule jobs at any time.

I needed reporting for DS badly. Luckily we have Notification Server (NS) and DS in our environment. I was able to create a couple basic SQL queries to accomplish this task. I am by no means a SQL guru and I'm sure my queries could be better formed, or optimized, but they do the trick.

Here is my SQL query to find jobs that are schedule in the future for DS on the same box as NS. If it is on a different SQL server the query would have to be modified to point to the correct SQL server.

declare @datevar datetime    
Select @datevar = getdate()    
    
SELECT DISTINCT    
	T1.[Name] AS 'Computer Name',   
	T2.[Start_Time] AS 'Job Start Time',    
	T3.[Name] AS 'Job Name'   
FROM    
	Express.dbo.[Computer] T1    
	JOIN Express.dbo.[Event_Schedule] T2 ON T1.[computer_id] = T2.[computer_id]     
	JOIN Express.dbo.[Event] T3 ON T2.[Event_id] = T3.[Event_id]    
    
WHERE    
	T2.[Start_Time] > @datevar    
    
ORDER BY    
       T2.[Start_Time], T1.[Name]

Here is my SQL script to find all of our WOL proxies. Again, this query is setup for NS and DS on the same SQL server.

SELECT  
     T2.[computer_name] AS "Computer Name", 
     T3.[ip_address] AS "IP Address", 
     T3.[group_name] AS "Group"  
FROM  
    express.dbo.[aclient_prop] T1 
    JOIN express.dbo.[computer] T2 ON T1.[computer_id] = T2.[computer_id] 
    JOIN express.dbo.[computer_display] T3 ON T1.[computer_id] = T3.[computer_id]  
WHERE  
    T1.[wol_proxy] = 1  
ORDER BY 
    T3.[ip_address], T2.[computer_name]
    

To create a report in NS using the above queries open your Reports tab. Navigate to the foler where the report should reside, User Defined for example. Right mouse click and choose New -> Report.

Click to view.

Give a report name and description. Choose Enter SQL Directly then put one of the above queries into the area for the SQL statement and click Finish.

Click to view.

You should now be able to run your new report for DS.

Click to view.