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 Status by Domain

 
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 Status by Domain Page: [1]
Login
Message << Older Topic   Newer Topic >>
Patch Status by Domain - 5/13/2008 2:29:34 PM   
jscott607

 

Posts: 210
Score: 0
Joined: 9/20/2005
Status: offline 		Hello everyone. I am trying to get together a web report that list patch status by domain. I have found several examples of reports that list the status across the entire enterprise but none that look at specific domains or ad sites. For example, management wants one report that only displays the domain and % failed. From there, a drill down report will display more details such as system names and which patches failed.

Below is an example that I have found. How would I get it to look up % failed by domain? Thank you for any assistance.

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
GROUP BY ID,Title, LocaleID, UpdateID
ORDER BY UpdateID DESC

< Message edited by jscott607 -- 5/13/2008 3:44:43 PM >
Post #: 1
RE: Patch Status by Domain - 5/13/2008 4:34:46 PM   
jscott607

 

Posts: 210
Score: 0
Joined: 9/20/2005
Status: offline 		I modified the report to look up by v_GS_Computer_System.Domain0. This gives me a detailed look at each domain. The only item I cannot figure out is how to display the % results for all domains on a single page. Basically, a domain would be listed followed by a total percentage of all updates that are out of compliance. Is this even possible?


SELECT     v_GS_PatchStatusEx.ID AS BulletinID, v_GS_PatchStatusEx.Title,
  100 * SUM(CASE WHEN LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / COUNT(*) AS '% Compliant',
  SUM(CASE WHEN LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS 'Install Verified',
  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'
                     FROM v_GS_PatchStatusEx INNER JOIN
                     v_GS_COMPUTER_SYSTEM ON v_GS_PatchStatusEx.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
WHERE (v_GS_PatchStatusEx.ID like 'MS%') and (v_GS_COMPUTER_SYSTEM.Domain0 = @id)
GROUP BY v_GS_PatchStatusEx.ID, v_GS_PatchStatusEx.Title, v_GS_PatchStatusEx.LocaleID, v_GS_PatchStatusEx.UpdateID
HAVING      (COUNT(*) > 10)
ORDER BY v_GS_PatchStatusEx.UpdateID DESC

(in reply to jscott607)
Post #: 2
RE: Patch Status by Domain - 5/13/2008 10:41:29 PM  1 votes
jnelson993


Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Well, if you're looking for a summary report to just pull the details by the domain, just pull out the other things of the query and just have your domain and the % Compliant something like this:
--SQL QUERY FOR SUMMARY REPORT - Patch Compliance By Domain
SELECT    
  cs.domain0 AS [Domain], 
  100 * SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / COUNT(*) AS '% Compliant'
FROM
  dbo.v_GS_PatchStatusEx AS pse
  INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS cs
     ON pse.ResourceID = cs.ResourceID
WHERE
  pse.ID like 'MS%'
GROUP BY
  cs.Domain0
ORDER BY
  cs.domain0

That report would link to the next report so when you clicked on a domain, it would pass the domain column from report 1 to the @Domain prompt in this report and you'd see the results for that domain...something like this:
-- SQL QUERY FOR INTERMEDIATE REPORT - Patch Status for a Specific Domain (by Update)
SELECT    
  cs.domain0 AS [Domain], 
  pse.ID,
  pse.Title,
  100 * SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / COUNT(*) AS '% Compliant',
  SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS 'Install Verified',
  SUM(CASE WHEN pse.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS 'No Status',
  SUM(CASE WHEN pse.LastStateName = 'Waiting' THEN 1 ELSE 0 END) AS 'Waiting',
  SUM(CASE WHEN pse.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS 'Retrying',
  SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS 'Failed',
  SUM(CASE WHEN pse.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS 'Uninstalled',
  SUM(CASE WHEN pse.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS 'Preliminary Success',
  SUM(CASE WHEN pse.LastStateName = 'Reboot Pending' THEN 1 ELSE 0 END) AS 'Reboot Pending',
  pse.LocaleID,
  pse.UpdateID
FROM
  dbo.v_GS_PatchStatusEx AS pse
  INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS cs
     ON pse.ResourceID = cs.ResourceID
WHERE
  pse.ID like 'MS%'
  AND cs.domain0 = @Domain
GROUP BY
  cs.domain0,
  pse.ID,
  pse.Title,
  pse.LocaleID,
  pse.UpdateID
ORDER BY
  pse.UpdateID DESC

and if you wanted to then see the details of a particular patch, you'd pass the domain, ID and UpdateID to the @Domain, @ID and @UpdateID prompt in this report to get the details...something like this:
-- SQL QUERY FOR DETAIL REPORT
SELECT    
  cs.domain0 AS [Domain],
  cs.name0 AS [Computer Name],
  cs.UserName0 AS [User Name],
  pse.ID,
  pse.Title,
  100 * SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / COUNT(*) AS '% Compliant',
  SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS 'Install Verified',
  SUM(CASE WHEN pse.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS 'No Status',
  SUM(CASE WHEN pse.LastStateName = 'Waiting' THEN 1 ELSE 0 END) AS 'Waiting',
  SUM(CASE WHEN pse.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS 'Retrying',
  SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS 'Failed',
  SUM(CASE WHEN pse.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS 'Uninstalled',
  SUM(CASE WHEN pse.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS 'Preliminary Success',
  SUM(CASE WHEN pse.LastStateName = 'Reboot Pending' THEN 1 ELSE 0 END) AS 'Reboot Pending',
  cs.Manufacturer0 AS [Make],
  cs.Model0 AS [Model],
  cs.TotalPhysicalMemory0 AS [Memory]
  pse.LocaleID,
  pse.UpdateID
FROM
  dbo.v_GS_PatchStatusEx AS pse
  INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS cs
     ON pse.ResourceID = cs.ResourceID
WHERE
  cs.domain0 = @Domain
  AND pse.ID = @ID
  AND pse.UpdateID = @UpdateID
GROUP BY
  cs.domain0,
  cs.name0 AS [Computer Name],
  cs.UserName0 AS [User Name],
  pse.ID,
  pse.Title,
  cs.Manufacturer0 AS [Make],
  cs.Model0 AS [Model],
  cs.TotalPhysicalMemory0 AS [Memory]
  pse.LocaleID,
  pse.UpdateID
ORDER BY
  cs.domain0,
  cs.name0,
  pse.updateID DESC



_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jscott607)
Post #: 3
RE: Patch Status by Domain - 5/14/2008 4:18:04 PM   
jscott607

 

Posts: 210
Score: 0
Joined: 9/20/2005
Status: offline 		Wow. Thanks for the information. This helps a lot.

(in reply to jnelson993)
Post #: 4
RE: Patch Status by Domain - 5/22/2008 5:14:46 PM   
tbiggi

 

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline 		Why am I getting:

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'pse'.

on your last query?

(in reply to jscott607)
Post #: 5
RE: Patch Status by Domain - 5/22/2008 5:18:55 PM   
jnelson993


Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Wouldn't you know, missing a comma

<snip>
cs.Manufacturer0 AS [Make],
  cs.Model0 AS [Model],
  cs.TotalPhysicalMemory0 AS [Memory], <- Right there
  pse.LocaleID,
  pse.UpdateID
ORDER BY
<snip>

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tbiggi)
Post #: 6
RE: Patch Status by Domain - 5/22/2008 5:20:03 PM   
tbiggi

 

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline 		And, how can this report only return results for patches that have been authorized?

(in reply to tbiggi)
Post #: 7
RE: Patch Status by Domain - 5/22/2008 5:20:47 PM   
jnelson993


Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Heck, missing 2 of them

-- SQL QUERY FOR DETAIL REPORT
SELECT    
  cs.domain0 AS [Domain],
  cs.name0 AS [Computer Name],
  cs.UserName0 AS [User Name],
  pse.ID,
  pse.Title,
  100 * SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / COUNT(*) AS '% Compliant',
  SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS 'Install Verified',
  SUM(CASE WHEN pse.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS 'No Status',
  SUM(CASE WHEN pse.LastStateName = 'Waiting' THEN 1 ELSE 0 END) AS 'Waiting',
  SUM(CASE WHEN pse.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS 'Retrying',
  SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS 'Failed',
  SUM(CASE WHEN pse.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS 'Uninstalled',
  SUM(CASE WHEN pse.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS 'Preliminary Success',
  SUM(CASE WHEN pse.LastStateName = 'Reboot Pending' THEN 1 ELSE 0 END) AS 'Reboot Pending',
  cs.Manufacturer0 AS [Make],
  cs.Model0 AS [Model],
  cs.TotalPhysicalMemory0 AS [Memory],
  pse.LocaleID,
  pse.UpdateID
FROM
  dbo.v_GS_PatchStatusEx AS pse
  INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS cs
     ON pse.ResourceID = cs.ResourceID
WHERE
  cs.domain0 = @Domain
  AND pse.ID = @ID
  AND pse.UpdateID = @UpdateID
GROUP BY
  cs.domain0,
  cs.name0 AS [Computer Name],
  cs.UserName0 AS [User Name],
  pse.ID,
  pse.Title,
  cs.Manufacturer0 AS [Make],
  cs.Model0 AS [Model],
  cs.TotalPhysicalMemory0 AS [Memory],
  pse.LocaleID,
  pse.UpdateID
ORDER BY
  cs.domain0,
  cs.name0,
  pse.updateID DESC



_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)
Post #: 8
RE: Patch Status by Domain - 5/22/2008 5:29:51 PM   
tbiggi

 

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline 		Now giving other errors ... will look at it a bit later.

(in reply to jnelson993)
Post #: 9
RE: Patch Status by Domain - 5/22/2008 5:48:58 PM   
jnelson993


Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Good grief, it's like I pasted a version of it before I finished it.  All the AS statements needed to be removed from the GROUP BY.  Sheesh.

SELECT top 100
cs.domain0 AS [Domain],
cs.name0 AS [Computer Name],
cs.UserName0 AS [User Name],
pse.ID,
pse.Title,
100 * SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) / COUNT(*) AS '% Compliant',
SUM(CASE WHEN pse.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS 'Install Verified',
SUM(CASE WHEN pse.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS 'No Status',
SUM(CASE WHEN pse.LastStateName = 'Waiting' THEN 1 ELSE 0 END) AS 'Waiting',
SUM(CASE WHEN pse.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS 'Retrying',
SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS 'Failed',
SUM(CASE WHEN pse.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS 'Uninstalled',
SUM(CASE WHEN pse.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS 'Preliminary Success',
SUM(CASE WHEN pse.LastStateName = 'Reboot Pending' THEN 1 ELSE 0 END) AS 'Reboot Pending',
cs.Manufacturer0 AS [Make],
cs.Model0 AS [Model],
cs.TotalPhysicalMemory0 AS [Memory],
pse.LocaleID,
pse.UpdateID
FROM
dbo.v_GS_PatchStatusEx AS pse
INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS cs
    ON pse.ResourceID = cs.ResourceID
WHERE
cs.domain0 LIKE @Domain
AND pse.ID LIKE @ID
AND pse.UpdateID LIKE @UpdateID
GROUP BY
cs.domain0,
cs.name0,
cs.UserName0,
pse.ID,
pse.Title,
cs.Manufacturer0,
cs.Model0,
cs.TotalPhysicalMemory0,
pse.LocaleID,
pse.UpdateID
ORDER BY
cs.domain0,
cs.name0,
pse.updateID DESC





< Message edited by jnelson993 -- 5/22/2008 5:50:39 PM >

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tbiggi)
Post #: 10
RE: Patch Status by Domain - 5/23/2008 9:11:45 AM   
tbiggi

 

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline 		Is there still a way to look at only the 'approved' or 'authorized' patches?  It used to be, before the 'PatchStatusEx', where one could use, I think, the TimeAuthorized was not NULL.

(in reply to jnelson993)
Post #: 11
RE: Patch Status by Domain - 5/23/2008 3:09:48 PM   
jnelson993


Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Yeah, I think you'd have to join to the v_GS_PatchSTATEex view on the resourceID and the uniqueUpdateID and use the authorized from there.  There isn't an authorize time in v_GS_PatchSTATUSex.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tbiggi)
Post #: 12
RE: Patch Status by Domain - 5/23/2008 3:14:56 PM   
tbiggi

 

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline 		Already looked there in my tables and all the entries are NULL.  Microsoft quit using those when they went to the "Ex" tables so I was hoping they hid that or tied it in someplace else...probably not.

(in reply to jnelson993)
Post #: 13
RE: Patch Status by Domain - 5/23/2008 3:21:21 PM   
jnelson993


Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		I don't think you read my post right.  I AM talking about the Ex views, I'm just talking about joining to STATE not just STATUS.

V_GS_PATCHSTATEEX


_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tbiggi)
Post #: 14
RE: Patch Status by Domain - 5/23/2008 3:26:30 PM   
tbiggi

 

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline 		I read it correctly.  Do a "select * from V_GS_PATCHSTATEEX" and you will see that the AuthorizationName and TimeAuthorized are all NULL.  The do the same on your V_GS_PATCHSTATE and you will see the information in those old ones

(in reply to jnelson993)
Post #: 15
RE: Patch Status by Domain - 5/23/2008 3:59:16 PM   
jnelson993


Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline 		Well, it's just that you said
quote:

Microsoft quit using those when they went to the "Ex" tables
So I guess I don't know what you're talking about.  Microsoft did not quit using V_GS_PatchStateEx cuz we use it all the time so what did you mean then? And we've TimeAuthorized0 field populated as recent as 5/13/08...


_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tbiggi)
Post #: 16
RE: Patch Status by Domain - 5/23/2008 4:51:39 PM   
tbiggi

 

Posts: 64
Score: 1
Joined: 9/13/2001
Status: offline 		I am running ITMUv3 on three seperate SMS 2003 infrastructures and none of them have the TimeAuthorized0 field populated.  I will need to dig into this further and go beat on Microsoft.  Have a good weekend !

(in reply to jnelson993)
Post #: 17
Page:   [1]
All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting >> Patch Status by Domain 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.375