Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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',               
vc.[Guid]         
              
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.[LoggedInUser],                       
swd.AdvertisementName ,                       
swd.PackageName, swd.[ExecutionNumber],              
swd.[Start], swd.[End],                   
swd.Status, swd.ReturnCode,               
vc.[Guid]                      
      
ORDER BY max(swd.Start) DESC, vc.[Name] ASC

Comments 3 CommentsJump to latest comment

Eshwar's picture

KSchroeder,
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.

Thanks,
Eshwar

Cheers,

Eshwar

+25
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 :)

Thanks,
Kyle
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.

+22
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.

0
Login to vote