Client Management Suite

I'm having a tough time, and I'm wondering if anyone might be able to help.  I'd like to design a better patch management for Windows report.  Unfortunately, the reports that are similar to what I want to use (Windows Compliance by Bulletin, Software Bulletin Details) use stored procedures, which I don't think are supposed to be modified.

Here's what I'd like to be able to do:

• Daily or weekly, or as often as I'd like, run the report
• The report can show all bulletins, but has parameters for Staged, Policies, Applicable, and Vulnerable
• I'd probably like to set the defaults for policies to 0, applicable to >0, the other two to % (any)
• The report would tell me what I need to stage and what I need to add to a policy by highlighting bulletins that are not staged or have no policies, but do apply to computers

Currently, I accomplish this by exporting the Software Bulletins Detail to a spreadsheet, then sorting to remove any where 'Applies To' is equal to 0 (I do not need to stage something which does not apply to anyone in my environment), then sorting to remove anything where Policies is greater than 0 (typically, a bulletin is approved for test, then modified for test+pilot, then modified for full release, so seeing a '1' here means I've done my job).

At this point, I sort by Staged, and for all where Staged is False, I begin staging.  With the bulletins staged, I then find this entire list (Applies to >0 but Policies=0) manually by scrolling through the entire Software Bulletin Details report.

If I had the above report, I could immediately run the report to find Staged=False, Applies>0, Policies=0, select everything, right-click, and Stage, then refresh the report with Staged=True, Applies>0, Policies=0, right-click, and create a Software Update Policy.

How have others solved this challenge?

I'd also like to see something like you describe.  Really if we could just add policies to the current "Windows Complaince by Bulletin" would be a hugh improvement!

I agree that would be VERY helpful.

While on the topic of patch reports I'd like to throw this out there. Why can't I have a report that will show me every patch that was installed during the last maintenance window? I used to use the restart status report in 7.0 and it'd show me everything installed, whether it is a pending restart or not. I didn't really care if it needs a restart or not as we force PC reboots on Friday nights, but at least I could see exactly what was installed and on who'c PC the night before.

This report was changed in 7.1 and now ONLY shows updates that are pended restart. If there was a bulletin installed that doesn't require a reboot then its not listed. I'd kill to have the report the way it was in 7.0 or another report listing updates installed and to who.

This comes up every patch week when I release patches to clients after testing them with my testing group for a week. Something happens to someones PC and they want to know what patches they just had installed.

Maybe someone has come up with ways around this.

This is something that could be useful to me as well so I'm working on it now... should be ready today!

Dan

The joys of version 7!

So, I've cloned the stored procedure, added the new parameters. Cloned the report and pointed it to the new sp. I can test within design view with default values for the new params... everything works fine...

EXCEPT! I can't get the new the new parameters to display from the gui of the cloned report...

I've spent a couple of hours now tyring to get them to show up...

I've attached a screen shot where you can see the new Staged param and it's default value, but for the life of me I can't get a drop down list to display so the user can enter values!

Any suggestions?

Here's a screenshot of the Staged parameter:

So... this became a bit of a challenge for me, obsessive person that I am.

I had to edit the xml directly for my new parameters to be shown in the GUI, go figure.

Anyway, it's done. I didn't add Vulnerable however because that one looked like it would take some time!

So what we have are 3 new parameters:

Polcies -- the criteria in the sp is >, so if you enter 0, only those with 1 or more policies will show up.

                I've made the default -1 so that everything shows up.

Applicable -- same thing with this one. enter 23 and only bulletins that have a number greater than 23 

                     in the Applies To column will show

Staged --      this is just a True or False

I've attached the new SP and the report. All you have to do is run the script in SQL Management Studio.

It will cretae a new SP called:

spPMCoreReport_SoftwareBulletinSummary_Dan 

you can change the name if you like (from within the script), just make sure you then edit the report to point to what you renamed it as...

I've commented the things I changed in the script as --new

Dan

Attachment(s)

Copy of Software Bulletin Details.xml   30 KB 1 version

CreateNewBulletinsScript.zip   2 KB 1 version

Shobud,

Maybe I'm missing something but since your report doesn't include Vulnerable how does it differ from the current standard reports?  The reason for adding the Vulnerable is that in ONE report you can see if an update is applicable (count) has been downloaded (staged), in a policy and if computers are vulnerable (Not Installed (Count)).

Seems with your report I still would not know if I even need to put a update into a policy as I don't know if any computers are vulnerable?

I do appreciate your obssesion with it though, nice job!

Hey Rick,

My report differs from the original in that you have extra parameters to filter on which I though would help the OP's workflow.

Adding Vulnerable is fairly difficult because the info needed to make that determination is not contained in the  spPMCoreReport_SoftwareBulletinSummary stored procedure.

Now I took a look at spPMWindows_ComplianceByBulletin and attempted to place the relevant parts into my custom sp but the results weren't 100% accurate when cross referenced against the Windows Compliance by Bulletin report. Also, adding that much more info to the report adds a lot more overhead!

Don't know if you've done much query writing in Altiris but you soon find out that unless you use the exact same joins, tables etc that one sp uses, chances are your results will be off, not by much, but they will be off and it's very difficult to trace the problem.

So short of dumping the entire spPMWindows_ComplianceByBulletin procedure into my version of spPMCoreReport_SoftwareBulletinSummary (which would just take too much time) I concede defeat :-)

EDIT: I've attached my attempt at adding Vulnerable for ayone that wishes to try their hand at it. As I said, the results are mostly accurate but I think in this case they have to be bang on... I've commented the section that is for Vulnerability. It's a variant of what is in spPMWindows_ComplianceByBulletin.

Dan

Attachment(s)

CreateNewBulletinsScript_0.zip   3 KB 1 version

Dan,

Yes I have a little experience with creating the reports using SQL and I imaged that there must be some major reason for not having such a report because to me it seems intuitive to include all the information in 1 report we have mentioned above.

Thanks for trying and explaining the issues!

Rick

Is it safe to assume this report was written for 7.0?  Because I'm getting no data from 7.1.