myITforum.com Community Forum myITforum.com Community Forum

Home  Forums  Blogs  Live Support chat  Search Articles  Wiki  FAQ  Email Lists  Register  Login  My Profile  Inbox  Address Book  My Subscription  My Forums 

Photo Gallery  Member List  Search  Calendars  FAQ  Ticket List  Log Out

All Forums RSS Feed Subscription:


  


Patch Report Issues \ Don't Know who is not patched

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
  Printable Version
All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting >> Patch Report Issues \ Don't Know who is not patched Page: [1]
Login
Message << Older Topic   Newer Topic >>
Patch Report Issues \ Don't Know who is not patched - 8/22/2008 1:24:14 PM   
michael.zimmer

 

Posts: 25
Score: 0
Joined: 4/20/2007
Status: offline 		I am using the following report to get the Patch compliance for a certain collection, but I need it to drill down on the tabs that are not a 100% compliant to find out what system I need to patch.  Anyone have any idea how to do this or know of a software company that might?

SELECT ID AS [BulletinID],Title,
sum(case when LastStateName='No Status' then 1 else 0 end) as 'No Status',
sum(case when LastStateName='Waiting' then 1 else 0 end) as 'Waiting',
sum(case when LastStateName='Retrying' then 1 else 0 end) as 'Retrying',
sum(case when LastStateName='Failed' then 1 else 0 end) as 'Failed',
sum(case when LastStateName='Uninstalled' then 1 else 0 end) as 'Uninstalled',
sum(case when LastStateName='Preliminary Success' then 1 else 0 end) as 'Preliminary Success',
sum(case when LastStateName='Reboot Pending' then 1 else 0 end) as 'Reboot Pending',
sum(case when LastStateName='Install Verified' then 1 else 0 end) as 'Install Verified',
100 * sum(case when LastStateName='Install Verified' then 1 else 0 end)/Count(*) as '% Compliant', LocaleID, UpdateID
FROM v_GS_PatchStatusEx
JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_GS_PatchStatusEx.ResourceID
WHERE v_FullCollectionMembership.CollectionID = @CollID
GROUP BY ID,Title, LocaleID, UpdateID
HAVING Count(*) > 10
ORDER BY BulletinID DESC
Post #: 1
RE: Patch Report Issues \ Don't Know who is not patched - 9/15/2008 5:49:24 AM   
jschoone

 

Posts: 359
Score: 25
Joined: 4/10/2004
From: Norway
Status: offline 		Hi!!

First you have to be sure all parameters are available to the drilldown report. That means that the parent report must include the collectionID in the returned records too!

SELECT ID AS [BulletinID],Title,
sum(case when LastStateName='No Status' then 1 else 0 end) as 'No Status',
sum(case when LastStateName='Waiting' then 1 else 0 end) as 'Waiting',
sum(case when LastStateName='Retrying' then 1 else 0 end) as 'Retrying',
sum(case when LastStateName='Failed' then 1 else 0 end) as 'Failed',
sum(case when LastStateName='Uninstalled' then 1 else 0 end) as 'Uninstalled',
sum(case when LastStateName='Preliminary Success' then 1 else 0 end) as 'Preliminary Success',
sum(case when LastStateName='Reboot Pending' then 1 else 0 end) as 'Reboot Pending',
sum(case when LastStateName='Install Verified' then 1 else 0 end) as 'Install Verified',
100 * sum(case when LastStateName='Install Verified' then 1 else 0 end)/Count(*) as '% Compliant', LocaleID, UpdateID, CollectionID
FROM v_GS_PatchStatusEx
JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_GS_PatchStatusEx.ResourceID
WHERE v_FullCollectionMembership.CollectionID = @CollID
GROUP BY ID,Title, LocaleID, UpdateID, CollectionID
HAVING Count(*) > 10
ORDER BY BulletinID DESC

The new report based on the collectionID and the UpdateID should list all resources in the collection that have a patch state other than 'Install verified'. It should look something like this:

SELECT DISTINCT ID AS [BulletinID],Title, Language, InfoPath
FROM v_ApplicableUpdatesSummaryEx
WHERE UpdateID = @PatchID
SELECT NetBios_Name0, AD_Site_Name0, User_Name0, Operating_System_Name_and0, LastStateName
FROM v_GS_PatchStatusEx
JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_GS_PatchStatusEx.ResourceID AND LastStateName!='Install Verified'
LEFT JOIN v_R_System ON v_R_System.ResourceID = v_GS_PatchStatusEx.ResourceID
WHERE v_FullCollectionMembership.CollectionID = @CollID AND v_GS_PatchStatusEx.UpdateID = @PatchID
ORDER BY Netbios_Name0

and has two prompts; one for the collectionID and one for the Software Update ID.

Hope this helped to create a report about the client status.

Jan

(in reply to michael.zimmer)
Post #: 2
RE: Patch Report Issues \ Don't Know who is not patched - 9/15/2008 10:02:31 AM   
michael.zimmer

 

Posts: 25
Score: 0
Joined: 4/20/2007
Status: offline 		Jan,

You are AWESOME!!!!  Thanks for the help.  I wish these SMS reports would show so much more.

Thanks Again, I owe you one.  ; )

(in reply to jschoone)
Post #: 3
RE: Patch Report Issues \ Don't Know who is not patched - 9/16/2008 11:19:27 AM   
MeenEnta


Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline 		I hope you do not mind, and I know that was your intension..I changed couple of things.

SELECT DISTINCT ID AS [BulletinID],Title, Language, InfoPath, QNumbers, DatePosted, InstalledCounts
FROM v_ApplicableUpdatesSummaryEx
WHERE UpdateID = @PatchID
SELECT NetBios_Name0,
User_Name0,
Operating_System_Name_and0, AD_Site_Name0
FROM v_R_System
SELECT LastStateName
FROM v_GS_PatchStatusEx
JOIN v_FullCollectionMembership ON
v_FullCollectionMembership.ResourceID = v_GS_PatchStatusEx.ResourceID
AND LastStateName!='Install Verified'
LEFT JOIN v_R_System ON
v_R_System.ResourceID = v_GS_PatchStatusEx.ResourceID
WHERE v_FullCollectionMembership.CollectionID = @CollID
AND v_GS_PatchStatusEx.UpdateID = @PatchID
ORDER BY Netbios_Name0
 
and here are the Prompts SQL
for the CollID
begin
if (@__filterwildcard = '')
select CollectionID, Name from v_Collection order by Name
else
select CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end
 
and for the PatchID
begin
if (@__filterwildcard = '')
select UpdateID,Title from v_ApplicableUpdatesSummaryEx order by Title
else
select UpdateID,Title from v_GS_PatchStatusEx
WHERE UpdateID like @__filterwildcard
order by Title
end

Jan thank you for excelllent work.
 
 

(in reply to michael.zimmer)
Post #: 4
RE: Patch Report Issues \ Don't Know who is not patched - 9/16/2008 11:28:47 AM   
michael.zimmer

 

Posts: 25
Score: 0
Joined: 4/20/2007
Status: offline 		MeenEnta

I like that as well, I think I might combo them.  Thanks Again for you help.

(in reply to MeenEnta)
Post #: 5
Page:   [1]
All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting >> Patch Report Issues \ Don't Know who is not patched Page: [1]
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts



  
Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI

0.266