Patch Report advice

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 advice

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 advice

Page: [1]

Message

<< Older Topic Newer Topic >>

Patch Report advice - 8/4/2008 4:57:29 PM

jscott607

Posts: 210
Score: 0
Joined: 9/20/2005
Status: offline

Hello. I am looking for some advice on a web report that I am working on. I have a SQL table that list all of the countries my company has offices. I am using this table to list the % compliant for each based only on patches that have been approved.

My question is that am I querying correcting for only patches that are approved. The results look odd in one of the countries. The country has a large amount that have a last state of no status.

Thank you for any advice.

SELECT     rl.Country, pse.Title AS 'Description', pse.ID AS 'Update ID', SUM(CASE WHEN pse.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS 'Failed',
                     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 = 'No Status' THEN 1 ELSE 0 END) AS 'No Status'
FROM         v_GS_PatchStatusEx AS pse INNER JOIN
                     v_R_System ON pse.ResourceID = v_R_System.ResourceID INNER JOIN
                     Regional_Location AS rl ON v_R_System.AD_Site_Name0 = rl.AdSite INNER JOIN
                     v_ApplicableUpdatesSummaryEx ON pse.UpdateID = v_ApplicableUpdatesSummaryEx.UpdateID
WHERE                 (v_ApplicableUpdatesSummaryEx.ScanAgent = 'Microsoft Updates Tool') and (v_ApplicableUpdatesSummaryEx.ID like 'MS0%') and (v_ApplicableUpdatesSummaryEx.TimeAuthorized not like 'Null') and (v_ApplicableUpdatesSummaryEx.InstalledCounts <> 0) and
      (rl.Country = @country) AND (pse.ID LIKE 'MS%') AND (pse.LastStateName = 'No Status') OR
                     (rl.Country = @country) AND (pse.ID LIKE 'MS%') AND (pse.LastStateName = 'Failed') OR
                     (rl.Country = @country) AND (pse.ID LIKE 'MS%') AND (pse.LastStateName = 'Retrying') OR
                     (rl.Country = @country) AND (pse.ID LIKE 'MS%') AND (pse.LastStateName = 'Waiting')

GROUP BY pse.ID, pse.Title, pse.UpdateID, rl.Country, rl.Region
ORDER BY 'Failed' DESC, 'No Status' DESC

Post #: 1

RE: Patch Report advice - 8/4/2008 7:22:50 PM 1 votes

jnelson993

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

Well, I don't have Regional_Location, I assume that's your own little thing, but my first thought is that you've got NOT LIKE 'NULL' in there for the timeAuthorized. If you want to look for NULL values, you need to use IS NULL or IS NOT NULL and skip the quotes. Otherwise it looks for the letters N U L L.

My next thought is that I haven't really found the patch STATUS very reliable, so I go to patch STATE if I'm just looking for compliance numbers.
SELECT
   pse.Title0                                                          AS [Description],
   pse.ID0                                                             AS [Update ID],
   SUM(CASE WHEN pse.status0 = 'INSTALLED' THEN 1 END)    AS [Installed],
   SUM(CASE WHEN pse.status0 = 'APPLICABLE' THEN 1 END)   AS [Applicable]
FROM
   v_GS_PatchStateEx AS pse
   INNER JOIN v_R_System AS sys
     ON pse.ResourceID = sys.ResourceID
   INNER JOIN v_ApplicableUpdatesSummaryEx
     ON pse.UpdateID0 = v_ApplicableUpdatesSummaryEx.UniqueUpdateID
   INNER JOIN Regional_Location AS rl
     ON sys.AD_Site_Name0 = rl.adSite
WHERE    (v_ApplicableUpdatesSummaryEx.ScanAgent = 'Microsoft Updates Tool')
         AND (v_ApplicableUpdatesSummaryEx.ID LIKE 'MS0%')
         AND (v_ApplicableUpdatesSummaryEx.TimeAuthorized IS NOT NULL)
         AND (v_ApplicableUpdatesSummaryEx.InstalledCounts <> 0)
         AND (pse.ID0 LIKE 'MS%')
         AND (rl.Country = @country)
GROUP BY pse.ID0,pse.Title0
ORDER BY pse.ID0,
         pse.Title0

_____________________________

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

(in reply to jscott607)

Post #: 2

RE: Patch Report advice - 8/5/2008 12:29:21 PM

jscott607

Posts: 210
Score: 0
Joined: 9/20/2005
Status: offline

Thank you very much for the response. My report is based off of one you assisted me with earlier. I will try it with the changes you recommend.

(in reply to jnelson993)

Post #: 3

RE: Patch Report advice - 8/5/2008 5:41:46 PM

jnelson993

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

Sorry, I should have picked up on that, but I do so many of these things in a week between the forums and the other people at work here that I can't remember whom I've done what for :)

Let us know how it goes. I do prefer the patchStateEx view because it's based on the actual patch scan. If it's patched, this WILL tell you.

_____________________________

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

(in reply to jscott607)

Post #: 4