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.language0,b.product0,b.reboottype0,b.scanagent0,
'b.severity0' = Case
When b.severity0 = 10 Then 'Red'
When b.severity0 = 8 Then 'Amber'
When b.severity0 = 6 Then 'Green'
else ' '
End,
b.status0,b.type0,b.title0,b.timeapplied0,b.timeauthorized0
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',
'MS08-020','MS08-021','MS08-022','MS08-031','MS08-032','MS08-033','MS08-034','MS08-035',
'MS08-036','MS08-037','MS08-045','MS08-046','MS08-047','MS08-048','MS08-049','MS08-050',
'MS08-051','MS08-052','MS08-053','MS08-058','MS08-061','MS08-062','MS08-063','MS08-064',
'MS08-065','MS08-066','MS08-067','MS08-068','MS08-069','MS09-001')
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!