SSRS – Reporting -Patching
Question
Hi Team,
I am looking to prepare simple report which gives patching status for end users on monthly basis over email.
Do we have any article which gives sample report on this.
Will the below columns be sufficient for the report :
- Device name
2. Collection name
3. Status – Should show compliant or error or in progress
Answers ( 4 )
Hi Mohan
i have updated the software update group deployment name and collection ID as below
I am getting line 5 incorrect syntax error
select
cs.name0 ‘Computer_name’,
os.Caption0 as ‘Operating System’,
col.Name as [Collection Name],
sum(case when UCS.status=3 then 1 else 0 end) ‘Installed’,
sum(case when UCS.status=3 then 1 else 0 end)+sum(case when UCS.status=2 then 1 else 0 end) ‘Applicable’,
sum(case when UCS.status=2 then 1 else 0 end) ‘Not Installed’,
concat(CONVERT(DECIMAL(18,2),round((1.0*sum(case when UCS.status=3 then 1 else 0 end))/(1.0*(sum(case when UCS.status=3 then 1 else 0 end)+sum(case when UCS.status=2 then 1 else 0 end))),5,5)*100),’%’) ‘Compliant%’,
case
when (sum(case when UCS.LastEnforcementMessageID=9 then 1 else 0 end))>0
then ‘Yes’
else ‘No’
end as ‘Pending restart’
from
v_UpdateComplianceStatus UCS
left outer join dbo.v_R_System CS on CS.ResourceID = UCS.ResourceID
left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
left join v_collection col on fcm.CollectionID = col.CollectionID
left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
left join v_AuthListInfo LI on ucs.ci_id=li.ci_id
left join v_GS_OPERATING_SYSTEM as os on CS.ResourceID = os.ResourceID
Where
FCM.collectionid in (‘PRI00014’)
and li.Title like ‘Microsoft Software updates’
Group by
CS.Name0, COL.Name, OS.Caption0
Hi Ram,
I copied query from your reply and tested in my environment, it working
Hi Ramkumar,
I have prepared custom SQL query for your requirement, pleas use below query i think it will works for you.
2 thing needs to update on this deployment,
Add all you patch deployment collection ID
Add your software update group deployment name.
select
cs.name0 ‘Computer_name’,
os.Caption0 as ‘Operating System’,
col.Name as [Collection Name],
sum(case when UCS.status=3 then 1 else 0 end) ‘Installed’,
sum(case when UCS.status=3 then 1 else 0 end)+sum(case when UCS.status=2 then 1 else 0 end) ‘Applicable’,
sum(case when UCS.status=2 then 1 else 0 end) ‘Not Installed’,
concat(CONVERT(DECIMAL(18,2),round((1.0*sum(case when UCS.status=3 then 1 else 0 end))/(1.0*(sum(case when UCS.status=3 then 1 else 0 end)+sum(case when UCS.status=2 then 1 else 0 end))),5,5)*100),’%’) ‘Compliant%’,
case
when (sum(case when UCS.LastEnforcementMessageID=9 then 1 else 0 end))>0
then ‘Yes’
else ‘No’
end as ‘Pending restart’
from
v_UpdateComplianceStatus UCS
left outer join dbo.v_R_System CS on CS.ResourceID = UCS.ResourceID
left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
left join v_collection col on fcm.CollectionID = col.CollectionID
left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
left join v_AuthListInfo LI on ucs.ci_id=li.ci_id
left join v_GS_OPERATING_SYSTEM as os on CS.ResourceID = os.ResourceID
Where
FCM.collectionid in (‘PRI0001’, ‘PRI0002’, ‘PRI0003’, ‘PRI0004’, ‘PRI0005’, ‘PRI0006’, ‘PRI0007’, ‘PRI0008’, ‘PRI0009’, ‘PRI0010’, ‘PRI0011’, ‘PRI00012’, ‘PRI0013’, ‘PRI0014’)
and li.Title like ‘SUP Group Deployment Name’
Group by
CS.Name0, COL.Name, OS.Caption0
Hi,
Use below link to create custom SSRS Reports and configure SMTP channel with email subscription alert.
https://gallery.technet.microsoft.com/TI095-Custom-Report-2b051812
USe below SQL query to get the status for specific software update group
–All Deployments status for Specific Software Update Group
————————————————————————————————————–
Declare @SoftwareUpdateGroupName as varchar(255)
Set @SoftwareUpdateGroupName = ‘All Updates SRWP2’ –Specify Software Update Group Name
Select
Vaa.AssignmentName as ‘DeploymentName’,
Right(Ds.CollectionName,3) as ‘Stage’,
Li.Title as ‘SUGroupName’,
CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’ Else ‘Others’ End as ‘DepType’,
Ds.CollectionName as ‘CollectionName’,
CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,
Ds.DeploymentTime as ‘AvailableTime’,
Ds.EnforcementDeadline as ‘RequiredTime’,
Ds.NumberTotal as ‘Target’,
Ds.NumberSuccess as ‘Success’,
Ds.NumberInProgress as ‘Progress’,
Ds.NumberErrors as ‘Errors’,
Ds.NumberOther as ‘Others’,
Ds.NumberUnknown as ‘Unknown’,
case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then ‘0’ Else (round(Ds.NumberSuccess/ convert (float,Ds.NumberTotal)*100,2)) End as ‘Success%’,
DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,
DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’,
DateDiff(D,Ds.CreationTime, GetDate()) as ‘CreatedDays’,
Vaa.CreationTime as ‘CreationTime’,
Vaa.LastModificationTime as ‘LastModifiedTime’,
Vaa.LastModifiedBy as ‘LastModifiedBy’
from v_DeploymentSummary Ds
left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID
Where Ds.FeatureType = 5
and Li.Title like @SoftwareUpdateGroupName
order by Ds.DeploymentTime desc
————————————————————————————————————–
–Deployment Detailed status for specific software Update Group
————————————————————————————————–
Declare @SoftwareUpdateGroupName as Varchar(255)
Set @SoftwareUpdateGroupName = ‘SU WKS Security Updates 1803’ –Specify Software Update GroupName
select
vrs.Name0 as ‘ComputerName’,
vrs.User_Name0 as ‘LastLogonUser’,
vos.Caption0 as ‘Operating Sytem’,
a.AssignmentName as ‘DeploymentName’,
Right(Ds.CollectionName,3) as ‘Stage’,
Li.Title as ‘SUGroupName’,
CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2 Then ‘Available’ End as ‘Purpose’,
Ds.CollectionName as ‘CollectionName’,
Ds.DeploymentTime as ‘AvailableTime’,
Ds.EnforcementDeadline as ‘RequiredTime’,
DateDiff(D,Ds.DeploymentTime, GetDate()) as ‘AvailableDays’,
DateDiff(D,Ds.EnforcementDeadline, GetDate()) as ‘RequiredDays’,
DateDiff(D,Ds.CreationTime, GetDate()) as ‘CreatedDays’,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,
SUP.LastErrorCode as ‘LasErrorCode’,
SUP.LastScanTime as ‘LastSUPScan’,
DateDiff(D, SUP.LastScanTime, GetDate()) as ‘LastSUPScan Age’,
wks.LastHWScan,
DateDiff(D, wks.LastHwScan, GetDate()) as ‘LastHWScan Age’
from v_CIAssignment a
left join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
left join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
left join v_R_System vrs on vrs.ResourceID = assc.ResourceID
Left Join V_GS_Operating_System Vos on Vrs.ResourceID = vos.resourceID
left join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID
left join v_UpdateScanStatus SUP on SUP.ResourceID = assc.ResourceID
left join v_DeploymentSummary Ds on Ds.AssignmentID=assc.AssignmentID
left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID
where Li.Title = @SoftwareUpdateGroupName
and assc.StateType in (300,301)
order by 11 desc
————————————————————————————————–
Thanks
Karthikeyan