Video Screencast Help

Handy Software Delivery Report with Collection Filter

Created: 26 Mar 2009 | 3 comments
Language Translations
KSchroeder's picture
+37 37 Votes
Login to vote

After being overall disappointed with the built-in Software Delivery execution reports, I came up with the following.  It seems to execute fairly quickly and has some decent filters in it.  It also demonstrates how to include a Collection filter and various parameters.  I'll paste the SQL query and attach the actual report .XML file too so you don't have to re-create all the parameters.  Note that the way it is currently written only displays the most recent execution of any Program within a Package; we don't typically include multiple Programs in our SWD packages, with some exception.

select distinct vc.[Name] AS 'Computer Name', vc.[Domain],                 
swd.[LoggedInUser] AS 'Logged On User',                       
swd.AdvertisementName AS 'SWD Task Name',                       
swd.PackageName AS 'SWD Package Name',                       
max(swd.Start) AS 'Start Time', swd.[End] AS 'End Time', swd.[ExecutionNumber] AS '# of Runs',                    
datediff(ss, swd.[Start], swd.[End]) AS 'Run Time (s)',                   
swd.Status, swd.ReturnCode AS 'Exit Code',               
FROM Inv_Aex_SWD_Execution_Summary swd                  
RIGHT OUTER JOIN vComputer vc                      
ON vc.Guid = swd.[_ResourceGuid]                      
INNER JOIN CollectionMembership cm                    
ON vc.Guid = cm.ResourceGuid                    
AND cm.CollectionGuid = '%_Collection%'    
WHERE 1 = 1           
AND swd.PackageId = %_PackageId%             
AND swd.Status IN (%_Status%)            
AND swd.[Start] >  %_StartTime%    
GROUP BY vc.Name, vc.Domain,      
swd.AdvertisementName ,                       
swd.PackageName, swd.[ExecutionNumber],              
swd.[Start], swd.[End],                   
swd.Status, swd.ReturnCode,               
ORDER BY max(swd.Start) DESC, vc.[Name] ASC

Comments 3 CommentsJump to latest comment

Eshwar's picture

Thanks for the post.

I'm creating a report that displays computers/Users depending upon the following criteria:

1. Region
- Asia-Pacific
- North America

2. City
- Delhi
- Mumbai
- Bangalore
- St Louis
- NewYork
- Chicago

User has to select "Region" and "City" choice will should be filtered by Region. So if i select "Asia-Pacific" from "Region", [Delh,Mumbai,Bangalore] should be displayed in the "City".

Thank you for your time on this.




Login to vote
KSchroeder's picture

Hey Eshwar,
I know what you're getting at...but I haven't yet figured out how to make it happen!  I think there are some "out of the box" reports that work like this, i.e. to filter a second parameter based on the value of the first parameter.  Let me know if you figure it out :)

Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

Login to vote
SanjayDeo7x's picture

Software Delivery Report with Collection Filter is v.good report. Please let knon how to add collection filter

to any report or any custonmise report.

Login to vote