What is the best practice for writing a custom Patch Management Solution Report?
The following is a stepping stone to get the custom report going in the right direction:
The forums - http://www.symantec.com/connect/forums - is an excellent source for Q&A concerning Patch Reports. However, please review the following information, for it can be also be helpful.
Patch reports differ from most reports in the NS Console, for they are run from Stored Procedures (multiple SQL scripts), pulling from multiple tables, and therefore a collection cannot be generated from them.
Use the Altiris Profiler: Start/Programs/Altiris/Diagnostic/Profiler - Start Profiling (Green Triangle), run Patch Report (Refresh), and Stop Profiling (Red Square). View the data logged on all tabs and in the details window to show what Stored Procedures are accessing the information. Then select File/Export to desktop to save the data for later use.
Please use the following script to run against the Altiris Database, and 'plug-in' one of the Stored Procedures from the list that follows (or use one from the profiler if desired).
sp_helptext <Insert Stored Procedure>
These will open up the views and tables accessed with the stored procedures. Copy and paste into a text doc or a new query window.
Note: Writing custom reports is not supported by Symantec Corp.
Opening the Microsoft SQL Server Management Studio Server/Databases/Altiris/Programmability/Stored Procedures, and then down to the spPM<StoredProcedure> area is where the Patch Stored Procedures are listed. These can be run with sp_helptext also to see if there are any other views or tables that would be helpful.
Please view the attachement for a basic Patch Report that was based on the Compliance and Vulnerability by Computer (spPMWindows_ComplianceByComputer) for more information on how the report can be altered to show a more limited view of data.