Advertisements
Latest Posts

SQL Query to list software updates deployment status for a specific BulletinID


This query will be useful if any specific Bulletin ID update causing issue on the machines and to figure out the deployed machines  & other status to troubleshoot further.We have some built-in reports in SCCM but you will have to export the machines details by one by one based on the deployment status.

Select
a.name0 [Machine Name],
c.BulletinID,
c.Title,
d.CategoryInstanceName [UpdatesClassification],
CASE((c.Severity))
When 2 Then ‘Low’
When 6 Then ‘Moderate’
When 8 Then ‘Important’
When 10 Then ‘Critical’
Else ‘NA’ End as ‘Severity’,
CASE((b.Status))
When 3 Then ‘Installed’
When 2 Then ‘Required’
Else ‘Not Required’ End as ‘Status’
from v_R_System a join
v_Update_ComplianceStatusReported b on a.ResourceID=b.ResourceID join
v_UpdateInfo c on b.CI_ID=c.CI_ID join
v_CICategoryInfo_All d on d.CI_ID=c.CI_ID
where d.CategoryInstanceName=‘Security updates’ and c.BulletinID=‘MS15-070’

Result->

2015-08-01_4-04-41

 

The below query will give you the machines count for the deployment status.

Select

count(b.Status) [Machines Count],
c.BulletinID,
c.Title,
d.CategoryInstanceName [UpdatesClassification],
CASE((c.Severity))
When 2 Then ‘Low’
When 6 Then ‘Moderate’
When 8 Then ‘Important’
When 10 Then ‘Critical’
Else ‘NA’ End as ‘Severity’,
CASE((b.Status))
When 3 Then ‘Installed’
When 2 Then ‘Required’
Else ‘Not Required’ End as ‘Status’
from v_R_System a join
v_Update_ComplianceStatusReported b on a.ResourceID=b.ResourceID join
v_UpdateInfo c on b.CI_ID=c.CI_ID join
v_CICategoryInfo_All d on d.CI_ID=c.CI_ID
where d.CategoryInstanceName=‘Security updates’ and c.BulletinID=‘MS15-070’

GROUP BY b.Status,c.BulletinID,c.Title,d.CategoryInstanceName,c.Severity

Result->

2015-08-01_4-30-08

 

 

 

Advertisements

1 Comment on SQL Query to list software updates deployment status for a specific BulletinID

  1. Hello,I read your blog named “SQL Query to list software updates deployment status for a specific BulletinID – Karthick Jokirathinam’s blog” on a regular basis.Your humoristic style is awesome, keep it up! And you can look our website about تحميل افلام.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s