Client Management Suite

Hello, I need assitance creating a simple report.

I only need 3 columns:

Computer Name/Collection/Compliance %

I'd like it outputable to Excel or CSV format.

Can any of you help?

Thanks.

Crankyozfranky

DB structure is quite different.

1) The built in compliance reports already do much of what you ask, and you can sort them, output them to excel, and cut out the data you don't want.
2) If you edit and look at the SQL being presented, it is fairly complex, it's not as 'simple' as it seems.
3) The built in reports can be 'copied to the clipboard' and pasted to excel, and even be set to run (and save results) on a set schedule. They cannot, at least in NS6, be set to automatically export to a given file type (although NS7 does add some features). You may want to take a look at IT Analytics or if money is a concern, learning how to use SQL 2005 reporting features. Both are designed to help those who want custom reports or features, and may not have a strong SQL background.

a computer can be a member of an unlimited number of collections, so you would have to hard-code the collection you care about into the report somehow.

6

Jim, thanks for the input...we currently do just what you've suggested--spit out default compliance reports, copy to Excel, and sort/filter. This works but it doesn't provide a column for Collection...I believe that Kyle (who also responded) made the crucial comment that because a computer (in my case, servers,) can belong to several Collections, how do you get the report to spit out the collection you are after. In my case, we have "Primary" custom inclusion collections each server belongs to. This would be what we would like to show up as output for a column named "Collection". I'm completely SQL, scripting illiterate, but we do have a DBA team we could pass code to. Thanks again. -Crankyozfranky

Kyle, that's a bit of a cat chasing its own tale...Short of simply creating multiple compliance reports and filtering by Collection, do you have any suggestions?...if it comes to that, what are my options for automating the report into a CSV/Excel format? Thanks heaps...-Crankyozfranky

You can clone the report, then export it.  At that point you can edit the .XML file (be sure to use an XML editor, like CrimsonEdit or TextPad) to change the pre-defined collection GUID it specifies to use your other "Primary" collections.  Rinse and repeat for the other collections.

We have to have our entire environment patched within 10 days of a patch being released, so I get to deal with this issue on a monthly basis.

We patch our environment the Thursday after patch Tuesday and run through the following Friday.  There are different schedules on each day to accommodate various applications and maintenance windows, i.e. patch at 8pm on a Saturday and reboot at 10pm vs. patch at midnight on Saturday and reboot at 2am.  We also have collections set up for servers that need manually patched (which has a 1/1/1980 patch policy so Altiris can still be leveraged to patch on demand)

Next, we have daily reporting collections that contain a cumulative list of everything that's patched so far.  The Saturday collection contains all Saturday patch collections, all Friday patch collections, all Thursday patch collections, etc., and the Sunday collection contains all of these plus the Sunday patch collections.

We have a collection report that lets you pick a parent collection and will report on all machines in that collection and what sub-collection they belong to:

select vc.[name] as 'Computer name', i.[name] as 'Collection'                 
from vcollection c          
join collectionincludecollection cic  on cic.collectionguid = c.guid          
join Collectionmembership cm  on cm.collectionguid = cic.subcollectionguid          
join vitem vc on vc.guid = cm.resourceguid          
join vitem i on i.guid = cic.subcollectionguid          
where cic.collectionguid = '%Collection1%'       
group by i.[name], vc.[name]    

I use that to know who's in what collection, then just run the canned Compliance/Vulnerability by Computer - Details report against the corresponding daily collection so I can get an accurate view of patch compliance levels only on the machines that should have patched.

It's still a solution that will require manual work on your part, but I've tried to dig into the canned reports (which are in the DB as stored procedures) and I think the SQL driving those things would make even the most seasoned DBA cry! :)