November 24, 2010

SQL query to get patch status report of production servers

-- It provides information about servers and their patch status as per MS bulletin ID and Qnumber.

select distinct a.name0,a.user_name0,b.id0,b.qnumbers0,
'b.severity0' = Case
When b.severity0 = 10 Then 'Red'
When b.severity0 = 8 Then 'Amber'
When b.severity0 = 6 Then 'Green'
else ' '
from v_r_system a,v_GS_PATCHSTATEEX b
where a.resourceid=b.resourceid
and b.id0 in ('MS08-003','MS08-005','MS08-006','MS08-007','MS08-008','MS08-010',
and b.qnumbers0 not in ('951746','955069','954459','954606')
and status0 like 'Applicable'
and a.operating_system_name_and0 like '%server%'

-- bulletinid and qnumbers are provided by server team. I pulled reports of servers which required these patches as per requirements.

Hope, It will help you to someway!

No comments:

Post a Comment